Distinct/Date help

[ncar35b]

Hi,

I can't figure out how to get my query to get me exactly what I need.

Table:

eventID eventDate eventTime
11 7/3/2004 8:00
11 7/3/2004 10:00
11 7/5/2004 8:00
13 7/3/2004 8:00
13 7/4/2004 8:00
13 7/4/2004 10:00
14 7/2/2004 8:00
14 7/5/2004 8:00

All I want is for the top 3 unique eventIDs and corresponding dates >=#7/1/2004:

14 7/2/2004 8:00
11 7/3/2004 8:00
13 7/3/2004 8:00

I would think that "SELECT distinct TOP 3 eventID, eventDate FROM eventTime WHERE eventDate>=#7/1/2004# ORDER BY eventDate" would work, but it doesn't

Please help!

 

[Crowley16]

have you tried using >=#01/JAN/2004#

sql uses mm/dd/yy format...

 

[ncar35b]

thanks, but that didn't fix it. Is it something to do with the order I put distinct TOP 3 eventID, eventDate in?

Or maybe something needs brackets or parenthesis somewhere?

 

[Crowley16]

in what way does it not work?

are you getting more results than you're expecting
or
are you getting a error in your sql?

 

[Crowley16]

eh, just had a closer look at your sql statement:

SELECT distinct TOP 3 eventID, eventDate FROM eventTime WHERE eventDate>=#7/1/2004# ORDER BY eventDate

is this exactly what you are putting in?

if so, then what's the name of your table? Cos ur using eventTime as the FROM criteria, but eventTime is a field in the table...

 

[ncar35b]

for some reason, it gives me three results, but seems to ignore the "distinct" logic. However, when I remove "eventID" from the clause, it gives me the distinct eventDates. The problem is I need the distinct eventID and the eventDate that corresponds.

 

[Crowley16]

so can you post the EXACT sql statement that you're using?

and the 3 results that you are getting?

 

[ncar35b]

it doesn't make any sense to me, but renaming the field so it doesn't have the same name as the table seems to have fixed it. I'll add more dates and play around, but thanks for your help, crowley16!

 

[Crowley16]

it's because you've not named the fields explicitly...

if you used the format:

SELECT DISTINCT TOP 3 tblName.FieldName, tblName.FieldName... FROM tblName...

that would have worked...

but since your tableName is the same as a fieldName, I think that access was getting confused about which one to use...

but don't quote me on this...