Adding a second Category to a Report 1

[rcoutts]

I created a report that groups the three column of a Table under one Category and one Detail, e.g.,

   Balance
               Previous Balance                  $100
               Amount Received                     50
   Costs
               Supplies                            10
   Fees
               $1000 Fee X 50% Complete
               less $100 Previously Billed        400

Now I'd like to add a single line to the bottom the totals everything, e.g.,

   Balance
               Previous Balance                  $100
               Amount Received                     50
   Costs
               Supplies                            10
   Fees
               $1000 Fee X 50% Complete
               less $100 Previously Billed        400
   TOTAL DUE UPON RECEIPT                        $460

What's the best way to do this? I can't add "TOTAL DUE..." to my table because I want "$460" to be on the same line. When I add "TOTAL DUE..." to the Page Footer it winds up on the bottom of the page, which isn't what I want either. I'm thinking I neet to add a second category(?) but don't know how to do this.

Thanks!
Rich

 

[Robbo1974]

Rich,

A bit confused here, what is the structure of the underlying table/query?

I'm assuming that you have three fields - balance, amount rec'd and costs?

Is the value of $1000 Fee X 50% Complete less $100 Previously = 400 already calculated or do you need to do this as well?

Is the fee always $1000?

Is the multiplier always 50%?

Where are you storing the 'previously' data?

The best way to do this is to not store the 'TOTAL DUE...' at all, but just to calculate it from the individual field values and display the value in a calculated control on the report, but I need further info on the above to help you with setting the value for this control.
Robbo ;-)

 

[rcoutts]

No. My (3) columns are: "Category", "Description", and "Subtotal." E.g.,

   Balance     Description                       Subtotal
   ----------- --------------------------------- --------
   Balance     Previous Balance                  $100
   Balance     Amount Received                     50
   Costs       Supplies                            10
   Fees        $1000 Fee X 50% Complete          (Null)
   Fees        less $100 Previously Billed        400


Everything's calculated already.  My Report then groups everything under the values in "Balance."  My proplem is that "TOTAL DUE UPON RECEIPT" won't fit into this format because of what I described in the original post.

Regards,
Rich

 

[Robbo1974]

Rich, bear with me but I'm still confused by your design.

Does the table have only three columns? If no what are the other ones?

Is the db tracking orders or subscriptions or something along those lines? Does each order have it's own unique ID?

Is it essential that you store the 'total due' somewhere in your db or is it fine to calculate it all the time?

Will you need to print a list of all of your 'orders' or will you simply be printing details for one particular 'order'?

Can you revise your design or would that cause you undue problems?

Does your report have a section header and footer for this information (maybe OrderNumber header and footer)?

For your current situation I take it you have three controls in the detail section of your report - category, description and subtotal?

I am assuming that you have a one table database where you enter a start balance for a particular order, and then add payments as received, with your report being the final balance invoice?

If you are entering debits as positive numbers and payments recieved as negative numbers it should just be a case of putting a calculated control into the footer of your section of your report with the control source set to:

=Sum([Subtotal]) which should leave you with the amount outstanding. Obviously you can then attach a label which states ' TOTAL... '

If this is not the case then things start to get a little complicated...

If I was tracking this I think I would probably structure things a little differently, you seem to have the potential to be storing a huge number of duplicate values.

If the file's not too big send me a copy at iain.robertson@leedsrugby.com and I'll happily take a look. It's often easier to see things when you've got them in front of you! ;-)


Robbo ;-)

 

[rcoutts]

Robbo,

Thanks for your posts. I think you may be reading a bit too much into my questions. I really have a simple report formating question. My Table has (3) columns, e.g.,

  Column1         Column2           Column3
  --------        ----------        -------
  BALANCE         Previous Balance    $1000
  BALANCE         Amount Received       $50
  FEES            New Fees             $100
   ...              ...                 ...

My Report Wizard put "Column1" into a Category Header and "Column2" and "Column3" into a Detail, so my Report looks like this:

  Column1
          Column2                  Column3
          Column2                  Column3
  Column1
          Column2                  Column3
Or
  BALANCE
          Previous Balance            $1000
          Amount Received               $50
  FEES
          New Fee                      $100

Now I simply want to append to my report the total, e.g.,

  BALANCE
          Previous Balance            $1000
          Amount Received               $50
  FEES
          New Fee                      $100
  TOTAL DUE UPON RECEIPT              $1050

The problem is that the "$1050" and "TOTAL DUE..." are in the same row, and "TOTAL DUE..." is left justified with "Column1," so it doesn't fit into my current formating for the Report. Putting "TOTAL DUE..." in the footer is no good because it winds up at the bottom of the page which isn't what I want.

So, my question is simply how do I append a new row to my Report that has a different format than its current category? Can I add another Category that will append itself?

-Rich

 

[Robbo1974]

Ok, maybe I am reading a little too much into it - it's just that I'm finding it really hard to understand how you are performing calculations based on debits/credits without seperating them into categories.

However, have you tried the calculated control approach as described above? If this throws up the right figure then the problem is just getting it in the right place right?

You say that the report wiz split col 1 off for the section header - did it also add a footer? If not create one. Placing the calculated control in the section footer should allow you to tuck it right up beneath the detail records.

Hope this helps, Robbo ;-)

 

[rcoutts]

Ahhh... by "Section Footer" you're referring to the Report Footer? It does exactly what I need. Thanks for the help!

 

[Robbo1974]

Not exactly but if it does what you need then go with it!

The one proviso is that this approach means that your 'total' data will only appear at the end of the report, so if you have more than one set of detail records on the report the calculated control will show the sum for all detail records.

If you need to be more specific then add a footer to the report dedicated exclusively to your column 1 section. You can do this by clicking the sorting and grouping properties button, highlighting the 'column 1' listing and changing the 'group footer' property to yes. This will then keep your 'total' data together with the detail record that it pertains to.

You should end up with a design view arrangement something like this:

Report Header
---------------
Page Header
---------------
Column 1 Header
---------------
Detail
---------------
Column 1 Footer
---------------
Page Footer
---------------
Report Footer

HTH Robbo ;-)

 

[Guest_imported]

Can someone help with a report question?
I have some data that I want split in a report. The data all comes from a query which holds 82 primary schools and 7 special schools. I want the the special schools to appear on the last page of the report after the 82 primary schools. I cannot seem to get the report sorting and grouping on the footer to work properly and am obviously doing something wrong.
I basically want the 82 schools to appear under one heading and the remaining 7 specials to appear under another at the bottom.
Hope question makes sense.
Can you help?

Rob