Totals Query

[Jeremy Schwab]

I'm trying to create a Totals Query for sales data to only show the first
invoice date for each part number and all the summarized data within that
group (basically to show how many total we shipped on the first order and
what the total dollar value was).

For example, I have the following data (these are not the actual field names)
:

Code:
--------------------------------------------------------------------------------

PartNum Invoice Qty Price Cust#
PK1009 1/13/2005 8 218.4 15776
PK1009 1/13/2005 12 327.6 15776
PK1009 1/13/2005 32 873.6 15776
PK1009 1/20/2005 16 448.81 15776
PK1016 6/29/2005 4 122.8 15776
PK1016 6/29/2005 8 245.6 15776
PK1016 6/30/2005 8 245.08 15776
PK1016 6/30/2005 20 612.7 15776
PK1016 7/8/2005 4 122.8 15776
--------------------------------------------------------------------------------


I would like to create a query that summarizes the Qty and Price fields for
the first date in the Invoice column. The output should be:
Code:
--------------------------------------------------------------------------------

PartNum Invoice Qty Price Cust#
PK1009 1/13/2005 52 1419.6 15776
PK1016 6/29/2005 12 368.4 15776
--------------------------------------------------------------------------------


Does anyone know how I might go about doing this? If I go into query design
(QBE grid) and Group By PartNum, and use something like Min(Invoice),
Sum(Qty), Sum(Price), I get the total sum for all the records not just that
date for that part number. I'd really like to group by PartNum and the First
Invoice Date and summarize for that.

I've also tried to do this with a subquery before as such:

Create a subquery that returns the minimum date (ie. output would be PK1009
1/13/2005). Join that query back to the table to summarize the values that
relate to those two value. However, I really don't think this is the way to
do it and I've had problems doing it this way in the past.

Any help is much appreciated!

 

[Marshall Barton]

I'm trying to create a Totals Query for sales data to only show the first
invoice date for each part number and all the summarized data within that
group (basically to show how many total we shipped on the first order and
what the total dollar value was).

For example, I have the following data (these are not the actual field names)
:

Code:
--------------------------------------------------------------------------------

PartNum Invoice Qty Price Cust#
PK1009 1/13/2005 8 218.4 15776
PK1009 1/13/2005 12 327.6 15776
PK1009 1/13/2005 32 873.6 15776
PK1009 1/20/2005 16 448.81 15776
PK1016 6/29/2005 4 122.8 15776
PK1016 6/29/2005 8 245.6 15776
PK1016 6/30/2005 8 245.08 15776
PK1016 6/30/2005 20 612.7 15776
PK1016 7/8/2005 4 122.8 15776
--------------------------------------------------------------------------------


I would like to create a query that summarizes the Qty and Price fields for
the first date in the Invoice column. The output should be:
Code:
--------------------------------------------------------------------------------

PartNum Invoice Qty Price Cust#
PK1009 1/13/2005 52 1419.6 15776
PK1016 6/29/2005 12 368.4 15776
--------------------------------------------------------------------------------


Does anyone know how I might go about doing this? If I go into query design
(QBE grid) and Group By PartNum, and use something like Min(Invoice),
Sum(Qty), Sum(Price), I get the total sum for all the records not just that
date for that part number. I'd really like to group by PartNum and the First
Invoice Date and summarize for that.

How about using GroupBy on both the part num and invoice
fields. Then use the criteria for the date field something
like:
(SELECT Min([date]) FROM sales As X WHERE X.PartNum =
sales.PartNum )

 

[Guest]

Two queries --
FirstInvoiceForPartNum --
SELECT Min(InvoiceData.Invoice) AS MinOfInvoice, InvoiceData.PartNum
FROM InvoiceData
GROUP BY InvoiceData.PartNum;

SELECT InvoiceData.[Cust#], InvoiceData.PartNum, InvoiceData.Invoice,
Sum(InvoiceData.Qty) AS SumOfQty, Sum(InvoiceData.Price) AS SumOfPrice
FROM InvoiceData INNER JOIN FirstInvoiceForPartNum ON (InvoiceData.Invoice =
FirstInvoiceForPartNum.MinOfInvoice) AND (InvoiceData.PartNum =
FirstInvoiceForPartNum.PartNum)
GROUP BY InvoiceData.[Cust#], InvoiceData.PartNum, InvoiceData.Invoice;