Showing ZERO in report footer (Access 2k SP1)

Topic

New Reply

I have a report with three reports embedded.

It shows a list of names and their totals for three
categories: Mandated / Circled / Closed

There are 8 names, but for the mandated… only 6 are reporting data.

For the Circled and Closed.. there are 7 and 8 respectively.

So as the report shows, the embedded report1 shows the names of only 6 people. The second embeded shows 7 and the third 8.

How can I report (0) Zero totals so that the report will show all 8 names across the the page?

Thanks

Reply | Quote

Replies

Are the offending zero values showing up in the query the report is based on?

Reply | Quote

No. That’s the problem. My query is based on the Status. If there is a record for status=Mandated for all 8 people, then all the names will show up. I guess I can have a query update the main table to put one record for each person with a status. The reason for these three embedded is that owner of the report wants the totals to stretch across the page. I may just have to offer him a totals report that is vertical like in the attached:

Here is my goal
A B C D
NAme01 ———————– 0——-4——-5———6
NAme02 ———————– 0——-3——-7———3
NAme03 ———————– 0——-6——-8———0

Instead here is what I get:

A B C D
NAme01 ———————– 0——-4——-5———6
4——-5———6
8———0

Where: Because there is only one record for status A, the name for that person is showing. For Status B, there are only 2 records. etc.

If this is too confusing, open up the first Attachment above. then open up this attachment and compare.

Reply | Quote

What is the source of these reports? Table or query, also what is the criteria for selecting Mandated, Circled and Closed. Can you post the query(s) in use.
This could be straight forward if the first PDF is what you are after (but with everything aligned with totals).
Pat

Reply | Quote

The main table has deals that have a status of Mandated Circled and closed.
I have three queries that pick out each of the status.
I have three separate reports that report on the 3 status
I have a MAIN report that has these three embedded in them.

The problem is that of the 8 people…. only 6 have Mandated… 7 have Circled… But all 8 have Closed

The first report has two fields: the Salesperson’s Name and their totals for 2002-2003
The Second report just has the totals for 2002-2003 because it I put the name in, it will be redundant
The Third report just has totals for 2002-2003 sam as report two.

I guess that I need to do is:

For the Mandated report, I need to report a ZERO total for the two salesreps that are missing… so their names will show up, in Alphabetical.

For the Circled report, I need to report a ZERO total for the salesrep that are missing… so their TOTALS will line up with the names in the first report.

For the Circled report, since all the reps have TOTALS, then their numbers should line up with the names on the left.

Hope this explains it.

Again, the focus of the queries is the Status field.

Reply | Quote

You could put this all on a Main report with the following type of query:

SELECT Name, iif(Status=”A”, Amt2002,0) as MandatedAmt2002, iif(Status=”A”, Amt2003,0) as MandatedAmt2003, iif(Status=”B”, Amt2002,0) as CircledAmt2002, iif(Status=”B”, Amt2003,0) as CircledAmt2003, iif(Status=”C”, Amt2002,0) as ClosedAmt2002, iif(Status=”C”, Amt2003,0) as ClosedAmt2003
FROM Tablename
…..ETC…..

It would make it a lot easier to answer if you would just post your queries and let people see them so they can answer the question for you.
HTH
Pat

Reply | Quote

I am wondering how you are going with this problem.
Pat

Reply | Quote

Since I was pressed for time. I simply created the report in column.
I have 4 reports all inmeded in one master report. The are stacked up vertcally.
So it lookes like this:

Mandated:
Name01—————–##——##
Name02—————–##——-##
Name03—————–##——##
Total———————-##——-##

CIRCLED:
Name01—————–##——##
Name02—————–##——-##
Name03—————–##——##
Name04—————–##——-##
Name05—————–##——##
Total———————-##——-##

CIRCLED:
Name01—————–##——##
Name02—————–##——-##
Name03—————–##——##
Name04—————–##——-##
Name05—————–##——##
Name06—————–##——-##
Name07—————–##——##
Total———————-##——-##

Grand Total————-##——-##

Reply | Quote