Posted on 2004-09-07
Wiley Coyote couldn't feel how I feel right now. Guidance. Please.
Building a db for Property inspection.
Properties (Property, address, city, state, zip, inspector, selection)
Units (Property, Unit ID, Unit No, Type, Inspected, etc, etc, etc)
Bedrooms (Property, Unit ID, Unit No, Walls, Wallpaper, Carpet, Mold, etc, etc, etc)
Bathrooms (Property, Unit ID, Unit No, Walls, Tile, Sink, Mold, etc, etc, etc)
Exterior (Property, Unit ID, Unit No, Door, Deadbolt, Patio Door, Screen Door, etc, etc, etc)
Kitchen (Property, Unit ID, Unit No, Walls, Tile, Sink, Refrig., Stove, Mold, etc, etc, etc)
LivingDiningHall (Property, Unit ID, Unit No, Walls, Wallpaper, Carpet, Hall Walls, Mold, etc, etc, etc)
MechanicalSafety (Property, Unit ID, Unit No, Furnace, Water Heater, Smoke Detector UP, Smoke Detector Down, etc, etc, etc)
UnitSummary (Care of Unit, Replace All Comment, y/n, y/n, y/n, Over All Comments, etc, etc)
The DB will ActiveSync with a iPaq and the interface is already built and running fine using SprintDB (love it!).
Problem 1: Trying to compile all the info to be retrieved into one Table or Query for the report. Beating my head against a wall does not even cover it.
If I go subforms - It's just plain wrong. Not to mention slow and redundant and "there's got to be another way" crazy.
If I go to compile into one query, I'll end up with 1 query with 15 BILLION fields. Slow, redundant and "there's got to be another way" crazy.
The only other direction I can see to go is to create a query, with all the right relationships and two fields; Select column heading from table 1-7 to populate field A as the "question" and then take information from tables 1-7 to populate the field B as the "answer" and then......OH MY GOD...There's got to be another way. In the meantime my DB keeps going BEEP! BEEP!
FYI - Property field of Property is my unique field. the interface builds it's own relationship internally which is why I must have the property, unit no, and unit id in each field, otherwise the handheld doesn't work correctly.