Totals Query


G

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?
 
Ad

Advertisements

M

MGFoster

spalj said:
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....
 
G

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;
 
Ad

Advertisements

M

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;

:

 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Totals Query 1
totals query 1
Totals Query 10
Totals Query 2
Query Totals 3
Query Totals 1
Query for totals 9
Totals Query 2

Top