Add records from another table

[Frizzellio]

I have three tables:

1. tblInspections
InspectionID (PK)
Inspector
DateInspected

2. tblInspectionItems
ItemID (PK)
Description
Reference

3. tblResults
ResultID (PK)
InspectionID
ItemID
Result

I want to add a record via a form button such that a new inspection is created adding all inspection items to that particular inspection record in tblResults. I think it should look something like this:

ResultID..InspectionID..ItemID..Result
1.........1.............1.......Pass/Fail
2.........1.............2.......Pass/Fail
3.........1.............3.......Pass/Fail
4.........2.............1.......Pass/Fail
5.........2.............2.......Pass/Fail
6.........2.............3.......Pass/Fail
etc

Am I thinking about this right?

If so, how do I do this via code?

If not, how would I go about connecting all inspection items (170 total) to every inspection record?

Thanks in advance!

 

[fneily]

You could normalize your tables. So:
tblInspectionItems
ItemsID
Description
other item fields

tblInspector
InspectorID
other Inspector fields

tblInspection
InspectionID Primary Key
ItemID Foreign Key
InspectorID Foreign Key
DateInspected
Result

Now tblInspection is basically is the output you want. And it's normalized. You have a many-to-many relationship - one inspector can inspect many items. Also, one item can be inspected by many inspectors. You can't have a many-to-many relationship in Access. So you must build a junction table which is the tblInspection. Now you have two one-to-many relationships, tblInspector-to-tblInspection and tblInspectionItems-to-tblInspection. Now you can build form/subforms.