Re: Using Max and Criteria on the same field in Access Query

dsdevonsomer@xxxxxxxxx wrote in
news:1fd2ab04-b1cf-44f9-b570-092aef9bb7d1
@t63g2000hsf.googlegroups.co
m:

On Apr 26, 10:32 pm, dsdevonso...@xxxxxxxxx wrote:
Hello Everyone,
I faced strange situation. To explain, let's take the following
table with 2 rows.

ID | Order # | Order Date | State | Count
1   | 201 | 10/1/2007 | CA | 1
2  | 201 | 11/15/2007 | CA | 1
3   | 201 | 3/1/2008  | CA | 1
4 | 201 | 4/10/2008 | CA | 1

Now, I want to find the max of order date and then total count of
orders. So if I use the following query, it gives me total of 4
for entire life of this order.

SELECT ORDER_DETAIL.[ORDER #], Max(ORDER_DETAIL.[ORDER DATE]) AS
[MaxOfORDER DATE], ORDER_DETAIL.STATE, Sum(ORDER_DETAIL.COUNT) AS
SumOfCOUNT
FROM ORDER_DETAIL
GROUP BY ORDER_DETAIL.[ORDER #], ORDER_DETAIL.STATE;

But, if I want to only find out how many orders were placed in
this year and what's the max date on that order, I tried to use
this query and it gave me still 4 count instead of 2.

SELECT ORDER_DETAIL.[ORDER #], Max(ORDER_DETAIL.[ORDER DATE]) AS
[MaxOfORDER DATE], ORDER_DETAIL.STATE, Sum(ORDER_DETAIL.COUNT) AS
SumOfCOUNT
FROM ORDER_DETAIL
GROUP BY ORDER_DETAIL.[ORDER #], ORDER_DETAIL.STATE
HAVING (((Max(ORDER_DETAIL.[ORDER DATE]))>#1/1/2008#));

I was confused... How would I get 2 order count ?
What's going wrong?
JB

To check something else, I did the following
SELECT ORDER_DETAIL.[ORDER #], Max(ORDER_DETAIL.[ORDER DATE]) AS
[MaxOfORDER DATE], ORDER_DETAIL.STATE, Sum(ORDER_DETAIL.COUNT) AS
SumOfCOUNT
FROM ORDER_DETAIL
WHERE (((ORDER_DETAIL.[ORDER DATE])>#1/1/2008#))
GROUP BY ORDER_DETAIL.[ORDER #], ORDER_DETAIL.STATE
HAVING (((ORDER_DETAIL.STATE)="CA"));

but for this I had to add one more time "Order Date" in columns
and apply my conditions. This is strange. I couldn't use >
1/1/2008 on the same field where I have used MAX. There must be
something inherently different in the way MS Access runs query on
MAX and WHERE. Does anyone know ?
JB.

Access applies the max(), sum(), etc functions only to the column
not to the the entire row. and does this consistently. the Where
clause is applied to the entire row before the functions. the Having
clause applies criteria after the functions.

To get the results you wish, you need to use one query to filter the
records and a second query based on the first to do the totals.

Once this is working as separate queries you can (sometimes) rewrite
the SQL into a single query, using the SQL editor.

Query1=
SELECT ORDER_DETAIL.[ORDER #], ORDER_DETAIL.[ORDER DATE]),
ORDER_DETAIL.STATE, ORDER_DETAIL.COUNT FROM ORDER_DETAIL
WHERE year(ORDER_DETAIL.[ORDER DATE]) = 2008;

Query2
SELECT Query1.[ORDER #], Max(Query1.[ORDER DATE]) AS
[MaxOfORDER DATE], Query1.STATE, Sum(Query1.COUNT) AS
SumOfCOUNT
FROM Query1
GROUP BY Query1.[ORDER #], Query1.STATE;



--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **
.

Relevant Pages

  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: Running balance from two tables
    ... the TOTALS button and show a totals line in the query, ... my total additions and my total costs and see how much money is in my ... What I'd love to do with it is have something pop up when the balance is ...
    (microsoft.public.access.queries)
  • RE: Crosstab Worries
    ... I copied your sql into my Northwind and I created a form off that query and I ... concerned, the recordsource is three letters, Jan, Feb, but the text box is ... You can sum a field from the record source but not ... with their totals in each of the twelve columns. ...
    (microsoft.public.access.queries)
  • RE: Cannot get code to work for API Save Dialog Box
    ... While I still need help on that other issue, I fixed the totals problem I had. ... The first spreadsheet placed where I told it to be contains the incorrect ... Set xlSheet = Nothing ... query, you will have to add the field to your query. ...
    (microsoft.public.access.forms)
  • Is ROLLUP really better than doing a nested query when rank is involved?
    ... I meant here by homing in: within the same query - using ... sales BUT with their subtotals as well... ... the totals and subtotals... ... use min(rank) in the same aggregation, ...
    (comp.databases.oracle.server)