Using 3 queries in one report

[Guest]

Access 2007

I am trying to create a monthly inventory report that lists the Product
Name, Beginning Balance, Sales, Purchases and Ending Balance, which is a
calculated field, in rows sorted by product name.

My problem is getting all the information from the Beginning Balance query,
Sales query and Purchases query to list all the information from all 3
queries even if there is no data for one of them. I can make this work with
only 2 queries, but I can't seem to make it work for 3 queries.

Everything I try links all the fields so I only get the records that show
data that is in each query, with the exception of the Beginning Balance,
which always seems to work.

This is how the report looks right now:
Prod A 23 BegBal 3 Sls 15 Purch 35 EndBal
Prod C 10 BegBal 15 Sls 33 Purch 28 EndBal
Prod E 0 BegBal 1 Sls 6 Purch 5 EndBal

This is how I want the report to look:
Prod A 23 BegBal 3 Sls 15 Purch 35 EndBal
Prod B 12 BegBal 6 Sls 0 Purch 6 EndBal
Prod C 10 BegBal 15 Sls 33 Purch 28 EndBal
Prod D 19 BegBal 0 Sls 5 Purch 24 EndBal
Prod E 0 BegBal 1 Sls 6 Purch 5 EndBal

I've tried using all different types of UNIONs and Nz in the fields but
nothing works.

Can anyone help?

 

[Guest]

A Null summed with a value is Null. Use the NZ function in summing.
NZ([YourField], 0) will result in zero if that record is null in the
query. Therefore zero plus some value equals some value.

 

[Guest]

Hi Karl...

I am using Nz in the Sales & Purchases fields and it is still not bringing
up the null fields as zero. Would this work better with subqueries? If so,
any ideas on how the syntax?

A Null summed with a value is Null. Use the NZ function in summing.
NZ([YourField], 0) will result in zero if that record is null in the
query. Therefore zero plus some value equals some value.
--
KARL DEWEY
Build a little - Test a little

Access 2007

I am trying to create a monthly inventory report that lists the Product
Name, Beginning Balance, Sales, Purchases and Ending Balance, which is a
calculated field, in rows sorted by product name.

My problem is getting all the information from the Beginning Balance query,
Sales query and Purchases query to list all the information from all 3
queries even if there is no data for one of them. I can make this work with
only 2 queries, but I can't seem to make it work for 3 queries.

Everything I try links all the fields so I only get the records that show
data that is in each query, with the exception of the Beginning Balance,
which always seems to work.

This is how the report looks right now:
Prod A 23 BegBal 3 Sls 15 Purch 35 EndBal
Prod C 10 BegBal 15 Sls 33 Purch 28 EndBal
Prod E 0 BegBal 1 Sls 6 Purch 5 EndBal

This is how I want the report to look:
Prod A 23 BegBal 3 Sls 15 Purch 35 EndBal
Prod B 12 BegBal 6 Sls 0 Purch 6 EndBal
Prod C 10 BegBal 15 Sls 33 Purch 28 EndBal
Prod D 19 BegBal 0 Sls 5 Purch 24 EndBal
Prod E 0 BegBal 1 Sls 6 Purch 5 EndBal

I've tried using all different types of UNIONs and Nz in the fields but
nothing works.

Can anyone help?

 

[Guest]

Post the SQL statement of your query.
--
KARL DEWEY
Build a little - Test a little

Hi Karl...

I am using Nz in the Sales & Purchases fields and it is still not bringing
up the null fields as zero. Would this work better with subqueries? If so,
any ideas on how the syntax?

A Null summed with a value is Null. Use the NZ function in summing.
NZ([YourField], 0) will result in zero if that record is null in the
query. Therefore zero plus some value equals some value.
--
KARL DEWEY
Build a little - Test a little

Access 2007

I am trying to create a monthly inventory report that lists the Product
Name, Beginning Balance, Sales, Purchases and Ending Balance, which is a
calculated field, in rows sorted by product name.

My problem is getting all the information from the Beginning Balance query,
Sales query and Purchases query to list all the information from all 3
queries even if there is no data for one of them. I can make this work with
only 2 queries, but I can't seem to make it work for 3 queries.

Everything I try links all the fields so I only get the records that show
data that is in each query, with the exception of the Beginning Balance,
which always seems to work.

This is how the report looks right now:
Prod A 23 BegBal 3 Sls 15 Purch 35 EndBal
Prod C 10 BegBal 15 Sls 33 Purch 28 EndBal
Prod E 0 BegBal 1 Sls 6 Purch 5 EndBal

This is how I want the report to look:
Prod A 23 BegBal 3 Sls 15 Purch 35 EndBal
Prod B 12 BegBal 6 Sls 0 Purch 6 EndBal
Prod C 10 BegBal 15 Sls 33 Purch 28 EndBal
Prod D 19 BegBal 0 Sls 5 Purch 24 EndBal
Prod E 0 BegBal 1 Sls 6 Purch 5 EndBal

I've tried using all different types of UNIONs and Nz in the fields but
nothing works.

Can anyone help?