Solved

Data Compilation

Posted on 2004-09-07
11
228 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:selfed
  • 5
  • 3
  • 3
11 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Do you have any leeway in changing the table structure, given that you have an iPaq interface?
0
 

Author Comment

by:selfed
Comment Utility
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~
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
Comment Utility
I might look at changing this structure into 2 tables and two related "lookup" tables.

I'd create one table that was called, perhaps, Features (I'd call it Propeties but that would rather confuse matters!). This contains:
ID
Property ID
Room
FeatureType
FeatureValue

The Room field contains Bedroom, Bathroom, Living Room etc. The FeatureType contains "Walls", "Wallpaper" etc, and the FeatureValue contains whatever data you held in those fields in your current tables. That might make it easier to report on and summarise. The "lookup" tables would limit the selections available in FeatureType and Room (and in fact, you might relate these fields by some kind of numeric ID rather than storing text in the Features table).
0
 

Author Comment

by:selfed
Comment Utility
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~
0
 
LVL 4

Expert Comment

by:Excalibur_Software
Comment Utility
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


 
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:selfed
Comment Utility
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~
0
 

Author Comment

by:selfed
Comment Utility
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~
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
No problem :-)

Shane.
ACME Database Solutions.
0
 
LVL 4

Expert Comment

by:Excalibur_Software
Comment Utility
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
0
 

Author Comment

by:selfed
Comment Utility
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~
0
 
LVL 4

Expert Comment

by:Excalibur_Software
Comment Utility
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 ;)
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now