Using 3 queries in one report

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

KARL DEWEY said:
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


Lissa said:
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?
Click to expand...
Click to expand...
 
Post the SQL statement of your query.
--
KARL DEWEY
Build a little - Test a little


Lissa said:
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?

KARL DEWEY said:
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


Lissa said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top