Assistance with query.

[Coder 23]

Below is a sample section of a table. It's sorted
by "Agent_ID ASC, Month_Reviewed ASC, Create_Date DESC,
Create_Time DESC;"

I'm trying to return only one unique month per agent which
is the most recent based on Create_Date and Create_Time.
Is there any way to do this in one shot(query)?

(the asterisks were added to indicate only the records
that should be returned.)

Agent_ID Month_Reviewed Create_Date Create_Time
15508 ** February 2003 9/29/2003 7:34:27 AM
15508 ** March 2003 9/29/2003 7:34:27 AM
15508 ** August 2003 8/26/2003 9:25:42 AM
15508 August 2003 8/1/2003 11:09:48 AM
15508 ** September 2003 9/29/2003 7:28:49 AM
15508 ** October 2003 11/6/2003 11:41:07 AM
15530 ** August 2003 8/26/2003 9:41:37 AM
15530 August 2003 8/26/2003 9:41:32 AM
15530 August 2003 8/1/2003 11:17:30 AM
15530 ** September 2003 9/29/2003 2:07:00 PM
15530 ** October 2003 11/7/2003 10:55:10 AM
26260 ** April 2003 9/29/2003 1:12:01 PM
26260 ** May 2003 9/29/2003 1:12:01 PM
26260 ** September 2003 9/29/2003 1:08:27 PM
26260 September 2003 9/2/2003 10:36:04 AM
26260 ** October 2003 11/3/2003 8:55:54 AM

Any suggestions are greatly appreciated.
Thanks.

 

[Tom Ellison]

Dear Coder:

It is a mystery to me why there should be two separate columns of data
for Create_Date and Create_Time. The date and time it was created is
all one "idea" and there is a datetime datatype just to do this.

I suggest you create a query that combines your separate date and time
values into one column. I cannot tell you exactly how to do this as I
do not know how your date and time values are stored. If they are
just text, then you may be able to use:

CDate(Create_Date & " " & Create_Time)

If so, then please create a saved query like this:

SELECT Agent_ID, Month_Reviewed,
CDate(Create_Date & " " & Create_Time) AS CreateDateTime
FROM YourTable

I'll call this YourQuery and use it below. You MUST get this working
before attempting the rest.

SELECT Agent_ID, Month_Reviewed, CreateDateTime
FROM YourQuery Q
WHERE CreateDateTime = (SELECT MAX(CreateDateTime) FROM YourQuery Q1
WHERE Q1.Agent_ID = Q.Agent_ID
AND Q1.Month_Reviewed = Q.MonthReviewed)

This could be done from the separate date and time, but it is a bit of
a mess. There would be separate criteria for Create_Date and
Create_Time, and the subquery for the Create_Time would have to find
the Create_Date maximum as yet a third subquery within the Create_Time
criteria in order to find the MAX() time within a certain date, the
MAX() date, ignoring the time of day for all other dates.

I'd prefer to keep this messy way of doing it in reserve.

Not knowing how you have stored Create_Date and Create_Time could be a
problem, as the ability to perform MAX() on it depends on the ability
to sort them.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Coder 23]

Thanks Tom : ) this really helped.

-----Original Message-----
Dear Coder:

It is a mystery to me why there should be two separate columns of data
for Create_Date and Create_Time. The date and time it was created is
all one "idea" and there is a datetime datatype just to do this.

I suggest you create a query that combines your separate date and time
values into one column. I cannot tell you exactly how to do this as I
do not know how your date and time values are stored. If they are
just text, then you may be able to use:

CDate(Create_Date & " " & Create_Time)

If so, then please create a saved query like this:

SELECT Agent_ID, Month_Reviewed,
CDate(Create_Date & " " & Create_Time) AS CreateDateTime
FROM YourTable

I'll call this YourQuery and use it below. You MUST get this working
before attempting the rest.

SELECT Agent_ID, Month_Reviewed, CreateDateTime
FROM YourQuery Q
WHERE CreateDateTime = (SELECT MAX(CreateDateTime) FROM YourQuery Q1
WHERE Q1.Agent_ID = Q.Agent_ID
AND Q1.Month_Reviewed = Q.MonthReviewed)

This could be done from the separate date and time, but it is a bit of
a mess. There would be separate criteria for Create_Date and
Create_Time, and the subquery for the Create_Time would have to find
the Create_Date maximum as yet a third subquery within the Create_Time
criteria in order to find the MAX() time within a certain date, the
MAX() date, ignoring the time of day for all other dates.

I'd prefer to keep this messy way of doing it in reserve.

Not knowing how you have stored Create_Date and Create_Time could be a
problem, as the ability to perform MAX() on it depends on the ability
to sort them.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.