Counting Orders in Sub-Select Query?

[Dan]

Hi,

I am summarizing data like this

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

 

[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 dbrder_Line_Invoice.Cono, Count(dbrder_Line_Invoice.ID) AS
OrderCount
FROM dbrder_Line_Invoice
WHERE (((dbrder_Line_Invoice.InvoiceDate) Between #8/1/2004# And
#7/31/2005#))
GROUP BY dbrder_Line_Invoice.Cono;

and a modification of your original SQL, as

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

 

[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 dbrder_Line_Invoice.Cono, Count(dbrder_Line_Invoice.ID) AS
OrderCount
FROM dbrder_Line_Invoice
WHERE (((dbrder_Line_Invoice.InvoiceDate) Between #8/1/2004# And
#7/31/2005#))
GROUP BY dbrder_Line_Invoice.Cono;

and a modification of your original SQL, as

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