Using MS Access for tool calibration log

[jarmbrister]

Greetings, all.

I’m a decided “newbie” when it comes to MS Access and databases in general. That I’ll state upfront. We’ve always used an Excel spreadsheet for tracking tool calibration in the past and it’s been adequate, but more recently the need for a better solution has become apparent.

I’ve created a simple database in Access with two tables. The first table “Instruments” contains information including:

• Tool ID

• Serial number

• Location

The second table “Calibration Log” contains the actual calibration records:

• Certificate number
• Last calibration date
• Next calibration (a calculated value from the last calibration date + 365.25)

Creating a relationship between the two tables was fairly straightforward and a simple query works as expected. Great so far. But there’s a problem, and here’s where I hope someone can point me in the right direction.

How might I structure a query which only returns the most recent calibration record for each tool?

For instance, if a tool has only one record in the calibration records table there’s no issue. A query returns the “next calibration date” from one table along with corresponding tool details from the other table as expected.

But upon re-calibration that tool will have another record against it (and subsequent ones each year). Running the query would return duplicate lines for the tool with multiple “next calibration” dates. I somehow need to filter out all but the most recent record for each tool.

I considered filtering to show only dates between x-y but if a tool is somehow missed in a calibration cycle it would be repeatedly missed going forward rather than showing up as being very far past-due.

I suspect that this may be something that needs Access and Excel working together but I’m not at all certain. Thanks in advance.

[paulfitzpatrick3 (Paul Fitz)]

Hi @jarmbrister ​, welcome to Access

You can create a Query and choose “Totals” on the “Query Design” ribbon. Then use “Group By” for the Tools Serial Number and use “Max” for the Date column. That should limit the results to the latest calibration date

Post back if you require more assistance

Good Luck!
Paul

[jarmbrister]

Thanks, Paul! I’ll give it a go tomorrow and post back with results/issues if any.

[jarmbrister]

I need to do a bit more testing but I think that did the trick, Paul. Thanks again!

[jarmbrister]

I seem to have spoken too soon. Adding a new calibration record produces the following result:

My query parameters:

@paulfitzpatrick3

[paulfitzpatrick3 (Paul Fitz)]

Hi @jarmbrister ​ , the problem is you have other fields that are causing the query to display more than you want. Create a query that just has the [Serial Number] and [Last Calibration] and verify it displays the desired results. Then create a second query and use the first query and the other tables you need to display all the other fields that you require.

[jarmbrister]

Got it. Not sure exactly where I’d gone off the rails with the query but I started over as you suggested and built it slowly, refreshing in between each change. I think it’s showing me what I need now.

Sincerely appreciate your help!

[paulfitzpatrick3 (Paul Fitz)]

@jarmbrister ​ Glad you got it working. Sometimes you need to use multiple Queries tied together to get the desired result. If you look at your first Query it is telling Access to Group on the values of multiple fields, so anytime one of those fields has a Unique value that record will be displayed. So for example because you added [Next Calibration] you ended up with the 2 Records being displayed since the date values are different and you chose ‘Group By’ for that field. You could have modified that to also be ‘Max’ but with all the other fields other problems may pop up so best to keep it simple and have the initial filter query and then use that in conjunction with the other tables/queries to build the final one.

[spiceuser-underham]

Excuse my possible error as I haven’t used Access since retiring nearly 10 years ago. It looks like the tables are joined where the Instrument ID is equal in both tables which is fine as it is. However if a record exists in the Instruments table but not in the Calibration Log (new tool maybe?) then no record will be returned. Changing the link to a Left Join will list all Instruments regardless of having a Calibration Log which ensures no Instrument gets overlooked.

[jarmbrister]

Good morning, @spiceuser-underham ​. Thanks for pointing that out. Indeed, it seems when a record is added to the “Calibration” table it will not appear in a query unless there is a corresponding record in the “Instruments” table.

I tried changing this to a left join but records in “Calibration” are still omitted. Previous setting highlighted in the screenshot, present setting selected.

Off the top of your head, can you suggest why the type 3 join doesn’t work as expected?

[spiceuser-underham]

Type 3 doesn’t work because you cannot have a Calibration Log for an Instrument that doesn’t exist (at least as far as Access is concerned). Change the join to a 2 so you get all Instruments and the Calibration Log where there is a valid record that meets your query criteria.

Also check you are joining the tables on the Instrument ID field in both tables

[jarmbrister]

Sorry – I should’ve mentioned that Type 2 doesn’t work either. And, unless I’m overlooking something obvious it’s not the behavior I’d want. I need to see a result for any ID that is past-due in the calibration table regardless of a corresponding entry in the instruments table.

Perhaps my query is still wonky?

[spiceuser-underham]

Sorry jarmbrister but I haven’t got access to Access and not used it for 10 years so it may need a proper expert. The only thing I can see is that your tables seems to have swopped places left to right in the query since the previous screenshots. If this was done between running the queries then it could be the type 2 and type 3 queries were effectively both the same. From what I can see the latest screenshot is showing the wrong join, it needs to be Instruments going to Calibration Log. Try reversing it (it used to be a right click on the line joining the fields) and try again. Good luck!

[jarmbrister]

No problem, & thanks for what help you were able to offer. Maybe someone else will have an idea of where the hangup lies.

[june7]

Linking should use designated primary key field. If you want to link on [Instrument ID] then this should be designated as primary key in Instruments and values saved to Calibration. Exactly what is Instruments field?

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

[riktek]

A few thoughts if you’re still fiddling with this:

• Remove spaces from the names of tables and fields. E.g., do “InstrumentID”, not “Instrument ID”. Access can be fussy about this.
• Thinking ahead, prefix table names with “tbl” because if you want to create a form later and it and the table are both named “Instruments”, Access will have to guess which you mean. So, do “tblCalibrationLog” (table prefix, no spaces) for the table and “frmCalibrationLog” (form prefix, no spaces) for the hypothetical form.
• Change the tblCalibrationLog primary key name from “ID” to “CalibrationLogID”. This is more for clarity and consistency.
• Make sure, in Table Design View, that LastCalibration (no spaces) and NextCalibration are the Date/Time data type.
• Change the tblInstruments primary key from Instruments to InstrumentID (no spaces). Be sure also to set it as an AutoNumber in tblInstruments. In tblCalibrationLog, rename the foreign key to “InstrumentID” (also no spaces) if you haven’t already.
• This is nit-picky but “Description” is a reserved word in Access for some purposes. Just to avoid random glitches, rename the field to something like “Detail”.
• Down the road, and depending on how many records you’re dealing with, it may be useful to move Type, Manufacturer, and Location to separate tables.
• Go to Database Tools > Relationships. There, you’ll have a graphical way to establish table relationships similar to that in Query Design View. Relate the two tables by InstrumentID and be sure the “Enforce Referential Integrity” box is checked. This will ensure that the data in the two tables is properly related as it goes in, with no duplicates, so that later, your queries will return reliable results.
• In Query Design View, set the join to “all the records from tblCalibrationLog and only those records from tblInstruments where the joined fields are equal”. Whether Access puts one or the other table on the right or left doesn’t matter.

Together, this should let you sort or filter tblCalibrationLog on NextCalibration to get the info you need.

HTH, and good luck!