CODEWITHSUNDEEP
databasems-access
Coffman34
Coffman34

Reputation: 57

How to link data in an Access table?

I am attempting to build a database for fire extinguisher inspections.

I have one table that contains all of the fire extinguishers on site.

ID - Barcode - Maintenance Date - Hydro date

I have another table that will contain an entry from when we do our inspections.

ID - Barcode - Inspection Date - Inspectors

Then each building has it's own table with locations.

ID - Location - Lookup for extinguisher - Data from extinguisher table - Data from inspection table

IE, you select an extinguisher in the building table from a lookup from the extinguisher database. I then want it to populate the rest of the fields in the building table with info from each of the other two. Pulling the most recent inspection date for that extinguisher.

I think I need a query or report. But am not quite sure.

My data looks like this:

Buildings table: Building names only (~100 records)

Extinguisher Locations: Building (linked above) - Floor - Exact Location (description) - Current Extinguisher (linked below) (~ 1000 records)

Extinguishers: Size - Type - Barcode - Serial - Maintenance date - Hydrostatic date (~1200 records)

Inspection log: Extinguisher (linked from above, multiple records per extinguisher may exist) - Inspection metrics (Rusted? Full? Maint/Hydro date still acceptable?) - Inspection date - Inspectors

Upvotes: 0

Views: 108

Answers (1)

Marek Stejskal
Marek Stejskal

Reputation: 2708

I found a nice tutorial on database design, it may be worth some reading. I quickly made a DB design of what your database could look like, based on your info. It is not perfect as I don't know what your data actually look like, but the result may look a little bit like this:

enter image description here

If you set up the relations correctly, then getting reports like the one you suggested should be piece of cake.

If you were building the DB from scratch I would split the Extinguisher info into a separate ExtinguisherType table and the Inspectors as well.

Upvotes: 1

Related Questions