Select MAX date query

[penfoldnz]

Hi, I am having a few problems extracting data from a databaseextract I have been provided. I need to extract all completed entrys (some are blank) based on the latest date. The below works(query 1 - 230 records), however when I try to include the additional fields needed it no longer works (see query 2 - 1291 results - all for evry month)

Any ideas on where I am going wrong? btw, the extract the admin team have provided, does not include key fields.

QUERY 1
SELECT Max([Completed APAR].Month) AS MaxOfMonth, [Completed APAR].Client, [Completed APAR].Group, [Completed APAR].Region
FROM [Completed APAR]
GROUP BY [Completed APAR].Client, [Completed APAR].Group, [Completed APAR].Region;

QUERY 2
SELECT Max([Completed APAR].Month) AS MaxOfMonth, [Completed APAR].Client, [Completed APAR].Group, [Completed APAR].Region, [Completed APAR].Referenceable, [Completed APAR].[Meets Overall Service Delivery Requirements], [Completed APAR].[Mainframe service], [Completed APAR].[Midrange Service], [Completed APAR].[Desktop/Laptop Service], [Completed APAR].[Network Services], [Completed APAR].[Helpdesk], [Completed APAR].[Collaborative Computing], [Completed APAR].Security, [Completed APAR].[Distributed print/file servers]
FROM [Completed APAR]
GROUP BY [Completed APAR].Client, [Completed APAR].Group, [Completed APAR].Region, [Completed APAR].Referenceable, [Completed APAR].[ Meets Overall Service Delivery Requirements], [Completed APAR].[Mainframe service], [Completed APAR].[Midrange Service], [Completed APAR].[Desktop/Laptop Service], [Completed APAR].[Network Services], [Completed APAR].[Helpdesk], [Completed APAR].[Collaborative Computing], [Completed APAR].Security, [Completed APAR].[Distributed print/file servers];

 

[lupins46]

Don't understand.
You say that Q1 'works' but you also say that you are selecting just 'completed' entries. There is no selection going on in this query.
On the other hand if Q1 'works' I see no reason why Q2 doesn't work.

 

[PHV]

Provided you've saved QUERY 1 as, say, qryMaxMonth:
SELECT [Completed APAR].Month, [Completed APAR].Client, [Completed APAR].Group, [Completed APAR].Region, [Completed APAR].Referenceable, [Completed APAR].[Meets Overall Service Delivery Requirements], [Completed APAR].[Mainframe service], [Completed APAR].[Midrange Service], [Completed APAR].[Desktop/Laptop Service], [Completed APAR].[Network Services], [Completed APAR].[Helpdesk], [Completed APAR].[Collaborative Computing], [Completed APAR].Security, [Completed APAR].[Distributed print/file servers]
FROM [Completed APAR] INNER JOIN qryMaxMonth
ON [Completed APAR].Month = qryMaxMonth.MaxOfMonth AND [Completed APAR].Client = qryMaxMonth.Client AND [Completed APAR].Group = qryMaxMonth.Group AND [Completed APAR].Region = qryMaxMonth.Region;

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