Report Grand Totals

[baugie]

I am creating a report based upon two tables with a 1 to many relationship. Both tables have a quantity field that I must aggregate on. However, when I create the query the quantity from the primary table is aggregated for each instance of the child record. This causes a erroneous value in the grand total that is much higher than what it should be. How do I get Access to only aggregate on each individual record in the primary record? I would hope that there would be a simple function for this, but can't seem to find it.

Thanks in Advance!!

baugie

 

[PHV]

Can you please post the SQL code of your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ckaspar]

Obviously I don't know how your report is organized but when I am designing a report where I am going to use aggregate function I make sure to include an Autonumber ID from each table as the criteria for the aggregate function.

Now you will have to format the detail and group header/footer to make sure that the ID is being shown in the correct placement on the report.

I typically make the ID visible=false because sometimes it it not nescessary to be seen but is required to get the right total in the aggregate function.

If I had more detail about yuor report format I might be able tp provide more information to help.

Chris Kaspar
Independent Systems and Software

http://www.independentsystemsandsoftware.com

Twin Town Treatment Centers

http://www.twintowntreatmentcenters.com

 

[baugie]

The report was created with a wizard, so there is no underlying query. However, here is the structure that is used...

Table_1 Table_2
Index_1 Index_2
Text_1 Index_1
Quantity_1 Desc_2
Quantity_2


Select Table_1.Text_1, Table_1.Quantity_1, Table_2.Desc_2, Table_2.Quantity_2 From Table_1 Inner Join Table_2 On Table_1.Index_1 = Table_2.Index_1

I need to total all of the Quantity_1 and the Quantity_2 into a grand total. However, because this is a one to many relationship the Quantity_1 is over represented in the total.

Thanks

baugie

 

[lespaul]

Even if your report is created by the wizard, there's an underlying query. The select statement you provided is a query, but there's no aggregate function (no summing).

What is the SQL for the report?

Leslie

 

[PHV]

You have to play with sections with Index_1 as group.
You may get more accurate replies in the Access reports Forum703

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ckaspar]

Try using a Dcount function that will give you the total of what you are looking for in Table_1

For Example:

=Dcount("[Table_1]![Index_1]","Table_1","[Table_1]![Index_1]=[index_on_report]")

That basically says count all of the Index_1 in Table_1 directly, prior to the one-to-many relationship that you have with Table_2, where Index_1 equals the text box called index_on_report. That will give you the total count from Table_1 only.

Place the above function in a text box in the same section as your current data. Rename the Index_1 object on your report to index_on_report. Run the report.

Try that and if you have questions let us know.

Chris Kaspar
Independent Systems and Software

http://www.independentsystemsandsoftware.com

Twin Town Treatment Centers

http://www.twintowntreatmentcenters.com