Report/Query Help with logic...

[Guest]

Hi all -- I have a very simple table and need help setting up a Report/Query
(not sure which to use first)...

Table: ULV_ZONE
ZONE DATE CHEMICAL METHOD
V-1 10/4/2004 DIBROM AERIAL
V-1 10/1/2005 DIBROM AERIAL
V-1 10/3/2006 DIBROM AERIAL
V-2 10/6/2005 DIBROM AERIAL
V-2 10/9/2006 DIBROM AERIAL
V-3 10/9/2005 DIBROM AERIAL
V-3 10/9/2006 DIBROM AERIAL


The query/question/report is to list the *last time* each zone was run. So,
in the ex data above, how do I list out V-1 thru V-10, and determine the last
date (most recent) it was worked on, not including all data --- just the
last/most recent occurence.

Thanks for any comments/suggestions!

j

 

[Guest]

In the WHERE clause you'd use a subquery which returns the latest date for
the outer query's zone:

SELECT Zone, [Date]
FROM ULV_ZONE AS UZ1
WHERE [Date] =
(SELECT MAX([Date])
FROM ULV_ZONE As UZ2
WHERE UZ2.Zone = UZ1.Zone);

BTW I'd avoid Date as a column name; it could easily be confused with the
built in Date function and give unexpected results in some circumstances.
Use something more explicit like RunDate.

Ken Sheridan
Stafford, England

 

[Guest]

SELECT MAX([Date])

This is what I was missing --- thanks Ken.

j

In the WHERE clause you'd use a subquery which returns the latest date for
the outer query's zone:

SELECT Zone, [Date]
FROM ULV_ZONE AS UZ1
WHERE [Date] =
(SELECT MAX([Date])
FROM ULV_ZONE As UZ2
WHERE UZ2.Zone = UZ1.Zone);

BTW I'd avoid Date as a column name; it could easily be confused with the
built in Date function and give unexpected results in some circumstances.
Use something more explicit like RunDate.

Ken Sheridan
Stafford, England

Hi all -- I have a very simple table and need help setting up a Report/Query
(not sure which to use first)...

Table: ULV_ZONE
ZONE DATE CHEMICAL METHOD
V-1 10/4/2004 DIBROM AERIAL
V-1 10/1/2005 DIBROM AERIAL
V-1 10/3/2006 DIBROM AERIAL
V-2 10/6/2005 DIBROM AERIAL
V-2 10/9/2006 DIBROM AERIAL
V-3 10/9/2005 DIBROM AERIAL
V-3 10/9/2006 DIBROM AERIAL


The query/question/report is to list the *last time* each zone was run. So,
in the ex data above, how do I list out V-1 thru V-10, and determine the last
date (most recent) it was worked on, not including all data --- just the
last/most recent occurence.

Thanks for any comments/suggestions!

j

 

[Guest]

Thanks again for your comments/suggestion....

I am still not getting the desired result.

Why do I have to enter 2 date parameters? It should not ask for these
values, but just list out the LAST date each zone was sprayed.

The SQLStatement should be asking,

List each Zone and the *last* time it was worked on. That is, every unique
zone value should be listed out (once) with a date value (the most recent
date it was worked on).

Here is the query I have in SQLView:

SELECT *
FROM ULV_ZONE AS UZ1
WHERE ULV_ZONE.[TX_DATE] =
(SELECT MAX(ULV_ZONE.[TX_DATE])
FROM ULV_ZONE AS UZ2
WHERE UZ2.ZONE = UZ1.ZONE);

In the WHERE clause you'd use a subquery which returns the latest date for
the outer query's zone:

SELECT Zone, [Date]
FROM ULV_ZONE AS UZ1
WHERE [Date] =
(SELECT MAX([Date])
FROM ULV_ZONE As UZ2
WHERE UZ2.Zone = UZ1.Zone);

BTW I'd avoid Date as a column name; it could easily be confused with the
built in Date function and give unexpected results in some circumstances.
Use something more explicit like RunDate.

Ken Sheridan
Stafford, England

Hi all -- I have a very simple table and need help setting up a Report/Query
(not sure which to use first)...

Table: ULV_ZONE
ZONE DATE CHEMICAL METHOD
V-1 10/4/2004 DIBROM AERIAL
V-1 10/1/2005 DIBROM AERIAL
V-1 10/3/2006 DIBROM AERIAL
V-2 10/6/2005 DIBROM AERIAL
V-2 10/9/2006 DIBROM AERIAL
V-3 10/9/2005 DIBROM AERIAL
V-3 10/9/2006 DIBROM AERIAL


The query/question/report is to list the *last time* each zone was run. So,
in the ex data above, how do I list out V-1 thru V-10, and determine the last
date (most recent) it was worked on, not including all data --- just the
last/most recent occurence.

Thanks for any comments/suggestions!

j