Getting a specific record by date field

[narpet]

Hello all...

I'm having a problem getting a query to work. As a matter of fact I can't even seem to get a good start.

I have a table with the following example data

ID#     PRE_ID#     POST_ID#     DATE
1          1           2         1/1/2001
1          1           2         1/1/2004
1          1           2         1/1/2006     *
1          3           4         1/1/2004
1          3           4         1/1/2005

What I want to do is find the latest date (in this example 1/1/2006), and then see if the ID# is equal to the PRE_ID#. If it is I want to select that record. So in this example I would select the record that I've marked with a *.

But if my data looked like this:

ID#     PRE_ID#     POST_ID#     DATE
1          1           2         1/1/2001
1          1           2         1/1/2004
1          1           2         1/1/2003     
1          3           4         1/1/2004
1          3           4         1/1/2005

I would not select any record, because the record with the latest date (1/1/2005 in this example) does not have a matching ID# and PRE_ID#.

If anyone knows how I could write this query I would really appreciate it.

Thanks
Rod

[MMcCarthy]

Hi Rod

Try something like this ...

SELECT * FROM TableName
WHERE [ID#]=[PRE_ID#]
AND Max([Date]) = DMAX("[Date]", "TableName")
 

Mary

[narpet]

Thanks for the info.

When I enter this query exactly as you've given it to me (replacing names appropriately of course), and I run it, I get the error "Cannot have aggregate function in WHERE clause".

Any ideas?

[MMcCarthy]

Sorry try this ...

SELECT * FROM TableName
WHERE [ID#]=[PRE_ID#]
AND [Date] = DMAX("[Date]", "TableName")
 

Mary

[narpet]

First off... I really appreciate your help very much. We are getting closer.

Okay, now the query is working but it looks like it's giving me the absolute latest date over all records, so it's only pulling one record.

In the example above there can be many ID#s, PRE_ID#s and POST_ID#s. For any given ID# (of which there are thousands) I need to check the criteria noted above and see if I should select that ID# record.

So, I should actually get several (in my case tens to hundreds) records. This query seems to be finding the absolute latest date in the entire table and giving me that record (where the ID# and PRE_ID# also match).

Is there any way to do this so that all ID#s that match the above criteria will be selected?

Thanks so much for your help!
Rod

[JConsulting]

Sorry try this ...

SELECT * FROM TableName
WHERE [ID#]=[PRE_ID#]
AND [Date] = DMAX("[Date]", "TableName")
 

Mary

Option 6? :o)

SELECT *
FROM tblTest
WHERE date1=(select max([Date1]) from tblTest where [Range1]=[Range2]);

[MMcCarthy]

Try this and see what results you get ...

SELECT * FROM TableName
WHERE [ID#]=[PRE_ID#]
AND [Date] = DMAX("[Date]", "TableName", "[ID#]=" & [ID#])
 

Mary

[narpet]

Try this and see what results you get ...

SELECT * FROM TableName
WHERE [ID#]=[PRE_ID#]
AND [Date] = DMAX("[Date]", "TableName", "[ID#]=" & [ID#])
 

Mary

This appears to have been exactly what I needed. Of course, with thousands of records I have to do quite a bit of manual confirmation before I can be sure.

Thanks so much for your help, everyone!
Rod

[MMcCarthy]

This appears to have been exactly what I needed. Of course, with thousands of records I have to do quite a bit of manual confirmation before I can be sure.

Thanks so much for your help, everyone!
Rod

You're welcome.