selfed
asked on
Data Compilation
Wiley Coyote couldn't feel how I feel right now. Guidance. Please.
Building a db for Property inspection.
9 Tables
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.
Any suggestions?
BEEP! BEEP!
Thanks, C
Building a db for Property inspection.
9 Tables
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.
Any suggestions?
BEEP! BEEP!
Thanks, C
shanesuebsahakarn
Do you have any leeway in changing the table structure, given that you have an iPaq interface?
ASKER
I have quite a bit of leeway.
SprintDB is actually quite easy and if I need to change anything I can do it quickly. The relationships on the interface side are pretty easy to create/delete/modify.
No relationships have been built on the access side as of yet.
BEEP! BEEP!
C~
SprintDB is actually quite easy and if I need to change anything I can do it quickly. The relationships on the interface side are pretty easy to create/delete/modify.
No relationships have been built on the access side as of yet.
BEEP! BEEP!
C~
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can I tell you how much I hate it when the answer is easy.
EASY. I will have to rebuild the Access and the SprintDB part of it BUT
WHY DIDN'T I THINK OF IT!!!!!!!!!!!!!!!!!!!!!
That will make life so much simpler, but because I am who I am....trust me on this.....
The final table layout would be...
Property
Unit
Features
Instead of the 9 that I have right now, it will only be 3? Can we all say DUUHHH!
I am soooo stupid.
Thank you
Thank you
Thank you
This will also fix my other problem of multiple users, but with this solutions, Problem 2 is an easy fix.
THANK YOU.
BEEP! BEEP! CRUSH!!!!!!!!!!!
C~
EASY. I will have to rebuild the Access and the SprintDB part of it BUT
WHY DIDN'T I THINK OF IT!!!!!!!!!!!!!!!!!!!!!
That will make life so much simpler, but because I am who I am....trust me on this.....
The final table layout would be...
Property
Unit
Features
Instead of the 9 that I have right now, it will only be 3? Can we all say DUUHHH!
I am soooo stupid.
Thank you
Thank you
Thank you
This will also fix my other problem of multiple users, but with this solutions, Problem 2 is an easy fix.
THANK YOU.
BEEP! BEEP! CRUSH!!!!!!!!!!!
C~
I have a few question about your tables and data fields
1. what is Unit No and why is it in most of your tabless
2. Could you create a rooms table and store your room type information then use that as a lookup
Example Room 01 Bedroom, wallpaper X ECT
3. Create a Exterior table and do the same thing using lookups
4. Add UnitSummary to the unit table
Not quit as smiple as shanesuebsahakarn (which is a good design) but it gives you a lot of room to expand your discriptions
I hope this helps you
thank you for your time
Al
1. what is Unit No and why is it in most of your tabless
2. Could you create a rooms table and store your room type information then use that as a lookup
Example Room 01 Bedroom, wallpaper X ECT
3. Create a Exterior table and do the same thing using lookups
4. Add UnitSummary to the unit table
Not quit as smiple as shanesuebsahakarn (which is a good design) but it gives you a lot of room to expand your discriptions
I hope this helps you
thank you for your time
Al
ASKER
I know, it's a little confusing. The property inspection db will be for apartment complexes and the sort.
1. what is Unit No and why is it in most of your tabless
Unit Number is strictly for which apartment is being inspected
Description of the apartment 1bed/1bath, 2bed/1bath
Status, inspected, denied, big freakin dog, no key
and I also have an inspect y/n field which locks that particular record on the interface side.
The Unit no was in most of the tables because I needed to lock the records on the interface side, i.e., They can go in and Add New Unit ( New Inspection) or Modify an Existing Unit (Oops I forgot to add the comment). When the press Modify, I only want the information for one unit. In Access you can do it with a query, but with the interface the closest I could get was to create multiple inner joins for each table. Instead I went with a Dlookup (I don't know which one was right).
2. Could you create a rooms table and store your room type information then use that as a lookup
Example Room 01 Bedroom, wallpaper X ECT
Yes and no. If it were strictly on the Access side most definately. I accepted shanesuebsahakarn solution but I will still have to make modifications. SprintDB wants to populate the fields. There's really no way around it. When I rebuild the interface side for the db using this solution, I will simply make the Feature Type a default value on each form and the Feature Value is populated with a drop down. SprintDB wants the record "going someplace" and it wants to be in control. All Access will have to do is accept the info. Placing a lookup on the Access side won't do anything on the interface side.
3. Create a Exterior table and do the same thing using lookups
Kind of the same thing, the interface wants to do the work.
4. Add UnitSummary to the unit table
Tried that before but with the above I won't have to. And I can't remember when I tried it or why it didn't work.
If they want to expand anything, they'll have to hire me for a redesign on the interface side. The Interface takes only the tables and table structure. No relationships, no drop down values, nothing.
Hope this helps.
Thanks, C~
1. what is Unit No and why is it in most of your tabless
Unit Number is strictly for which apartment is being inspected
Description of the apartment 1bed/1bath, 2bed/1bath
Status, inspected, denied, big freakin dog, no key
and I also have an inspect y/n field which locks that particular record on the interface side.
The Unit no was in most of the tables because I needed to lock the records on the interface side, i.e., They can go in and Add New Unit ( New Inspection) or Modify an Existing Unit (Oops I forgot to add the comment). When the press Modify, I only want the information for one unit. In Access you can do it with a query, but with the interface the closest I could get was to create multiple inner joins for each table. Instead I went with a Dlookup (I don't know which one was right).
2. Could you create a rooms table and store your room type information then use that as a lookup
Example Room 01 Bedroom, wallpaper X ECT
Yes and no. If it were strictly on the Access side most definately. I accepted shanesuebsahakarn solution but I will still have to make modifications. SprintDB wants to populate the fields. There's really no way around it. When I rebuild the interface side for the db using this solution, I will simply make the Feature Type a default value on each form and the Feature Value is populated with a drop down. SprintDB wants the record "going someplace" and it wants to be in control. All Access will have to do is accept the info. Placing a lookup on the Access side won't do anything on the interface side.
3. Create a Exterior table and do the same thing using lookups
Kind of the same thing, the interface wants to do the work.
4. Add UnitSummary to the unit table
Tried that before but with the above I won't have to. And I can't remember when I tried it or why it didn't work.
If they want to expand anything, they'll have to hire me for a redesign on the interface side. The Interface takes only the tables and table structure. No relationships, no drop down values, nothing.
Hope this helps.
Thanks, C~
ASKER
One more note.
When I said I would have to rebuild both the interface and the access db, I was not kidding.
It's great software but only takes the tables and the structure. Everything else has to be populated on the interface side.
When I rebuild I will create the multiple forms that I need with the features (default value specific to each form) and the features value (drop down, text, or y/n) and create lables for what I need. Sounds like a pain in the butt and it is
BUT
When it comes time to finish the Access side I'll be thanking shanesuebsahakarn again and again. The Multiple user issue will be nothing now, and creating the queries, reports and Master Forms for internal use will be a piece of cake (if a database can be a piece of cake, that is).
Thanks, C~
When I said I would have to rebuild both the interface and the access db, I was not kidding.
It's great software but only takes the tables and the structure. Everything else has to be populated on the interface side.
When I rebuild I will create the multiple forms that I need with the features (default value specific to each form) and the features value (drop down, text, or y/n) and create lables for what I need. Sounds like a pain in the butt and it is
BUT
When it comes time to finish the Access side I'll be thanking shanesuebsahakarn again and again. The Multiple user issue will be nothing now, and creating the queries, reports and Master Forms for internal use will be a piece of cake (if a database can be a piece of cake, that is).
Thanks, C~
No problem :-)
Shane.
ACME Database Solutions.
Shane.
ACME Database Solutions.
If you are writing your software in VB with Access back end then you could create a property object and store the data from the tables that shanesuebsahakarn gave you. After that you can add a object interface this would make the rest of your job very easy as well.
I am glad that shanesuebsahakarn gave you a great solution I have seen him on here for about 6 days now and know he doesn't miss much
Good luck and have a nice day
I am glad that shanesuebsahakarn gave you a great solution I have seen him on here for about 6 days now and know he doesn't miss much
Good luck and have a nice day
ASKER
I'm not sure I understand. If VB stands for the $$$$$$$$ software. I don't have it (the software or the cash).
If there is a built in function that will enable me to do this without the third-party software I'd love to know what that object interface is.
Project Object and Object interface, is that Greek or Chinese?
Thanks, C~
If there is a built in function that will enable me to do this without the third-party software I'd love to know what that object interface is.
Project Object and Object interface, is that Greek or Chinese?
Thanks, C~
Ok I get the point
So many people on here seem to have Visual basic.
Let me look if I can still find the free version of Visual basic (Think it is called Control Creation Edition). It is limited but it will give you a starting point. Here is an older version but the price is right it is free.
http://earthlink.com.com/3302-2403_4-880768.html?pn=2&fb=0
<If there is a built in function that will enable me to do this without the third-party software I'd love to know what that object interface is
No not unless you create one
If you do let me know
<Project Object and Object interface, is that Greek or Chinese?
No it is computerese
A language created to confuse and daze the new computer programmer or user.
It works great (the confusion may last for weeks, most days when I get out of work it takes me 2 to 3 hours before I can talk normal again ;)
So many people on here seem to have Visual basic.
Let me look if I can still find the free version of Visual basic (Think it is called Control Creation Edition). It is limited but it will give you a starting point. Here is an older version but the price is right it is free.
http://earthlink.com.com/3302-2403_4-880768.html?pn=2&fb=0
<If there is a built in function that will enable me to do this without the third-party software I'd love to know what that object interface is
No not unless you create one
If you do let me know
<Project Object and Object interface, is that Greek or Chinese?
No it is computerese
A language created to confuse and daze the new computer programmer or user.
It works great (the confusion may last for weeks, most days when I get out of work it takes me 2 to 3 hours before I can talk normal again ;)