Looking for a bit of help on how to design my DB

[grabrail]

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.

[June7]

Maybe get ideas from https://www.youtube.com/watch?v=WIX3lXA4sCo

[Micron]

This is always an involved discussion where you likely end up answering many questions. I don't think you'll like what I'm going to say though.
Fact: most people who move from Excel to Access set up their db tables all wrong (like 95%?). If you have not, research db normalization as tables (tall) are not spreadsheets (wide). Related info resides in related tables and things are brought together via relationships.

Sounds like you're saying that inspecting 5 vehicles is one inspection, and I think that will not serve you well. It really is 5 inspections done on one other "thing" (we used to call that thing a Route) but I doubt you or anyone else will want to change that way of thinking. You will likely end up with at least 5 tables once you wrap your head around normalization as it applies to your project.

My background was PM and CO work (preventive maintenance/corrective maintenance) at both ends of the spectrum - execution and planning - for quite a few years in case that tells you anything.

[grabrail]

This is always an involved discussion where you likely end up answering many questions. I don't think you'll like what I'm going to say though.
Fact: most people who move from Excel to Access set up their db tables all wrong (like 95%?). If you have not, research db normalization as tables (tall) are not spreadsheets (wide). Related info resides in related tables and things are brought together via relationships.

Sounds like you're saying that inspecting 5 vehicles is one inspection, and I think that will not serve you well. It really is 5 inspections done on one other "thing" (we used to call that thing a Route) but I doubt you or anyone else will want to change that way of thinking. You will likely end up with at least 5 tables once you wrap your head around normalization as it applies to your project.

My background was PM and CO work (preventive maintenance/corrective maintenance) at both ends of the spectrum - execution and planning - for quite a few years in case that tells you anything.

From a terminology perspective, each visit to a site is called an inspection, and each individual vehicle inspected is called a DPU. So DPU 1, DPU 2, etc will all be part of Inspection A (for example)

I have experience in the past with SQL databases, so ahve an understanding of how databases and excel differ. What I am trying to do is work out how to create the relationship between these 2 different tables at the point of import. The end goal here is to have the engineers provide the completed reports, and thos reports are imported into the database, so the Gatecheck ID needs tbe created/generated at the point of import, what I am thinking maybe, is:

each row has a PK generated using the random process, then the Gatecheck ID field is generated maybe using a combination of some other data in the workbook, such as some mixup of Date and Operator so the Gatecheck ID could look something like 310523smiths or smiths310523 and then that used as a foreign key maybe between the Gatechecks and Defects tables?

[June7]

It would likely be complicated coding to import data appropriately into related tables and assign generated parent key to related child table records. I've done that using autonumber field for parent key. I've also done it using a natural key which was actually assigned before data collection. However, in both cases data sources were Access files already in proper db schema and I had to merge multiple files to one master db. Importing from non-normalized Excel to normalized structure would be even trickier.

[Micron]

IMO the best approach is to import the data as is (in similar raw data table structure) then use queries in Access to migrate it into properly normalized tables which you then use. I wouldn't try to relate a normalized table to a spreadsheet or a table that looks like one. With that method, no code needed for imports (but is optional) and no code needed to get it into a normalized state. Also, that takes care of the issue that often arises from Excel data types being wrong for Access.

But a bit confused; if the goal is to move away from spreadsheets, is importing really a long term issue?

I am currently setting up a DB in Access to move away from spreadsheets