Get the highest record only.

  • Thread starter Thread starter Simon Hart
  • Start date Start date
S

Simon Hart

Hi folks.

Tearing my hair out here. :)

Noddy example as follows
I have two tables, Clients and Events. Both have ClientID in them and they
have a one-to-many join, as in one client/many events. The primary key of
the Events table is ClientID+Date+Type, primary of Clients is ClientID.

Anyway, what I need is a "make-table" query that will give me one record for
each clientID, where the Events data is from the Events record where the
date is the most recent. Sort of if you were looking at the query in design
mode, what you would put in the criteria for Date that amounts to
"=MostRecent(Client.Date)".

I think that sort of explains it. I've got an example .mdb(208Kb) that might
make it more clear if anyone wants it.

Thanks for your help all.
 
If you are using Date as your field, you need to change it to something else
because Date is reserved word in Access and could cause you problems. I am
using EventDate in my example of a Make-Table query. Also calling the new table
CurrentEvents.

SELECT Events.ClientID, Events.EventDate, Events.EventType INTO CurrentEvents
FROM Events
WHERE (((Events.EventDate)=(SELECT Max(EventDate) FROM Events)));

What are you going to do if someone attended 2 different types of events on the
same date?

HTH,
Debbie


| Hi folks.
|
| Tearing my hair out here. :)
|
| Noddy example as follows
| I have two tables, Clients and Events. Both have ClientID in them and they
| have a one-to-many join, as in one client/many events. The primary key of
| the Events table is ClientID+Date+Type, primary of Clients is ClientID.
|
| Anyway, what I need is a "make-table" query that will give me one record for
| each clientID, where the Events data is from the Events record where the
| date is the most recent. Sort of if you were looking at the query in design
| mode, what you would put in the criteria for Date that amounts to
| "=MostRecent(Client.Date)".
|
| I think that sort of explains it. I've got an example .mdb(208Kb) that might
| make it more clear if anyone wants it.
|
| Thanks for your help all.
|
| --
| Simon Hart
|
| In space nobody can hear you scream.
 
Hi Debbie.

Thank you, exactly what I needed! Ooops, forgot all about date... I'll
change that. Fortunately, two events of the same type on one date can't
happen.

Ciao.
 
Back
Top