How to use Group By an actually see other fields too

[David Ball]

Hi,

I have a Query that is Grouped By KPI and then has MAX applied for Delivery Date. This shows the latest delivery date for each KPI, which is what I want.

The problem is also want to show other information associated with the latest date for each KPI. For example, the Equipment Number of the item being delivered, its Area Number, etc.

If I drag these fields in, Access Groups By them and the result is nothing like what I need.

How can I show this information for the latest delivery dates for each KPI?

I have always had this exact same problem every time I have ever tried to use Group By. It seems it only works if you want to show only the field you are Grouping BY. I find it so confusing.

Thanks very much

Dave

 

[MarkK]

Then maybe you don't want/need to GROUP BY at all. You can also sort by date DESC, and just pick the first record, like . . .

SELECT TOP 1 * FROM YourTable ORDER BY DeliveryDate DESC;

. . . which gives you all the data for the latest date.

 

[David Ball]

Thanks, But I need the latest date for each KPI?

I really need to use the Query design window, code is out of my league.

 

[MarkK]

Use your GROUP BY query as an input table on another query. Then you can link to other data by KPI and DeliveryDate.