B
br549
I'm getting tripped up by some missing data and desperately need help. I
start with one data table, tblTransactions, that has fields
[TransactionDateTime] and [ProductType]. Each record represents a transaction
for one of two product types, Type1 and Type2. It looks something like:
TransactionDateTime ProductType
------------------------- ----------------
2006-12-30 12:24:21 Type1
2007-01-01 14:23:57 Type2
2007-02-01 08:02:27 Type1
etc etc
I run a query: qryWeeks, that results in two fields, [Year], and [Week].
This produces a list of every week for which there was a transaction:
Year Week
----- ------
2006 50
2006 51
2007 01
etc etc
Another query run on tblTransactions, qryQty, results in fields [Year],
[Week], [ProductType], [Qty]. Qty is a count of transactions by week and by
product type:
Year Week ProductType Qty
----- ------ --------------- -----
2006 50 Type1 301
2006 50 Type2 257
2007 01 Type1 152
2007 02 Type1 53
2004 02 Type2 378
etc etc etc etc
Here's where my problem starts. This query (qryQty) only produces a line for
a type when there was data of that type in the week. In the example above,
there were no transactions for product Type2 in week 01, so there is a
missing line.
What I want is a line for each of the two product types for each week (that
is, each week in qryWeeks), if there was data for the product type or not.
If there was no data for a type, then I want [Qty] to show 0. It would look
instead something like:
Year Week ProductType Qty
----- ------ --------------- -----
2006 50 Type1 301
2006 50 Type2 257
2007 01 Type1 152
2007 01 Type2 0
2007 02 Type1 53
2004 02 Type2 378
etc etc etc etc
I tried a query that pulls Year and Week from the complete list qryWeeks and
added Type and Qty from qryQty.
I assumed that a Nz function would take care of this, but it does not. I
have tried every combination of Nz, iif(....), and every way of setting up
the queries that I can think of, but can't get the result I want. It seems
like a simple problem, but I can't figure it out. I'm desperate after 2 days
of trying different things and banging my head against the wall over it.
Here's the SQL that I thought might work, but doesn't:
SELECT qryWeeks.Year, qryWeeks.Week, qryQty.[Type], nz([Qty],0) AS Qtty
FROM qryWeeks INNER JOIN qryQty ON (qryWeeks.Week = qryQty.Week) AND
(qryWeeks.Year = qryQty.Year)
GROUP BY qryWeeks.Year, qryWeeks.Week, qryQty.[Type], nz([Qty],0)
WITH OWNERACCESS OPTION;
start with one data table, tblTransactions, that has fields
[TransactionDateTime] and [ProductType]. Each record represents a transaction
for one of two product types, Type1 and Type2. It looks something like:
TransactionDateTime ProductType
------------------------- ----------------
2006-12-30 12:24:21 Type1
2007-01-01 14:23:57 Type2
2007-02-01 08:02:27 Type1
etc etc
I run a query: qryWeeks, that results in two fields, [Year], and [Week].
This produces a list of every week for which there was a transaction:
Year Week
----- ------
2006 50
2006 51
2007 01
etc etc
Another query run on tblTransactions, qryQty, results in fields [Year],
[Week], [ProductType], [Qty]. Qty is a count of transactions by week and by
product type:
Year Week ProductType Qty
----- ------ --------------- -----
2006 50 Type1 301
2006 50 Type2 257
2007 01 Type1 152
2007 02 Type1 53
2004 02 Type2 378
etc etc etc etc
Here's where my problem starts. This query (qryQty) only produces a line for
a type when there was data of that type in the week. In the example above,
there were no transactions for product Type2 in week 01, so there is a
missing line.
What I want is a line for each of the two product types for each week (that
is, each week in qryWeeks), if there was data for the product type or not.
If there was no data for a type, then I want [Qty] to show 0. It would look
instead something like:
Year Week ProductType Qty
----- ------ --------------- -----
2006 50 Type1 301
2006 50 Type2 257
2007 01 Type1 152
2007 01 Type2 0
2007 02 Type1 53
2004 02 Type2 378
etc etc etc etc
I tried a query that pulls Year and Week from the complete list qryWeeks and
added Type and Qty from qryQty.
I assumed that a Nz function would take care of this, but it does not. I
have tried every combination of Nz, iif(....), and every way of setting up
the queries that I can think of, but can't get the result I want. It seems
like a simple problem, but I can't figure it out. I'm desperate after 2 days
of trying different things and banging my head against the wall over it.
Here's the SQL that I thought might work, but doesn't:
SELECT qryWeeks.Year, qryWeeks.Week, qryQty.[Type], nz([Qty],0) AS Qtty
FROM qryWeeks INNER JOIN qryQty ON (qryWeeks.Week = qryQty.Week) AND
(qryWeeks.Year = qryQty.Year)
GROUP BY qryWeeks.Year, qryWeeks.Week, qryQty.[Type], nz([Qty],0)
WITH OWNERACCESS OPTION;