Showing a zero on a report when no underlying query records found

[dburns]

I have a query underlying a report which finds all (book) records for
items that either have been received this fiscal year or are still on
order. The report totals are grouped by department. Right now it
returns results something like this:

ART: On Order: $100
ART: Received: $300
BIO: Received: $400
CHE: On Order: $200
CHE: Received: $150

The problem here is for the department "BIO." Nothing is currently on
order, so the query does not return any records meeting that
criterion. I would like it to print out something like this:

BIO: On Order: $0
BIO: Received: $400

The same goes for departments which have not received any books yet,
or have not ordered OR received any books yet. Can I alter the query
or report to indicate that no records have been returned for a
particular department? I would like to avoid using blank records.

Thanks.

David Burns
Spring Arbor, MI

 

[Marshall Barton]

I have a query underlying a report which finds all (book) records for
items that either have been received this fiscal year or are still on
order. The report totals are grouped by department. Right now it
returns results something like this:

ART: On Order: $100
ART: Received: $300
BIO: Received: $400
CHE: On Order: $200
CHE: Received: $150

The problem here is for the department "BIO." Nothing is currently on
order, so the query does not return any records meeting that
criterion. I would like it to print out something like this:

BIO: On Order: $0
BIO: Received: $400

The same goes for departments which have not received any books yet,
or have not ordered OR received any books yet. Can I alter the query
or report to indicate that no records have been returned for a
particular department? I would like to avoid using blank records.

Deal with this kind of thing in the report's record source
query. Start with a table that has a record for every
possible department and transaction type. You probably
don't have such a table, so use a query as a virtual table:
SELECT Dept, TransType
FROM Depts, TransTypes

If you don't have a transaction types table create it. At
this point it will probably only have two records:
On Order
Received

Now, add the above query to the report's record source query
using a LEFT JOIN. Something along these lines:

SELECT DeptTransTypes.Dept,
DeptTransTypes.TransType,
Sum(Amount) As Total
FROM DeptTransTypes LEFT JOIN Book
ON Book.Dept = DeptTransTypes.Dept
And Book.trans = DeptTransTypes.TransType
WHERE Book.transdate
Between DateSerial(Year(Date()),1,1) And Date()

Try the query before using it in the report to check that
it's doing what you want.

 

[dburns]

Thanks for your help. I'm going to work on this today.