Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Showing ZERO in report footer (Access 2k SP1)

    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
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showing ZERO in report footer (Access 2k SP1)

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

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showing ZERO in report footer (Access 2k SP1)

    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.
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Showing ZERO in report footer (Access 2k SP1)

    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

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showing ZERO in report footer (Access 2k SP1)

    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.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Showing ZERO in report footer (Access 2k SP1)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Showing ZERO in report footer (Access 2k SP1)

    I am wondering how you are going with this problem.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showing ZERO in report footer (Access 2k SP1)

    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-------------##-------##

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •