Total Query

[Abe Katz]

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

 

[Douglas J. Steele]

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)

 

[Abe Katz]

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]

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

(no e-mails, please!)

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

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)