Combining two queries

[John Ortt]

Hi everyone,

I am trying to combine the following two queries into a single one.......can
it be done?

SELECT tblRedline.PartNo, Max(tblRedline.DateValidTo) AS DateValidTo
FROM tblRedline
GROUP BY tblRedline.PartNo;

SELECT qryRedlineGrouped.PartNo, qryRedlineGrouped.DateValidTo,
Max(tblRedline.workability) AS Expr1
FROM qryRedlineGrouped INNER JOIN tblRedline ON
(qryRedlineGrouped.DateValidTo = tblRedline.DateValidTo) AND
(qryRedlineGrouped.PartNo = tblRedline.PartNo)
GROUP BY qryRedlineGrouped.PartNo, qryRedlineGrouped.DateValidTo;

Thanks in advance,

John

 

[Douglas J Steele]

I assume qryRedlineGrouped is what you've named the first query when you
save it.

What version of Access are you using? In Access 2000 and higher, you should
be able to do something like:

SELECT qryRedlineGrouped.PartNo, qryRedlineGrouped.DateValidTo,
Max(tblRedline.workability) AS Expr1
FROM
(SELECT tblRedline.PartNo, Max(tblRedline.DateValidTo) AS DateValidTo
FROM tblRedline
GROUP BY tblRedline.PartNo) AS qyRedlineGrouped
INNER JOIN tblRedline ON
(qryRedlineGrouped.DateValidTo = tblRedline.DateValidTo) AND
(qryRedlineGrouped.PartNo = tblRedline.PartNo)
GROUP BY qryRedlineGrouped.PartNo, qryRedlineGrouped.DateValidTo