Help with nulls, Nz, etc

G

Guest

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

Guest

Hi there

try using left outer join. So the query would look like:

SELECT qryWeeks.Year, qryWeeks.Week, qryQty.[Type], nz([Qty],0) AS Qtty
FROM qryWeeks LEFT OUTER 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;

I have not test this query, but from your desc, it seemed left outer join
should work.

Thank you
Lucas

br549 said:
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;
Click to expand...
 
G

Guest

Thanks for the suggestion, but I get the same result when I change to LEFT
OUTER JOIN as I did before. Still does not include a line for weeks in which
a product type had no transactions.
 
G

Guest

Create a new table (tbl_Product_Types) with one field (Product_Type) and two
records "Type1" and "Type2"

Then, in qryWeeks, add this table to the query grid but don't join it to
tbl_Transactions. Then drag the Product_Type field to the grid. I assume
that qryWeeks does some sort of grouping, so adding this table will give you
a record for each year, week, and product_type.

Then, when you join qryWeeks to qryQty, use a left join on the year, week,
and product_type columns (select all from qryWeeks and those that match in
qryQty).

BTW, I would change the column names in your query from Year and Week to
Trans_Year, and Trans_Week. Year is a reserved word in Access and can cause
problems if you use it as a field name.

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.


br549 said:
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;
Click to expand...
 
G

Guest

Thank you very much; with this advice I was able to get the result I wanted.

Dale Fye said:
Create a new table (tbl_Product_Types) with one field (Product_Type) and two
records "Type1" and "Type2"

Then, in qryWeeks, add this table to the query grid but don't join it to
tbl_Transactions. Then drag the Product_Type field to the grid. I assume
that qryWeeks does some sort of grouping, so adding this table will give you
a record for each year, week, and product_type.

Then, when you join qryWeeks to qryQty, use a left join on the year, week,
and product_type columns (select all from qryWeeks and those that match in
qryQty).

BTW, I would change the column names in your query from Year and Week to
Trans_Year, and Trans_Week. Year is a reserved word in Access and can cause
problems if you use it as a field name.

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.


br549 said:
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;
Click to expand...
Click to expand...
 
D

Dale Fye

Glad I could help.

br549 said:
Thank you very much; with this advice I was able to get the result I
wanted.

Dale Fye said:
Create a new table (tbl_Product_Types) with one field (Product_Type) and
two
records "Type1" and "Type2"

Then, in qryWeeks, add this table to the query grid but don't join it to
tbl_Transactions. Then drag the Product_Type field to the grid. I
assume
that qryWeeks does some sort of grouping, so adding this table will give
you
a record for each year, week, and product_type.

Then, when you join qryWeeks to qryQty, use a left join on the year,
week,
and product_type columns (select all from qryWeeks and those that match
in
qryQty).

BTW, I would change the column names in your query from Year and Week to
Trans_Year, and Trans_Week. Year is a reserved word in Access and can
cause
problems if you use it as a field name.

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.


br549 said:
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;
Click to expand...
Click to expand...
Click to expand...
 

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

Crosstab query help please 1
Help needed with query for weekly totals 4
novice needs help with query 4
Help with Crosstab Query to transpose data 8
Help with Crosstab Query 1
i need help with a lookup and/or array type formula 1
Help with update query UDF 1
Query: Advanced Help Needed to "repeat" rows / split months 2

Top