Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals in reports (Access 97)

    Because of legacy issues I am working with an un-normalized relationship where I have a field called ContractTotal repeating in each detail line of a query unlying a report, PLUS I have Amount in each detail line. The report has 2 groups with group header and group footer turned on: 1) Sales Person; 2) Job Number. On the report, the total of the Amount field for each Job Number should equal the ContractTotal. I am not having a problem adding the Amount field for each Job Number, then for each Sales Person, then for the whole report. But I'm having a terrible time with the ContractTotal. I tried writing code in the OnPrint event for JobNumberFooter, SalesPersonFooter and ReportFooter and it works when I'm stepping through in debug mode. But when I run it like an end-user, I sometimes get the right totals and sometimes NOT!^%$@ <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    Eeekkk! What to do?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totals in reports (Access 97)

    You say that ContractTotal exists for legacy reasons. But that doesn't mean that you're obliged to use it in your report. Why don't you ignore ContractTotal entirely and just use the sum of Amount. I think you can rely on Access adding the individual values of Amount correctly, I have never experienced problems with that. If the sum of Amount differs from the ContractTotal field, I would tend to suspect ContractTotal not having been updated correctly - that is why a normalized design is to be preferred.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals in reports (Access 97)

    Hans - thank you and you're right, but I'm stuck with having to use ContractTotal. But I think I've found the solution in PrintCount. Here's an excerpt from Help:

    " If you print a report containing order information, you might keep a running total of the order amounts. The following example shows how you can use the PrintCount property to make sure the value in the OrderAmount control is added only once to the running total:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If PrintCount = 1 Then
    RunningTotal = RunningTotal + OrderAmount
    End If
    End Sub

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

    Re: Totals in reports (Access 97)

    In the past I have found this doesn't always seem to work. However if it works for you, then well and good.

    I would tend to do what Hans suggests and use a Sum, that's what it is there for.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals in reports (Access 97)

    Well - it didn't work. I had to use DSUM in the end.

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

    Re: Totals in reports (Access 97)

    Well done. See you don't need these people here all the time, do you?

    Seriously though, what was the condition that you used in the DSUM that you could not use on the report with a Sum?

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals in reports (Access 97)

    Hi Pat - We used to record the amount of a sale in Contract Total. Then we created a new table which stores a bunch of detail records which break the total sale into its consituent parts. Sort of like detail items in an order. I am using SUM to add up the amounts in the detail records. But I can't use SUM on ContractTotal because when I join the Contract table in one-to-many to the Detail Table, then ContractTotal repeats for each detail line. I have to include the Contract Total in the report, because if management run the report for, say, the last year, there will be nothing in the Detail table. So I created a new query, using all the same tables as the query underlying the report, and turned on UNIQUE. Then this query became the domain for the DSUM.

    It's working but I feel dumb because it took me 4.5 hrs to figure it out and get it working. Just when I think I know Access something like this comes along to humble me again. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: Totals in reports (Access 97)

    >>Just when I think I know Access something like this comes along to humble me again. <<

    Be consoled by the fact it has happened to all of us.

Posting Permissions

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