Subtotal per Group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Subtotal per Group

Author

Topic

electroniclady
Starting Member

4 Posts

Posted - 2005-10-31 : 13:55:28

This a repost... I'm rewriting in an attempt to better clarify:

In a MS Access report, I need to create Subtotals based on a particular Group/Sort.
My report has 3 different Group/Sorts with the following relationships:
Group/Sort <--> Relationship
Claimant <--> Franchise
Site <--> Location
Specialty Seat Type <--> Product

I need to create Subtotals of the Specialty Seat Type grouped by Claimant.

To explain my current and desired situation, I’ll use the following illustration:
I have a table that I’ll call tblFranchise.
In it are several fields including Claimant, Site and Specialty Seats.
I have a Query called qryClaimant based upon tblFranchise.
The criteria is [Enter Claimant:] on the Claimant field.
I have a report called rptProductTotals, with the RecordSource being qryClaimant.
Sorting and Grouping has (3) levels (Claimant, Site & Specialty Seat Type).
Seat Type and Claimant each have a footer.
I have a text box with a control source on the Specialty Seat Footer
="Sub Total: Specialty Seat Type, " & [Claimant] & " at " & [Site] & " -- > " & [SeatType]
I have a text box with a control source on the Claimant Footer
="TOTAL CUTOVERS for " & [Claimant]

Keep in mind that the Seat Type is a field of ‘different types’ of Seats; therefore this Subtotal will be many different amounts -- per Seat Type -- which should be equal to the (1) Grand Total amount of Remaining Seats (that is already displayed) on Claimant footer.

To reiterate, I want to display both my Subtotal of Specialty Seat Type along with the Grand Total of Remaining Seats on the Claimant Footer.

I’ve already tried moving my Subtotal source code (as it is) to the Claimant footer, but it does not work. That is, it does not give me the Subtotal ‘per seat type.’

Here is a snapshot of a portion of the report: http://home.comcast.net/~electroniclady/subtotal.jpg

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-10-31 : 21:22:42

Hi electronic lady

this may be a better post, but I'm still finding it hard to work out exactly what you want....(not your fault - just the nature of trying to describe an access report in words!)

Q1. Can you give an example (ie list a subgroup of values) and tell us what value you are getting and what value you should be getting?
eg
Claimant, Site, Specialty Seats, Subtotal
X, Y, bla1, 7
X, Y, bla2, 9
subtotal = 16, but should be 23 or whatever

this will make it easier to see what is going on....

Q2. when you tried moving the Subtotal source code (as it is) to the Claimant footer, what did you get? Too big a number, too small a number, 0 or what? Or was there some other pattern to the result? This may help to diagnose what the problem is.

Q3. is there any chance you could use MS Paint on your jpg to "point out" the parts of the report, and which bit is wrong? Because it looks to me like you've already calculated subtotals per seat type??

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

electroniclady
Starting Member

4 Posts

Posted - 2005-10-31 : 22:53:48

Illustration A is the *incorrect* report format. It is created with the following SourceCode on a textbox on the Seat Type Footer. This displays the Subtotal on the Seat Type Footer.

="Sub Total: Specialty Seat Type, " & [Claimant] & " at " & [Site] & " -- > " & [SeatType]

Illustration A
CLAIMANT...............NWS Earle....................Classified, RAS...................1
.......................NWS Charleston...............Classified, RAS...................8
........Sub Total: Specialty Seat Type, CLAIMANT -- > Classified, RAS...........9

CLAIMANT...............NS Pascagoula................OBLD..............................3
.......................NAS Brunswick................OBLD..............................3
........Sub Total: Specialty Seat Type, CLAIMANT -- > OBLD......................6

CLAIMANT...............HRSC Pacific - Makalapa VSSD..............................6
.......................NCIS Norfolk Reg Fraud VSSD..............................2
........Sub Total: Specialty Seat Type, CLAIMANT -- > VSSD......................8

........TOTAL Remaining Seats (per Tactical Plan) for CLAIMANT...................22

Illustration B is the desired*correct* report format. I need to know what to do to get the following output. I’ve already tried a variation of the above SourceCode on a textbox on the Claimant Footer, but it did not group by Seat Type. It just gave me a Grand Total per Claimant. The report should look like the following:

Illustration B
Sub Total: Specialty Seat Type, -- > Basic, HREN 3
Sub Total: Specialty Seat Type, -- > Basic 1
Sub Total: Specialty Seat Type, -- > Army_AF 1
Sub Total: Specialty Seat Type, -- > VSSD 4
TOTAL Remaining Seats (per Tactical Plan) for Claimant 9

I believe your Variable idea will work, but I don’t know how to code it. Could you give me a sample code?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-10-31 : 23:53:42

Dearest lady

First example code - all I mean is:

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)
   s  = "Sub Total: Specialty Seat Type, " & Me.Claimant & " at " & [Site] & " -- > " & Me.SeatType
End Sub

and then in the later group

Private Sub GroupFooter2_Print(Cancel As Integer, PrintCount As Integer)
   Me.SubTotal   = s
End Sub

Note that the way you get to the code for each event is to right-click, properties (in designmode) on the area of the report (ie the background) where the control (eg the subtotal) is, then choose ON_PRINT. Ask if you're not sure what I mean.

I'm still not sure of which (exactly) footers you need to put it into...so don't type my code in verbatim...it should be obvious when you do it.

If that doesn't help, then I have some more questions for you...
1. how do you get "Basic, HREN 3" from the first sub group in illustration A - or are the two illustrations using different data?

2. in illustration A how come the TOTAL Remaining Seats is 22 and not 23 (9+6+8)?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

electroniclady
Starting Member

4 Posts

Posted - 2005-11-01 : 11:48:26

Laureate, the words are not showing up and the sum is not a Subtotal, it is a Grand Total.

I understood how to edit the On-Print code; however, I it wasn't clear to me how to edit the code specific to my needs (so I did use it verbatim).

<how do you get "Basic, HREN 3" from the first sub group in illustration A - or are the two illustrations using different data?>

'Basic, HREN' is a [SeatType] and '3' is a SubTotal

<in illustration A how come the TOTAL Remaining Seats is 22 and not 23 (9+6+8)?>

Both Illustration A & B are hardcoded/hand typed, by me. I was sleepy :)

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-11-02 : 16:47:24

Sorry I was not in yesterday.

OK - that makes slightly more sense...(I thought I was missing something). But could you show the "illustration B" using the same data as illustration A - otherwise its almost impossible to tell exactly where the problem is.

It all depends on which footers you put the code into. From your jpg, it looks like your report is grouping on:
Claimant
Specialty Seat Type

Are there other groupings? Where did you put the code above?

If the words are not showing up at all, then it sounds like s is being recognised separately as two different routine-level variables. This could happen if you do not have "Option Explicit" turned on in the report module.
Make sure s is declared in the report module level. eg

Option Compare Database
Option Explicit

Dim s As String

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)
   s  = "Sub Total: Specialty Seat Type, " & Me.Claimant & " at " & [Site] & " -- > " & Me.SeatType
End Sub

Private Sub GroupFooter2_Print(Cancel As Integer, PrintCount As Integer)
   Me.SubTotal   = s
End Sub

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources