How to let access count totals, and make sums with these totals

[MadSlayer]

I have a access database, with a number of disciplines and organisations, the thing i want access to do is give me totals per discipline per org. and then use the fixed amount to calculate the price per org.


HELLLPPP MEEE!!!!

 

[scriverb]

You can do this by use of Grouping and Sorting in ACCESS reports. After you select the Sorting and Grouping button at the top of the Design screen you will be able to identify the controls in the report that can be sorted and grouped. The grouping parameter allows you to select a Group Header and/or Footer. It is in these sections that a new control can be placed with the following code as the control source.
=Sum(me.DetailControlName)

This control will now sum the multiple detail records for this control within this group and display the total for you. Each subsequen Grouping Footer can have the same coded control and it will sum up the records according to the Grouping parameters. Within each a calculation can then be made with this figure to create the Pricing data.

I hope this helps you are the correct path to developing this technique in your database.

Bob Scriver

 

[MadSlayer]

I keep getting an #error outcome for my sum, and i don't quite get where to put the code you suplied

 

[MadSlayer]

ok, i've got access to add up the ammounts, but it displays a subtotal under each line with an amount on it, and the intention is to let access display a subtotal at the end of each page and then ad up all subtotals to a grand total, anyone got any ideas???

 

[Dor100]

You might want to try the same kind of process you just did, but with Page Footer, Report Footer, and a subreport in the Report Footer. Not sure if you can get subtotals for a page because it has to be based on some group of data, but you might.

 

[scriverb]

It sounds like your Sorting and Grouping might not be set up correctly. You have to sort your records from highest level to lowest and then create the Group Footers appropriate to where you want to add up the subtotals.

You sub-totallying control should be at the Group Footer for Discipline and Org. Each will total its own figures automatically and reset for the next group. Finally you can have one of these controls at the Report Footer to perform the same function.

Bob Scriver

 

[MadSlayer]

It works !!!!!! but i've only got subtotals, how can i get totals???? over the subtotals?

 

[scriverb]

Put the same control in the Report Footer.

Bob Scriver

 

[MadSlayer]

Cool, it works, tnx a lot. I have 1 qeustion remaining, my repport displays an empty page between page 1 and 2. What have I done wrong

 

[scriverb]

Sounds like the Page 1 was pretty much filled up when the Totals were printed. Make sure that you don't have much white space in the group footers. It is just printing that white space over to the next page and then starting a new page on #3 because there is a new group being started. Thus the empty page2.


Bob Scriver

 

[MadSlayer]

I've done it, with help ;-), i'm so happy, tnx to all peeps helping, whoopie.

 

[HRBimbo]

My subtotals look fine on the report. When I try to export to Excel, everything exports except for the subtotals. Do you have a solution for that?

 

[scriverb]

HRBimbo: Sorry, don't anything about that. But, logically a calculated field in the headers would not necessarily export. You will probably have to setup a calculated cell in the excel spreadsheet to perform that function.

Bob Scriver

 

[HRBimbo]

Thank you! That was my first post. I thought I turned on the email notification, but I didn't receive anything. Maybe it's lost in my spam filter?

 

[MadSlayer]

that could be, at the end of the tread it says of you have e-mail notification or not, but it can stick behind your spam filter