select table data if the value does not include criteria

[erfigge]

I have two tables, One with instrument numbers, and then another table joined with instrument numbers that has all the data for each instrument. What I need to do is search the last records for each instrument and see if it is within the last 6 months. If the last record is not within the last 6 months then it should be included within the reported data.

When I report the data I just want to show the instrument number (from the key table) and the last date (from the data table).

I tried to search on this but I couldn't come up with the right wording to find a solution. Anybody know how to do this?

 

[lespaul]

Here's the query to get the second part of your question:

I just want to show the instrument number (from the key table) and the last date (from the data table).

SELECT A.InstrumentNumber, B.LastDate FROM
FROM Instruments As A
INNER JOIN (SELECT InstrumentID, Max(DateField) As LastDate FROM DetailTable GROUP BY InstrumentID) As B ON A.InstrumentID = B.InstrumentID

In order for an instrument to show up in the query, it must have detail information. If you have instruments with no detail then you would need to change the INNER to a LEFT.

Once you get this working, you will just need to add the date criteria in the WHERE clause.

HTH

Leslie

 

[erfigge]

Ok, I got lost on this line:

FROM Instruments As A


The first table is "Instrument Names" and the second is "Calibration Data".

In instrument names is the field "Instrument Number" which is what keys instrument names to calibration data.

Sorry to be dense on this one.....

 

[PHV]

Perhaps something like this ?
SELECT A.[Instrument Number], Max(B.[Date Field]) AS LastDate
FROM [Instrument Names] AS A INNER JOIN [Calibration Data] AS B ON A.[Instrument Number] = B.[Instrument Number]
GROUP BY A.[Instrument Number]
HAVING Max(B.[Date Field])<DateAdd('m',-6,Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886