Counting Orders in Sub-Select Query?


D

Dan

Hi,

I am summarizing data like this

SELECT dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId,
Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00') AS
[Year-Month], dbo_Order_Line_Invoice.VendId,
Sum(dbo_Order_Line_Invoice.Sales) AS Sales_Total, Sum([Sales]-[Cost]) AS
GM_Total, Count(dbo_Order_Line_Invoice.LineNum) AS Line_Count
FROM dbo_Order_Line_Invoice
WHERE (((dbo_Order_Line_Invoice.InvoiceDate)>=#8/1/2004# And
(dbo_Order_Line_Invoice.InvoiceDate)<=#7/31/2005#))
GROUP BY dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustId,
Year([Invoicedate]) & '/' & Format(Month([Invoicedate]),'00'),
dbo_Order_Line_Invoice.VendId;

But I also want to count the number of orders. I am thinking I could
use a sub query to select just the orders for the above realtions, group
on ordernumber then Count(*) but I don't know how to actually do this.
Can someone help me?

TIA,
Dan
 
Ad

Advertisements

T

tina

well, i couldn't figure out how to do it with a subquery. i also couldn't
tell which field in your SQL contains the values the identify specific
orders. so i arbitrarily decided to use the Cono field, and created the
following solution with two queries, as

qryCountOrders
SELECT dbo_Order_Line_Invoice.Cono, Count(dbo_Order_Line_Invoice.ID) AS
OrderCount
FROM dbo_Order_Line_Invoice
WHERE (((dbo_Order_Line_Invoice.InvoiceDate) Between #8/1/2004# And
#7/31/2005#))
GROUP BY dbo_Order_Line_Invoice.Cono;

and a modification of your original SQL, as

SELECT dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustID,
Format([InvoiceDate],"yyyy/mm") AS [Year-Month],
dbo_Order_Line_Invoice.VendID, Sum(dbo_Order_Line_Invoice.Sales) AS
Sales_Total, Sum([Sales]-[Cost]) AS GM_Total,
Count(dbo_Order_Line_Invoice.LineNum) AS Line_Count,
qryCountOrders.OrderCount
FROM dbo_Order_Line_Invoice LEFT JOIN qryCountOrders ON
dbo_Order_Line_Invoice.Cono = qryCountOrders.Cono
WHERE (((dbo_Order_Line_Invoice.InvoiceDate) Between #8/1/2004# And
#7/31/2005#))
GROUP BY dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustID,
Format([InvoiceDate],"yyyy/mm"), dbo_Order_Line_Invoice.VendID,
qryCountOrders.OrderCount;

note the minor simplifications of the criteria on the InvoiceDate field, and
on the formatting of the InvoiceDate field.

hth
 
Ad

Advertisements

T

tina

well, i couldn't figure out how to do it with a subquery. i also couldn't
tell which field in your SQL contains the values that identify specific
orders. so i arbitrarily decided to use the Cono field, and created the
following solution with two queries, as

qryCountOrders
SELECT dbo_Order_Line_Invoice.Cono, Count(dbo_Order_Line_Invoice.ID) AS
OrderCount
FROM dbo_Order_Line_Invoice
WHERE (((dbo_Order_Line_Invoice.InvoiceDate) Between #8/1/2004# And
#7/31/2005#))
GROUP BY dbo_Order_Line_Invoice.Cono;

and a modification of your original SQL, as

SELECT dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustID,
Format([InvoiceDate],"yyyy/mm") AS [Year-Month],
dbo_Order_Line_Invoice.VendID, Sum(dbo_Order_Line_Invoice.Sales) AS
Sales_Total, Sum([Sales]-[Cost]) AS GM_Total,
Count(dbo_Order_Line_Invoice.LineNum) AS Line_Count,
qryCountOrders.OrderCount
FROM dbo_Order_Line_Invoice LEFT JOIN qryCountOrders ON
dbo_Order_Line_Invoice.Cono = qryCountOrders.Cono
WHERE (((dbo_Order_Line_Invoice.InvoiceDate) Between #8/1/2004# And
#7/31/2005#))
GROUP BY dbo_Order_Line_Invoice.Cono, dbo_Order_Line_Invoice.CustID,
Format([InvoiceDate],"yyyy/mm"), dbo_Order_Line_Invoice.VendID,
qryCountOrders.OrderCount;

note the minor simplifications of the criteria on the InvoiceDate field, and
on the formatting of the InvoiceDate field.

hth
 

Top