Stuck

[davidodell]

Please advise best way to build and relate table
Owners table to wells table to inspections table fine then i want to go to 6 different kinds of inspections with only one it relates great where i can cascade delete when i add second table for second type of test it messes up please advise.
Thanks

 

[nberryman]

If you have 6 different inspections then to me it seem that you need each inspection as a record in the inspections table, not a table for each



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[lespaul]

exactly, create a new table called tblInspectionType (InspectionID (PK), InspectionType), then store the InspectionID in the inspections table to indicate what kind of inspection it was.



Leslie

 

[tahilg]

I presume you are trying to create a different table for each inspection type because each one needs to have different data fields. Why not create six "virtual" tables within one actual table.

Try two new tables:

Table 1:
Inspection-Type (FK to the inspection type table if any)
Info-Type

The Info-Type field would contain the name of an allowed/required data field. This table would identify the detailed information allowed/required for each inspection type.

Table 2:
Inspection-Id (FK to the main inspections table)
Info-Type
Info-Value

The Info-Type field would contain the name of an allowed/required data field. The Info-Value field would contain the actual value for the specified Info-Type.

You will have to programmatically ensure that the appropriate Info-Type values are used in Table 2 for each type of inspection.

HTH

Gunny

 

[davidodell]

Gunny your on the right track but these six inspections all have different results like one has one result true false, then 6 numeric values then 16 numeric values then one has 6 text entries and another 7 text entries, and the last has 3 true false 3 text. so they all really need a seperate table for responses, I know i can push them all to a seperate database but i would like to have it all together and I know there is a way. Thank you all for your responses.

 

[lespaul]

You should still be able to take advantage of Gunny's suggestion! You'll just need another table or two.

You have:

tblInspection
InspectionID
InpectionType

tblInspectionType
TypeID

tblInspectionItems(all the items that can be on any inspection type)
ItemID

tblTypeItems(stores which items are on each individual inspection type)
maybe AutoNumber ID?
TypeID(PK)
ItemID(PK)
ResultType (Text, Number, Boolean)

tblInspectionResults
InspectionID
AutoNumber from tblTypeItems
Result


Leslie

 

[davidodell]

ok i think i am seeing this so basically put all the results into one table and then form or query out the needed info but let it all be entered into one table insted of six. I can't belive i couldn't see that. i am pretty sure that will work.
So there isnt a way to leave them seperated and relate the tables so that the tests link back to the inspection id?