Problem with max function

[ccox]

I have 2 tables related by Project_ID. Table 2 has a text field for status and a date field for date of status.
I want all of the fields in Table 1 and the status from Table 2 with the most recent update for a report.
My query has all of the records from Table 1 with "Group By" in Totals. It has the date field from Table 2 with "Max". As long as I don't add the status field from Table 2 all is well. But I really need the status and the date. Any help would be greatly appreciated!

 

[gemma-the-husky]

I had exactly the same problem and couldn't solve it a couple of days ago. You would think you could get at the text value for the max row, wouldn't you, but as soon as you put it in, you get lots of subtotals.

I ended up having to sort by descending max values, and inserting all the values into a table, which was keyed to exclude duplicates, so I only got the max row in my table. A real fudge, and I didn't like it, except that it means my table is permanently there and only needs updating now and again.

I would be interested if you get a proper fix.

 

[ccox]

Thanks! That at least gives me a work around until we find the absolute fix

 

[ccox]

I used the sql provided by ByteMyzer in the "Max of Count" thread and it solved my problem beautifully!!! Day saved!