By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,870 Members | 2,484 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

Getting a specific record by date field

P: 69
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

Expand|Select|Wrap|Line Numbers
  1. ID#     PRE_ID#     POST_ID#     DATE
  2. 1          1           2         1/1/2001
  3. 1          1           2         1/1/2004
  4. 1          1           2         1/1/2006     *
  5. 1          3           4         1/1/2004
  6. 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:

Expand|Select|Wrap|Line Numbers
  1. ID#     PRE_ID#     POST_ID#     DATE
  2. 1          1           2         1/1/2001
  3. 1          1           2         1/1/2004
  4. 1          1           2         1/1/2003     
  5. 1          3           4         1/1/2004
  6. 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
May 7 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Rod

Try something like this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TableName
  2. WHERE [ID#]=[PRE_ID#]
  3. AND Max([Date]) = DMAX("[Date]", "TableName")
  4.  
Mary
May 7 '07 #2

P: 69
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?
May 7 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TableName
  2. WHERE [ID#]=[PRE_ID#]
  3. AND [Date] = DMAX("[Date]", "TableName")
  4.  
Mary
May 7 '07 #4

P: 69
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
May 7 '07 #5

JConsulting
Expert 100+
P: 603
Sorry try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TableName
  2. WHERE [ID#]=[PRE_ID#]
  3. AND [Date] = DMAX("[Date]", "TableName")
  4.  
Mary

Option 6? :o)

SELECT *
FROM tblTest
WHERE date1=(select max([Date1]) from tblTest where [Range1]=[Range2]);
May 7 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this and see what results you get ...
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TableName
  2. WHERE [ID#]=[PRE_ID#]
  3. AND [Date] = DMAX("[Date]", "TableName", "[ID#]=" & [ID#])
  4.  
Mary
May 7 '07 #7

P: 69
Try this and see what results you get ...
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TableName
  2. WHERE [ID#]=[PRE_ID#]
  3. AND [Date] = DMAX("[Date]", "TableName", "[ID#]=" & [ID#])
  4.  
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
May 7 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
May 7 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.