Hi, I am currently setting up a DB in Access to move away from spreadsheets that we currently use. A bit of background.
We are a vehicle inspection company and we have engineers that go out to customer sites and inspect vehicles. The wiill complete a spreasheet form, that has multiple macros and code to make it function. There is a seperate worksheet for each vehicle inspected on that site on that day.
Once the spreadsheet is completed, a button in the workbook is pressed that collates all the gathered data to 2 seperate worksheets in a specific format. Each seperate vehicle inspection worksheet is called a DPU, so DPU Report 1, DPU Report2, etc up to a max of 20
I have a spreadsheet based database that all of these workbooks are imported to, the database workbook has the same 2 tabs as eahc of the inspection workbooks, called Gatechecks and GCDefects. The Gatechecks worksheet contains all the info about the inspection, and GCDefects contains any defects found on the vehicles. The 2 are tied together by a Gatecheck ID and DPU Number fields. I will attach images to show this. The images shown are of the individual inspection workbooks, the gatecheck ID is left blank is as created when the data is imported into the database to avoid duplicates.
Now, I am trying to replicate this to a DB in Access, so I want 2 tables, one for Gatechecks and one for Defects. However, what I am struggling to get my head round is, each workbook imported must have an individual Gatecheck ID, this ID must be against all veheicles inspected on that report, so for example, if 5 vehicles are inspected there will be 5 rows on the gatechecks table, but each one must have teh same Gatecheck ID as they are all part of the same inspection.
Then, if 3 of those vehicles have defects found, they will be listed on the defects table, this table shoould also have the same gatecheck ID, and also the DPU Report Number that the defect came from. Also to add to this, one vehicle (Or one DPU Report) could have multiple defects listed, so event hough in this examnple there are only 3 vehicles with defects, one of those vehicles could have 5 indiviudual defects, and therefore a seperate row on the defects tab for each defect.
how would i set up the relationships of the tables to achieve this? I really hope I have explained this well enough.