Totals Query

[Guest]

I have a table that has list of orders, each order is assigned one of four
categories. I want to produce a query that will group these orders by week,
and by order category, and provide a total count for each category. I have
been able to do so with one problem. If there happens to be no orders for a
particular category one week I still want a record shown in the query with a
count of zero. The query I made is a select query grouped by week and
category, with a field that counts order numbers to give me a total for each
week by category. Any ideas?

 

[MGFoster]

I have a table that has list of orders, each order is assigned one of four
categories. I want to produce a query that will group these orders by week,
and by order category, and provide a total count for each category. I have
been able to do so with one problem. If there happens to be no orders for a
particular category one week I still want a record shown in the query with a
count of zero. The query I made is a select query grouped by week and
category, with a field that counts order numbers to give me a total for each
week by category. Any ideas?

Probably a LEFT JOIN is needed instead of an INNER JOIN, but, we can't
know that until you post the SQL. Hint, hint....

 

[Guest]

I thinking in that line but was unsucessful trying.

Here's the SQL

SELECT [qry3-01PerformanceMatrix].Week, OrderTypes.OrderType,
Count([qry3-01PerformanceMatrix].ShipmentID) AS [#ofLoads]
FROM OrderTypes LEFT JOIN [qry3-01PerformanceMatrix] ON OrderTypes.OrderType
= [qry3-01PerformanceMatrix].[Load Type]
GROUP BY [qry3-01PerformanceMatrix].Week, OrderTypes.OrderType
ORDER BY [qry3-01PerformanceMatrix].Week DESC , OrderTypes.OrderType;

 

[MGFoster]

Does the query "qry3-01PerformanceMatrix" return all Weeks? If not,
that is the query that should be fixed, not the one, below.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

I thinking in that line but was unsucessful trying.

Here's the SQL

SELECT [qry3-01PerformanceMatrix].Week, OrderTypes.OrderType,
Count([qry3-01PerformanceMatrix].ShipmentID) AS [#ofLoads]
FROM OrderTypes LEFT JOIN [qry3-01PerformanceMatrix] ON OrderTypes.OrderType
= [qry3-01PerformanceMatrix].[Load Type]
GROUP BY [qry3-01PerformanceMatrix].Week, OrderTypes.OrderType
ORDER BY [qry3-01PerformanceMatrix].Week DESC , OrderTypes.OrderType;

: