Help with a Union Query in Access

[caoamo]

I have a TargetChange_tbl in my database that contains the following fields:

eventID, date, employeeID, equipmentID, targetID, chamberPosition

Every time the maintenance team changes a target in a machine, it needs to be logged.

I want to build a query off of this table that returns only the LAST (most recent) occurance of a target change for a given chamber position. IE - what target was installed the last time it was changed.

I don't have a problem when only one chamber position is selected. Below is the code:

SELECT chamberPosition, date, targetID
FROM TargetChange_tbl
WHERE chamberPosition = 1
AND date = (SELECT MAX(date) FROM TargetChange_tbl WHERE chamberPosition = 1)
AND equipmentID = [Sputter tool?]

The problem lies when I try to do a UNION query and copy the same query 3 times, changing the chamberPosition =1 clause to 2 and 3 respectively in each SELECT statement of the UNIONS. The results come back correct for chamberPosition 1 and 3, but data for chamberPosition 2 is omitted. I have verified that there is valid data in the table for chamberPosition 2, but it continues to omit it. Any ideas?

Below is the UNION query I am trying:

SELECT chamberPosition, date, targetID
FROM TargetChange_tbl
WHERE chamberPosition = 1
AND date = (SELECT MAX(date) FROM TargetChange_tbl WHERE chamberPosition = 1)
AND equipmentID = [Sputter tool?]
UNION
SELECT chamberPosition, date, targetID
FROM TargetChange_tbl
WHERE chamberPosition = 2
AND date = (SELECT MAX(date) FROM TargetChange_tbl WHERE chamberPosition = 2)
AND equipmentID = [Sputter tool?]
UNION
SELECT chamberPosition, date, targetID
FROM TargetChange_tbl
WHERE chamberPosition = 3
AND date = (SELECT MAX(date) FROM TargetChange_tbl WHERE chamberPosition = 3)
AND equipmentID = [Sputter tool?]

 

[PHV]

A starting point:
SELECT A.*
FROM TargetChange_tbl AS A INNER JOIN (
SELECT chamberPosition, Max([date]) As LastDate FROM TargetChange_tbl GROUP BY chamberPosition
) AS L ON A.chamberPosition = L.chamberPosition AND A.date = L.LastDate

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

 

[spizotfl]

i don't know if it is an issue or not, but is there an order of precedence in UNION queries? is it possible that access is performing one union, then using that result set, performing the next union with the remaining query? just a thought