Sum on Group, but Displayed on Another Group 1

[ElectronicLady]

In MS Access, On a Report:

I want a total on a group of records. Whats more, I want to subtotal this group of records, but add the text box to another group's footer.

How can I do this?????

My current code looks like the following, and it works as long as the text box is on the same group that I'm adding up. But that's not what I want. As I said above, I want my text box on the another group.

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

BTW, my Grouping is as follows:

1st level: Claimant
2nd level: Site
3rd level: SeatType

I want to add the total amount of SeatTypes (per Claimant/Site), and show the Sum on the Claimant footer level.

 

[THWatson]

ElectronicLady
I'm not 100% sure what totals you want where...but you should be able to put a text box in the SeatTypes Group Footer, and then reference that text box in the Claimant Group Footer. (e.g. the text box in the Claimant Group Footer would have its control source as "=TheTextBoxInTheSeatTypesFooter"

Tom

 

[ElectronicLady]

Tom,
I think you get my point Did I mention that I'm a novice?

Anyway, following is a snapshot of a portion of my report as it is now.

http://home.comcast.net/~electroniclady/subtotal.jpg

You'll notice that the Subtotal of Specialty Seats is displayed on several lines by Site, and the Total of Remaining Seats is displayed after all the Subtotals. The Total Remaining Seats is a total sum of all seats by Claimant.
The report Groups/Sorts
Claimant/Site/Specialty Seats
Have a relationship such as the following example:
Claimants = Franchise
Site = Location
Specialty Seats = Products

As I mentioned earlier, I want to continue to subtotal the Specialty Seats, however, not by Site but by CLAIMANT.
The control source code (see above post) that I have on the Specialty Seat footer does not work correctly when I move it to the Claimant footer. This is where my next question to you comes in………

How would I write the code that you suggest? Please let me know if I should be more specific.

Thank you!

 

[THWatson]

ElectronicLady
You indicated, in your first post, that you have 3 sorting levels - Claimant, Site, Seat Type. In the snapshot, I don't see any Claimant Footer. (unless it's there but the entire page won't view)

So, is the following the scenario?
Walter Wiggins owns a franchise, so he is the Claimant. He has 3 different sites - A, B and C. In A he has 12 seat types, in B he has 14 seat types, and in C he has 24 seat types. Therefore, in the Group Footer for Walter Wiggins you want to show 50 seat types.

If that is the correct scenario, then could you post the SQL from the query that populates the report?

Tom

 

[ElectronicLady]

Tom,
The Claimant Footer is....
Total Remaining Seats for _____ 5592

Since I'm really quite the novice, I'm not following you. What SQL would I post?

 

[THWatson]

ElectronicLady
You didn't indicate whether or not my scenario was correct. But based on the assumption that it was, here's what I did, so you can see whether or not that matches your set-up.

1. I made a table called tblClaimant. In it are two fields - ClaimantID (auto number, No dups) and Claimant (text).
2. I made a second table called tblSite. In it are 4 fields - SiteID (auto number, dups ok), ClaimantID (number, foreign key that is the same as the ClaimantID field in tblClaimant), Site (text), SeatType (number)
3. In the Relationships, I joined the ClaimantID fields from tblClaimant and tblSite in a one-to-many relationship, and enforced referential integrity.
4. I made a query, called qryClaimant. In it are the two tables - tblClaimant and tblSite.
5. I made a report, called rptClaimant, with the RecordSource being qryClaimant. The Detail section has three controls - Claimant, Site, SeatType.
Sorting and Grouping has two levels - Claimant and SeatType - and both have Group Footers.
In each of the Footers, I put a text box that has the control source as =Sum(SeatType)
So the report shows 3 sites for the Claimant Walter Wiggins. The Group Footer for Site A shows 12, for Site B shows 14, and for Site C shows 24.
The Claimant Group Footer shows 50.

Unless I am missing something, this is the set-up you want.

As for the SQL...I assume the report is based on a query. Go to the query, show the SQL View rather than the Design View, and copy the SQL and paste it here.

Hope this is helping.

Tom

 

[ElectronicLady]

Tom,
The scenario as you just wrote up is not correct. I’ll try to explain better, by following your example. First remember my example of
Claimant = Franchise
Site = Location
Specialty Seat = Product
1. I have a table called tblFranchise. In it are several fields including Claimant, Site and Specialty Seats.
2. I have a Query called qryClaimant based upon tblFranchise. The criteria is [Enter Claimant:] on the Claimant field.
3. I have a report called rptProductTotals, with the RecordSource being qryClaimant.
a. Sorting and Grouping has (3) levels (Claimant, Site & Specialty Seat Type).
b. Seat Type and Claimant each have a footer.
c. I have a text box with a control source as =Sum(SeatType) on the Specialty Seat Footer
d. I have a text box with a control source as =Sum(Claimant) on the Claimant Footer
Keep in mind that the Seat Type is actually ‘different types’ of Seats; therefore this will be several different amounts -- per Seat Type -- which would be equal to the (1) Grand Total amount of Remaining Seats that is displayed on the Claimant footer. [Total Remaining Seats for ‘Claimant’ 5592]

http://home.comcast.net/~electroniclady/subtotal.jpg

