Report Total

[Guest]

H
I have 2 tables in 1 to many relationship.I need to create a report

DocNo:1 Amount:100,000---from the 1 side of the tabl
Mat1..Qty:5---many side of the tabl
Mat2..Qty:1

DocNo:2 Amount:50,000---from the 1 side of the tabl
Matx...Qty:50---many side of the tabl
MatY...Qty:10

In my report i group by DocN
DocNo....Amount....Description....Qt
1 100000
Mat1
Mat2 1

2 50000
Matx 5
Maty 10

In the report footer i have a text box with control source as =Sum([Amount]

The problem is that the total at the report footer shows 300,000--which means it takes amount of 100,000 and multiplies by the number of detail entries(2 in this case) and 50,000 x 2 and then it totals it

I need to total only 100,000+50,000=150,00
How to do thi

Thank
Marin

 

[Adam]

It may sound clunky... but I would create a New Query of
your Primary Table utilising any criteria that you had.

I would then calculate the Total in that Querie.

I would then bring together the Main Table, Sub Table and
your Total Query into a new all embracing Query.

I would then link the Report to that Query

 

[Duane Hookom]

Add a text box to the DocNo header
Name: txtAmtRS
Control Source: [Amount]
Running Sum: Over All
Visible: No
Then add a text box to your report footer:
Control Source: =txtAmtRS

--
Duane Hookom
MS Access MVP

Hi
I have 2 tables in 1 to many relationship.I need to create a report:

DocNo:1 Amount:100,000---from the 1 side of the table
Mat1..Qty:5---many side of the table
Mat2..Qty:10

DocNo:2 Amount:50,000---from the 1 side of the table
Matx...Qty:50---many side of the table
MatY...Qty:100

In my report i group by DocNo
DocNo....Amount....Description....Qty
1 100000
Mat1 5
Mat2 10

2 50000
Matx 50
Maty 100

In the report footer i have a text box with control source as =Sum([Amount])

The problem is that the total at the report footer shows 300,000--which

means it takes amount of 100,000 and multiplies by the number of detail
entries(2 in this case) and 50,000 x 2 and then it totals it.