Max versus Group By problem (1 Viewer)

[Mr Smin]

I want to get the most recent item per client from a table.

ItemID (primary key, autonumber)
Date (date)
ClientID (foreign key, integer)

If I select ClientID (group by), Date (Max), then I get the results I'm expecting, but I want to also return the ItemID so that I can join some other tables. I can't find a way to do that without changing the query results.

Please could someone tell me what I've missed?

Thanks.

 

[MStef]

Try to do it via 2 queries.
Look at "DemoGroupByMaxA2002.mdb" (attachment, zip).
Look at Table1. Run Query2.

 

[Mr Smin]

Hi MStef,

Thanks for posting the example, but it won't work in my case because I can't guarantee that I will never have two events on the same date.

Apologies for the layout - is there some way to post tables here? It even strips out my extra spaces!

If I add a row to Table1 which has a date which already exists in the table but for another client
ItemID Date1 ClientID
11 25/04/2010 3008

I get the following result
ItemID Date1 ClientID
5 30/04/2010 3008
9 25/04/2010 5000
10 28/04/2010 4001
11 25/04/2010 3008

 

[MStef]

Hello Mr Smit!
Ok, it demand some change in Query2, (it became a "Make Table Query").
I made some change in Query2, I made a Module1, function "GetClient",
I made a Query3 on the NewTable1, I made a Form1.

Open Form1 and try.
*****************
Maybe somebody else knows another way for this.
Look at attachemnt (zip).

 

[John Big Booty]

Instead of creating a table I've used 4 queries to get the required result.

I've started with basically the same query (QueryA) that MStef used in the first sample. I've then used that in a second select query and used an expression to create a pseudo key using the max of the date and the group clientID (QueryB), I've then create another select query (QueryC) to select all the data in the table and one again used an expression to create a pseudo key using the date and clientID the final query (QueryResult) then uses QueryB and QueryC linked via the pseudo key to extract the required results.

 

[MStef]

Hello John!
My congratulation, a very good solution.

 

[John Big Booty]

Hello John!
My congratulation, a very good solution.

Thank you

As missinglinq is fond of reminding us there is always more than one way to skin a cat

 

[Mr Smin]

Thanks very much both of you - that looks to have done it!