Recent events

C

CurtainMary

Hi all
I record a number of events with their dates
Different events may occur on the same date
On some days no events occur.
I need to retrieve records for each event for the six most recent occurrences.
Please help.
 
K

Ken Sheridan

Try this:

SELECT *
FROM Events as E1
WHERE EventDate IN
(SELECT TOP 6 EventDate
FROM Events As E2
WHERE E2.Event = E1.Event
ORDER BY EventDate DESC);

or this:

SELECT *
FROM Events as E1
WHERE
(SELECT COUNT(*)
FROM Events As E2.
WHERE E2.Event = E1.Event
AND E2.EventDate >= E1.EventDate) < = 6;

The former should be used if the same event can occur twice on the same date
as the latter could fail in some circumstances if that is the case, returning
less than 6 (incorrectly). In that situation you would find the former
returns more than 6 (correctly) if the same event occurred twice on the 6th
previous date.

Ken Sheridan
Stafford, England
 
S

Smartin

CurtainMary said:
Hi all
I record a number of events with their dates
Different events may occur on the same date
On some days no events occur.
I need to retrieve records for each event for the six most recent occurrences.
Please help.
Click to expand...

This works... there are probably better ways though:

SELECT *
FROM Events AS E1
WHERE E1.EventDate IN
(SELECT TOP 6 E2.EventDate
FROM EVENTS AS E2
WHERE E2.EventID = E1.EventID
ORDER BY E2.EventDate DESC)
ORDER BY E1.EventID, E1.EventDate DESC;
 
S

Smartin

CurtainMary said:
Thank you for responding
I shall have a go and see if I can make it work
Click to expand...

You're welcome. Check out Ken Sheridan's response as well. My solution
is identical to one of his, but he offers another flavor as well.
 
C

CurtainMary

Thanks again, but I how do I incorporate your solution in to my query that
also limits other fields?
 
K

Ken Sheridan

Put the additional criteria in the subquery, e.g.

SELECT *
FROM Events as E1
WHERE EventDate IN
(SELECT TOP 6 EventDate
FROM Events As E2
WHERE E2.Event = E1.Event
AND (SomeTextField = "Some Value"
OR SomeNumberField = 42)
ORDER BY EventDate DESC);

or:

SELECT *
FROM Events as E1
WHERE
(SELECT COUNT(*)
FROM Events As E2.
WHERE E2.Event = E1.Event
AND E2.EventDate >= E1.EventDate
AND (SomeTextField = "Some Value"
OR SomeNumberField = 42)) < = 6;

You can add an ORDER BY clause to the outer query if you wish, but don't do
so if using the query as the RecordSource for a report; it will only slow it
down Use the report's internal Sorting and Grouping mechanism, instead.

Ken Sheridan
Stafford, England
 
K

Ken Sheridan

Put the additional criteria in the subquery, e.g.

SELECT *
FROM Events as E1
WHERE EventDate IN
(SELECT TOP 6 EventDate
FROM Events As E2
WHERE E2.Event = E1.Event
AND (SomeTextField = "Some Value"
OR SomeNumberField = 42)
ORDER BY EventDate DESC);

or:

SELECT *
FROM Events as E1
WHERE
(SELECT COUNT(*)
FROM Events As E2.
WHERE E2.Event = E1.Event
AND E2.EventDate >= E1.EventDate
AND (SomeTextField = "Some Value"
OR SomeNumberField = 42)) < = 6;

You can add an ORDER BY clause to the outer query if you wish, but don't do
so if using the query as the RecordSource for a report; it will only slow it
down Use the report's internal Sorting and Grouping mechanism, instead.

Ken Sheridan
Stafford, England
 
C

CurtainMary

Thanks Ken (also Smartin)
I have used design mode for making Queries a long time. I knew about the SQL
equivalent but never used that. To make myself understand what was going on,
I did a MakeTable Query first containing a limited list of events and dates
then used your bits of programming to get the top 6 results for each event.
BINGO it worked.
Thanks again!!

CurtainMary said:
Thank you for replying again. I shall try it in the morning.
Click to expand...
 
Top