To reiterate, I want to display both my subtotal of the Seat Type and Grand Total on the Claimant Footer. I’ve tried moving my Subtotal code (as it is) to the Claimant footer, but it does not work. That is, it does not give me the total ‘per seat type.’

Any suggestions?

 

[THWatson]

ElectronicLady
Access has to have a way of differentiating between the Sites, and...unless I'm missing something...it sounds to me as if your data isn't being fully normalized.

If FranchiseA has 4 sites, and there can be several different SeatTypes in each of those 4 sites, how is the data to properly be accumulated when the Claimant, the Sites and the SeatTypes are all in the one table?

From what you describe, I would be inclined to set it up more along the lines that I suggested. Have a Franchise table, a Site table, and a SeatTypes table.
Relate the Site table to the Franchise table. And relate the SeatType table to the Site table.

In other words, there is a need to keep the Sites that relate to FranchiseA totally separate from those that relate to FranchiseB...and keep the SeatTypes that relate to the Sites for FranchiseA totally separate from the SeatTypes that relate to FranchiseB.

I am gone now for the rest of the day, but I will check in here later tonight and see whether or not you replied further.

If you think that you should be able to get what you want from your existing set-up, then please pose the SQL for your query, as I haven't seen that yet.

Keep plugging! You'll get there.

Tom

 

[ElectronicLady]

Tom,

I don't know how I would do what you've suggested. I'm very new at this, and am working alone. I have several other fields/columns besides Claimants, Sites and Seat Types. I do not know how I would create different tables and also include the other fields. Also I have several different Queries, so I think it would be too advanced for me at this point. At least more advanced than I want it to be

Anyway, here is some additional information that may help you assist you in helping me create what I want to do, utilizing only one table.

My report

‘counts’ each Seat Type (a text field).

It Groups each different ‘Seat Type’ with it's specific ‘Type’.

So if there are 10 'Basic' Seat Types, 20 'Classified' Seat Types and 30 'Regular' Seat Types, I need the Report Subtotals to be grouped accordingly.

Example of the Report Subtotals would be…..

__Seat Type Subtotals__

Basic = 10
Classified = 20
Regular = 30

I want these Subtotals on the Claimant footer of the report.

Already on the Claimant footer is the Grand Total number of All Seats which is only Grouped by Claimant.

 

[THWatson]

ElectronicLady
It's after midnight here and I just got home.

I'm not at all sure how to help you get what you want from one table...because, from what you describe, trying to do everything in one table is only going to lead to further problems.

Your comment "more advanced than I want it to be" suggests to me that you find some of this stuff pretty daunting. And, yes, it can be. Particularly when you're, as you say, new at this, and working alone.
But when is the best time to be sure that things are being done properly?

I might be able to help further if you would post the SQL behind the query that populates the report. To do that...
1. Go to the query and open it in Design View
2. Then click on SQL View
3. Highlight the SQL, copy it, and paste it here.

The other way I might be able to help further would be if you could send the database to me. You may or may not be interested in that approach, depending upon the sensitivity of the data. (But, believe me, I am not the least interested in the data itself...only in helping you get things working)
If you are interested in sending the database to me, reply to that effect, and I will then indicate how that can happen.

When you have time, you might like to take a look at the following link from the Microsoft site.

http://office.microsoft.com/en-us/assistance/HA011201381033.aspx

And there is lots more information on the Microsoft site, and on other sites as well.

Tom

 

[ElectronicLady]

Tom, you are sooo KIND!!!!! Thank you so much for all your help...I really appreciate it

<But when is the best time to be sure that things are being done properly?>

I'm more than will to learn, but my report is due today! So you see...I don't have much time for a learning curb.

<I might be able to help further if you would post the SQL behind the query that populates the report.>

There is no SQL behind my report. My query is my table and I'm using a *simple* criteria on the table.

<The other way I might be able to help further would be if you could send the database to me. You may or may not be interested in that approach, depending upon the sensitivity of the data.>

I wish I could, but I'm a government employee.

But anyway, I've posted my question on several forums. You by the way have give the most time and effort to me

None the less, It seems I might possibly be able to create variables in the SeatType footer and call them in the Claimant footer. At least that what it seems like several individuals have indicated to me (on various online forums). The problem is that I don't know how to write the code or even where to write it. I quite the Novice!!!! This report is due today

Do you have any suggestions????

 

[THWatson]

ElectronicLady
So your report is based directly on the table...rather than on a query? Well, if that's the case then there is no SQL to post.

I'm not clear as to what you mean by a *simple* criteria on the table ??

I really don't know what to suggest that would help you get that report out today. Perhaps the other online forums people, who made suggestions, could help you flesh it out further.

I understand the restrictions on sending the database. You could probably make a copy of the database, remove all the data, and send that...as then there would be no sensitive data in it at all.

Failing that, I'm sorry that I couldn't be of further help.

Take care. Good luck with your project.

Tom

 

[ElectronicLady]

Tom, I've edited my data. Where can I send it to you?

 

[THWatson]

ElectronicLady
I can't post my e-mail address because that would attract undesirable stuff to the site...but you can interpret the following.

Send it to...
twatson at sentex dot net

If you can Zip the file, do so. If not, send it as is.

Tom