Help with a query to yield the most recent event grouped by client

[Steve]

I have a clientTable (clientID[key]) linked in a 1:many relationship with an
eventTable (eventID[key], clientID, eventType, eventDate, eventDescription).
I would like a query to provide only the most recent eventDate and
description for each eventType grouped by client (clientID) and eventType as
follows:

client 1/ eventtype 1/ eventdate(most recent of all eventtype 1's for client
1)/event description
client 1/eventtype 2/ eventdate(most recent of all eventType 2's for client
1) /event description
client 2/eventtype 1/ eventdate(most recent of all eventType1's for client
2)/event description
client 2/eventtype 2/eventdate(most recent of all eventType2's for client
2)/event description

Thank-you for any assistance.

 

[SteveS]

I have a clientTable (clientID[key]) linked in a 1:many relationship with an
eventTable (eventID[key], clientID, eventType, eventDate, eventDescription).
I would like a query to provide only the most recent eventDate and
description for each eventType grouped by client (clientID) and eventType as
follows:

client 1/ eventtype 1/ eventdate(most recent of all eventtype 1's for client
1)/event description
client 1/eventtype 2/ eventdate(most recent of all eventType 2's for client
1) /event description
client 2/eventtype 1/ eventdate(most recent of all eventType1's for client
2)/event description
client 2/eventtype 2/eventdate(most recent of all eventType2's for client
2)/event description

Thank-you for any assistance.

First create a new query. Cancel Show Table dialog and switch to SQL view.
Paste in the following SQL:

SELECT EventTable.clientID, EventTable.eventType, Max(EventTable.eventDate) AS
MaxOfeventDate
FROM EventTable
GROUP BY EventTable.clientID, EventTable.eventType;


Save the query as Query5 (for now).

Create another query. Add the tables ClientTable and EventTable and the query
Query5.

Make a link between ClientTable.ClientID and EventTable.ClientID.

Make a link between EventTable.EventDate and Query5.MaxOfEventDate
Make a link between EventTable.ClientID and Query5.ClientID
Make a link between EventTable.EventType and Query5.EventType

Now add whatever fields you want from ClientTable and EventTable the design
grid. Run the query (click on the red !).


In effect, Query5 is acting like a filter on the EventTable. The relationship
is Option1 - "Only include rows where the join fields from both tables are
equal" for all joins.


HTH

 

[Steve]

I have a clientTable (clientID[key]) linked in a 1:many relationship with an
eventTable (eventID[key], clientID, eventType, eventDate, eventDescription).
I would like a query to provide only the most recent eventDate and
description for each eventType grouped by client (clientID) and eventType as
follows:

client 1/ eventtype 1/ eventdate(most recent of all eventtype 1's for client
1)/event description
client 1/eventtype 2/ eventdate(most recent of all eventType 2's for client
1) /event description
client 2/eventtype 1/ eventdate(most recent of all eventType1's for client
2)/event description
client 2/eventtype 2/eventdate(most recent of all eventType2's for client
2)/event description

Thank-you for any assistance.

First create a new query. Cancel Show Table dialog and switch to SQL view.
Paste in the following SQL:

SELECT EventTable.clientID, EventTable.eventType, Max(EventTable.eventDate) AS
MaxOfeventDate
FROM EventTable
GROUP BY EventTable.clientID, EventTable.eventType;


Save the query as Query5 (for now).

Create another query. Add the tables ClientTable and EventTable and the query
Query5.

Make a link between ClientTable.ClientID and EventTable.ClientID.

Make a link between EventTable.EventDate and Query5.MaxOfEventDate
Make a link between EventTable.ClientID and Query5.ClientID
Make a link between EventTable.EventType and Query5.EventType

Now add whatever fields you want from ClientTable and EventTable the design
grid. Run the query (click on the red !).


In effect, Query5 is acting like a filter on the EventTable. The relationship
is Option1 - "Only include rows where the join fields from both tables are
equal" for all joins.


HTH

THANKS, THAT WORKED GREAT. MUCH APPRECIATED. STEVE

 

[SteveS]

:

I have a clientTable (clientID[key]) linked in a 1:many relationship with an
eventTable (eventID[key], clientID, eventType, eventDate, eventDescription).
I would like a query to provide only the most recent eventDate and
description for each eventType grouped by client (clientID) and eventType as
follows:

client 1/ eventtype 1/ eventdate(most recent of all eventtype 1's for client
1)/event description
client 1/eventtype 2/ eventdate(most recent of all eventType 2's for client
1) /event description
client 2/eventtype 1/ eventdate(most recent of all eventType1's for client
2)/event description
client 2/eventtype 2/eventdate(most recent of all eventType2's for client
2)/event description

Thank-you for any assistance.

First create a new query. Cancel Show Table dialog and switch to SQL view.
Paste in the following SQL:

SELECT EventTable.clientID, EventTable.eventType, Max(EventTable.eventDate) AS
MaxOfeventDate
FROM EventTable
GROUP BY EventTable.clientID, EventTable.eventType;


Save the query as Query5 (for now).

Create another query. Add the tables ClientTable and EventTable and the query
Query5.

Make a link between ClientTable.ClientID and EventTable.ClientID.

Make a link between EventTable.EventDate and Query5.MaxOfEventDate
Make a link between EventTable.ClientID and Query5.ClientID
Make a link between EventTable.EventType and Query5.EventType

Now add whatever fields you want from ClientTable and EventTable the design
grid. Run the query (click on the red !).


In effect, Query5 is acting like a filter on the EventTable. The relationship
is Option1 - "Only include rows where the join fields from both tables are
equal" for all joins.


HTH

THANKS, THAT WORKED GREAT. MUCH APPRECIATED. STEVE

You're welcome, Steve