-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Right, so what's the SQL look like for that query?
Problem is probably how you get the Week number. Are you using a
calendar table w/ week numbers, or is the Week number generated from a
Date column? If from a date column, you'll have to find some way to
ensure that all dates you want in the result set are available to the
query. Most people use a calendar table that has all the dates they
will be using in the query. I've used temp. tables that hold all the
dates, numbers, whatever, that I want to always show in the query.
It could also be just a LEFT vs INNER JOIN problem that can be resolved
to get the Load Type to show in the SELECT clause.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQwY0Q4echKqOuFEgEQLQRQCg7CEdrXvvaEcx7VtMwLz2lSZVzVEAn1FJ
MEyiqZfN8rg8BibZNyRI6zSy
=bKGY
-----END PGP SIGNATURE-----
The qry returns all weeks that have orders. Below is a portion of what it I
get from the qry I'm having problems with.
Week Load Type #ofLoads
31 DC - DC 7
31 DC - Store 21
31 Supplier - DC 7
31 Supplier - Store 2
30 DC - DC 6
30 DC - Store 20
30 Supplier - DC 12
29 DC - DC 7
29 DC - Store 21
29 Supplier - DC 7
The problem I'm experiencing for example happens in week 29. There are no
"Supplier - Store" loads that week but I need it to show "Supplier - Store
0".
:
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;
:
spalj wrote:
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?
