Query is misbehaving.

[dgoreth]

Hello!

Hey guys. I am pretty new at Access, so bear with me a little.

I have designed a database that is for calibration of all of our in-house tools. With this, I would like to add a report function that tells me what is due in the next week. I have one Table that contains all of the calibration records (tool ID, calibration date, and so fourth) and another table that is a master list of all of the tools. This table contains Tool location on the production floor, Tool Type, Calibration cycle length (in days) among other things. For ease of entry, most of my database items are lookup table values. This locks the person entering the information in to a set of pre-defined values. (calibration schedules being pulled from a table that contains amount of days for each schedule (daily,1; weekly,7; monthly,30; bi-monthly,60, etc)

What I need to do is take the default cycle length, add that to the date of last calibration, and print a report that shows tool ID, last calibration date, due date (calculated and never stored in the table), and location of the tool. I have this working, to a point....

So far, I have a sub query that pulls the correct info, and does a date calculation.

For the life of me, I cannot figure out why it returns multiple records. It should only show one tool, the date it was calibrated, the day it's due. But I am getting multiple results. I know I'm going to have to add a filter for only showing the last time a tool was calibrated (that's a fight for another day), but at this stage of the development, there is only one calibration record per tool.

I have attached a screenie to show what my query is set up like. If anyone can help me, I would really appreciate it.

 

[plog]

You have a cartesian product (http://en.wikipedia.org/wiki/Cartesian_product). Since you have no link between your two data sources, for every record in Tools you are pulling in every record from CallRecords. Resulting in [Number of Tools Records] x [Number of CallRecords Records] results.

You need to join the two datasources in your query.

 

[dgoreth]

You need to join the two datasources in your query.

I thought something like that, and did actually try joining CalID to NewToolName, but I get a type mismatch since the CalID is actually a lookup to the NewToolName field, and it won't allow it. Is there some other trick I should be aware of?

 

[dgoreth]

So there is no way to join these two fields? I really can't change the data types on them without breaking the rest of my functionality. The Calibration ID number in the calibration database is a lookup to the string in the "NewToolName" Field so that only the tools that are in the tool database can have a valid calibration record. I would have assumed these were already joined because of this. But, again, I'm still a bit wet behind my ears.

 

[plog]

The trick is to use foreign keys (http://en.wikipedia.org/wiki/Foreign_key) in your data. I don't know how those 2 fields relate, but I see they each have an ID field. When you want to relate two tables you use the primary key value of one table as the foreign key value in the second, that way you can link them using that value.

 

[dgoreth]

Here is how they relate to each other:

For example, we receive in a new 1" micrometer. We enter the tool into the "Tool" database, giving it its own unique calibration ID based upon location of the tool and the number of tools issued to that spot. (at the Swiss CNC machine 01 we have a Mic named S01-001 , a caliper named S01-002. If the Mic goes out of spec and has to be replaced, we would call it S01-003). Along with this information is the calibration interval required per our spec. This is pulled from another Table containing the valid calibration cycles per our Quality Standards like I described above. These calibration cycles need to be on a "per tool" basis because different tool types in different departments need to have different cycle lengths. (Inspection level tools are calibrated much more frequently than production level tools)

At calibration time, I use a combo box to "pull down to" the "ToolID" found in the Tool Database. this ensures that the tool being calibrated is in our system and active. Eventually, I want to add in filters that remove the tools that are removed from production for whatever reason, but for now, it's fine. This "Name" is stored in the Calibration Database along with the vital information of that calibration for that tool.

When all is said and done, I need to have a query that finds the last time a tool was calibrated, look up the calibration cycle length (in days), add the two together to calculate the calibration due date and if that value is less than today's date plus 7 days, show it on a report.

I really, really hate the idea of duplicating data across multiple tables, but I'm starting to think that I need to have the calibration cycle length be stored in the calibration records as a lookup to the tools table and just do all of the calculations based upon that one table. But I know that's bad data practice...

 

[dgoreth]

Awww man... seriously? I just got it working. I was trying to join "CalID" to "NewToolName". As soon as I tried linking "ID" to "ID" it came to life. Thanks for the help.

 

[plog]

These calibration cycles need to be on a "per tool" basis

Relating calibrations to tools shouldn't take 4 paragraphs to explain. So, I skimmed your explanation and that last nugget stuck out.

To achieve that relationship your data should look something like this:

Tools
ToolID, ToolType, ToolAcquisitionDate
1, Micrometer, 3/13/2014
2, Digital Scales, 4/1/2008
3, Micrometer, 5/8/2009

Calibrations
CalibrationID, ToolID, CalibrationDate
1, 1, 4/1/2014
2, 1, 5/1/2014
3, 2, 5/10/2010
4, 1, 6/1/2014
5, 3, 7/7/2011
6, 2, 9/28,2013

Looking at the Calibration table you can see that the first micrometer (ToolID=1) was calibrated 3 times, the 2nd micrometer(ToolID=2) was calibrated once and the digital scales were calibrated twice.

That's how your data should be set up and be related. Forget, forms and queries and reports, focus on getting your tables structured properly.