Total Query

Hello All,

I need to know, when was the last time (date) we sold an ItemNo and the
total quantity sold.
I created a Total query and in the Total cell, for the Date I entered Max
and for the Qty I entered Last.
The problem is, The date works fine but the qty doesn't work. The date comes
up the last date sold but the Qty is not the last it's from a different
record.

Please let me know,
Thank you
Abe

That's not the way Totals queries work.

What the query is doing is returning is the Max date value, but essentially
a random value for Qty. That's because you cannot make any assumption about
the order of records in a table.

What you actually want is a subquery, something like:

SELECT CustomerId, ItemNo, OrderDate, OrderQty
FROM OrderTable As A
WHERE OrderDate IN
(SELECT Max(OrderDate)
FROM OrderTable
WHERE CustomerId = A.CustomerId)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Abe Katz" wrote in message
...
Hello All,

I need to know, when was the last time (date) we sold an ItemNo and the
total quantity sold.
I created a Total query and in the Total cell, for the Date I entered Max
and for the Qty I entered Last.
The problem is, The date works fine but the qty doesn't work. The date
comes up the last date sold but the Qty is not the last it's from a
different record.

Please let me know,
Thank you
Abe

Doug,
I need a list from all the Items in the Inventory, the last date each item
was last sold, and the qty sold.
Doug Steele told me to use a subquery, it works fine with one table but when
I have a Header and a Detail table and using the INNER JOIN, the system
doesn't let me add the aliases table name (AS A). Error msg "Syntax error
missing operator in query expression".

This is my code,
SELECT [Invoice Detail].StyleNo, Invoice.Date AS DateLastInv, Sum([Invoice
Detail].Qty) AS QtyInv
FROM Invoice INNER JOIN [Invoice Detail] ON Invoice.InvoiceNo = [Invoice
Detail].InvoiceNo AS A (AS A, error message)
GROUP BY [Invoice Detail].StyleNo, Invoice.Date
HAVING (((Invoice.Date) In (SELECT Max(Date) FROM [Invoice] WHERE [Invoice
Detail].StyleNo=A.StyleNo)));

Please let me know
Thank you
abe

"Douglas J. Steele" wrote in message
...
That's not the way Totals queries work.

What the query is doing is returning is the Max date value, but
essentially a random value for Qty. That's because you cannot make any
assumption about the order of records in a table.

What you actually want is a subquery, something like:

SELECT CustomerId, ItemNo, OrderDate, OrderQty
FROM OrderTable As A
WHERE OrderDate IN
(SELECT Max(OrderDate)
FROM OrderTable
WHERE CustomerId = A.CustomerId)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Abe Katz" wrote in message
...
Hello All,

I need to know, when was the last time (date) we sold an ItemNo and the
total quantity sold.
I created a Total query and in the Total cell, for the Date I entered Max
and for the Qty I entered Last.
The problem is, The date works fine but the qty doesn't work. The date
comes up the last date sold but the Qty is not the last it's from a
different record.

Please let me know,
Thank you
Abe

While it can be done in a single query, the easiest approach is to create a
query that joins the two tables and use that query in the same query as I
suggested earlier.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Abe Katz" wrote in message
...
Doug,
I need a list from all the Items in the Inventory, the last date each item
was last sold, and the qty sold.
Doug Steele told me to use a subquery, it works fine with one table but
when
I have a Header and a Detail table and using the INNER JOIN, the system
doesn't let me add the aliases table name (AS A). Error msg "Syntax error
missing operator in query expression".

This is my code,
SELECT [Invoice Detail].StyleNo, Invoice.Date AS DateLastInv, Sum([Invoice
Detail].Qty) AS QtyInv
FROM Invoice INNER JOIN [Invoice Detail] ON Invoice.InvoiceNo = [Invoice
Detail].InvoiceNo AS A (AS A, error message)
GROUP BY [Invoice Detail].StyleNo, Invoice.Date
HAVING (((Invoice.Date) In (SELECT Max(Date) FROM [Invoice] WHERE [Invoice
Detail].StyleNo=A.StyleNo)));

Please let me know
Thank you
abe

"Douglas J. Steele" wrote in message
...
That's not the way Totals queries work.

What the query is doing is returning is the Max date value, but
essentially a random value for Qty. That's because you cannot make any
assumption about the order of records in a table.

What you actually want is a subquery, something like:

SELECT CustomerId, ItemNo, OrderDate, OrderQty
FROM OrderTable As A
WHERE OrderDate IN
(SELECT Max(OrderDate)
FROM OrderTable
WHERE CustomerId = A.CustomerId)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Abe Katz" wrote in message
...
Hello All,

I need to know, when was the last time (date) we sold an ItemNo and the
total quantity sold.
I created a Total query and in the Total cell, for the Date I entered
Max and for the Qty I entered Last.
The problem is, The date works fine but the qty doesn't work. The date
comes up the last date sold but the Qty is not the last it's from a
different record.

Please let me know,
Thank you
Abe