sub-query question 2

[GerryGoldberg]

I have at table (tblGRF) that contains:
clientID (long integer)
update (date)
status (yes/no)
A record is added to this table each time a client's status is changed.

So, given a specific date, I need to be able to create a list of clients (with their status) where their Update value is <= to the specific date. This looks like a sub-query application to me, but I keep selecting all of the records in the table. Here is my query:

SELECT tblgrf.clientID, tblgrf.update,tblgrf.owf, tblgrf.status
FROM tblgrf
WHERE (((Exists (select max(date_of_update) <= #9/30/2000# FROM tblGRF GROUP BY client_ID))=True));

Thanks,

Gerry Goldberg

 

[downwitchyobadself]

Sorry, I don't get it. How many records are you trying to return? If clientID x has the following records

Update    Status
5/31/01   Yes
6/1/01    No
6/2/01    Yes

and your cutoff date is 6/1/01, do you want to see the first two? One only per client? What's date_of_update? Why can't you just return all records where Update <= a certain test date? And just by the way, &quot;Update&quot; is not such a hot field name, since it's an Access reserved word. It's like calling your field &quot;Name&quot; or &quot;Field&quot;.

 

[tlbroadbent]

Try this query.

SELECT a.clientID, a.[update], a.owf, a.status
FROM tblgrf a
WHERE tblgrf.[update]=(select max([update]) FROM tblGRF Where ClientID=a.ClientID And [Update]<=#9/30/2000#);

NOTE: I recommend changing the column named Update to a different name as Update is a keyword. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[GerryGoldberg]

I really only want one record per client, and it should reflect the status of the most recent update as of the update&quot; date (also called date_of_update). Thanks for your help.

Gerry Goldberg