Newbie SQL Question

[ncar35b]

Hi, the table below is in MS Access and I'm trying to pull out only one eventDate (the date nearest to today) for each unique eventID in order by date. (the times of the event are irrelevent)

I would like this result:

ID eventID eventTime eventDate
63 23 11am 7/23/2004
61 22 11am 7/28/2004
72 26 8pm 7/29/2004
69 25 5pm 8/1/2004
65 24 11am 8/13/2004


This is my table:

ID eventID eventTime eventDate
61 22 11am 7/28/2004
62 22 2pm 7/28/2004
63 23 11am 7/23/2004
64 23 2pm 7/23/2004
65 24 11am 8/13/2004
66 24 2pm 8/13/2004
68 25 2pm 8/1/2004
69 25 5pm 8/1/2004
70 25 2pm 8/2/2004
71 25 5pm 8/3/2004
72 26 8pm 7/29/2004

Thank you for any help!!!

 

[SkipVought]

Hi,

[tt]
Select First(ID), First(eventID), First(eventTime), eventDate
From YourTable
Group By eventDate
Order By eventDate
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

Something like this ?
SELECT A.ID, A.eventID, A.eventTime, A.eventDate
FROM tblEvents A INNER JOIN
(SELECT eventId, Max(eventDate+evenTime) As MaxDateTime GROUP BY eventID) B
ON A.eventID=B.eventID
WHERE (A.eventDate+A.evenTime)=B.MaxDateTime
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ncar35b]

SkipVought, thanks for your help. I'm close now. Your SQL statement...

Select First(ID), First(eventID), First(eventTime), eventDate From YourTable Group By eventDate Order By eventDate

...gave me this:

ID eventID eventTime eventDate
61 22 11am 6/28/2004
63 23 11am 7/23/2004
72 26 8pm 7/29/2004
68 25 2pm 8/1/2004
70 25 2pm 8/2/2004
71 25 5pm 8/3/2004
65 24 11am 8/13/2004

I need the results your query gave me, but with only one distinct eventID (your query has 25 3 times).

Thanks for any help!

 

[JonFer]

Don't use the First() aggregate function because what it returns depends on the order of the records. You should use Max() and Min() instead. Also, it would be better to combine the date and time fields - there are functions to parse out the date and time for display. This SQL will give you event dates closest to the current date (ignoring your time value):

Select EventID, EventDate
From Events
Where Abs(Now()-EventDate))=
(Select Min(Abs(Now()-EventDate)))
From Events as a where a.EventID=Events.EventID)

If you want the closest EventDate excluding past ones, you can use a simpler SQL:

Select EventID, EventDate
From Events
Where EventDate =
(Select Min(EventDate)
From Events as a
Where a.EventID=Events.EventID And a.EventDate>=Now()
)

 

[PHV]

JonFer, you will get 2 times the 22 eventId.
ncar35b, just to know, have you tried my code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

PHV - Not if the date and time fields are combined as I suggested. The query can also be modified to combine the two fields before doing the calculation with Now().

ncar - If you try PHV's solution, be sure to include a FROM clause in the inline table statement (right after the INNER JOIN). It should return the latest event date in the table for each event.

 

[PHV]

Good catch JonFer :~/
SELECT A.ID, A.eventID, A.eventTime, A.eventDate
FROM tblEvents A INNER JOIN
(SELECT eventId, Max(eventDate+evenTime) As MaxDateTime
FROM tblEvents GROUP BY eventID) B
ON A.eventID=B.eventID
WHERE (A.eventDate+A.evenTime)=B.MaxDateTime
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ncar35b]

Thank you all so much! I modified JonFer's query slightly to get it to work. (added the distinct to the front and order by to the end).

Select distinct EventID, EventDate From eventTime Where EventDate = (Select Min(EventDate) From eventTime as a Where a.EventID=EventTime.EventID And a.EventDate>=Now()) order by eventDate

PHV - thanks for your help. I understand exactly what you were getting at, it's just that the eventTime field isn't a Time/Date field. It's a text field (sometimes 'noon' or 'afternoon' need to be options) so I couldn't concatinate the categories.

 

[PHV]

Glad you solved your issue, but the result isn't what asked, as you don't get the ID nor the eventTime.
And what about this ?
SELECT A.ID, A.eventID, A.eventTime, A.eventDate
FROM eventTime A INNER JOIN
(SELECT eventId, Max(eventDate & eventTime) As MaxDateTime
FROM eventTime GROUP BY eventID) B
ON A.eventID=B.eventID
WHERE (A.eventDate & A.eventTime)=B.MaxDateTime
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244