Sum of child form values shown in parent form and updated in database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

H

I have two table
T Main with column
CNu
Custome
SubTota
Ta
Grand Tota

T Detail with column
CNu
Produc
Qt
Pric
Amount

There are two forms F Main for table T Main and a sub form F Detail for table T Detail. Both show the above columns on the form and are linked on CNum. The amount is calculated on the F Detail and updated in the T Detail. I kept this column in T Detail thinking it would ease the issue given below

I need to show the Sub Total on form F main and update the Sub Total box in the table T Main. Sub Total is the sum of all the Amount for which D Num’s match.

If there is a Tax value it is added to Sub Total to give Grand Total

The issue – I need to update the Sub Total on the form F Main and table T Main. Please help

Thank you in advance for your help and time.

Varu
 
Hi Varun

it is not considered "good" practice to store calculated fields in tables,
but to do the calculation each time that it is needed.

However to show the calculation on the main form you need to include a
calculated field in the sub form's form FOOTER, e.g. control source of text
box =sum([Amount]) (and Name this field something like TotalAmount)
then on the main form you have another text box with the control source of
=[subform's name].[Form]![TotalAmount]
where the "subform's name" is retrieved by right mouse clicking on the
subform when viewed from the main form and looking at the name under
Properties.

Then i would have another text box with the tax amount, and a third to add
together the TotalAmount (from the subform) & the tax, but i would not store
the subtotal or grand total in the table.

Hope this makes sense and helps you.

Cheers
JulieD
PS i do have a demo database you could have a look at if you would like me
to email it to you.





Varun said:
Hi

I have two tables
T Main with columns
CNum
Customer
SubTotal
Tax
Grand Total

T Detail with columns
CNum
Product
Qty
Price
Amount

There are two forms F Main for table T Main and a sub form F Detail for
Click to expand...
table T Detail. Both show the above columns on the form and are linked on
CNum. The amount is calculated on the F Detail and updated in the T Detail.
I kept this column in T Detail thinking it would ease the issue given below.
I need to show the Sub Total on form F main and update the Sub Total box
Click to expand...
in the table T Main. Sub Total is the sum of all the Amount for which D Num'
s match.
 
Hi Julie,
Thanks for your reply! I love your solution. This had
been driving me nuts.

I fully agree with you about not storing calculated
values in the tables. It was like that but when I came
upon this problem of subtotal I thought it would help me
but it didnt!

I would like to see the demo database. If it is not too
big mail me at (e-mail address removed)

Thanks again,
Varun

-----Original Message-----
Hi Varun

it is not considered "good" practice to store calculated fields in tables,
but to do the calculation each time that it is needed.

However to show the calculation on the main form you need to include a
calculated field in the sub form's form FOOTER, e.g. control source of text
box =sum([Amount]) (and Name this field something like TotalAmount)
then on the main form you have another text box with the control source of
=[subform's name].[Form]![TotalAmount]
where the "subform's name" is retrieved by right mouse clicking on the
subform when viewed from the main form and looking at the name under
Properties.

Then i would have another text box with the tax amount, and a third to add
together the TotalAmount (from the subform) & the tax, but i would not store
the subtotal or grand total in the table.

Hope this makes sense and helps you.

Cheers
JulieD
PS i do have a demo database you could have a look at if you would like me
to email it to you.





news:20F3BBFC-A18E-4A1E-AE59-
Click to expand...
(e-mail address removed)...
Hi

I have two tables
T Main with columns
CNum
Customer
SubTotal
Tax
Grand Total

T Detail with columns
CNum
Product
Qty
Price
Amount

There are two forms F Main for table T Main and a sub
Click to expand...
form F Detail for
table T Detail. Both show the above columns on the form and are linked on
CNum. The amount is calculated on the F Detail and updated in the T Detail.
I kept this column in T Detail thinking it would ease the issue given below.
I need to show the Sub Total on form F main and update
Click to expand...
the Sub Total box
in the table T Main. Sub Total is the sum of all the Amount for which D Num'
s match.
If there is a Tax value it is added to Sub Total to give Grand Total.

The issue - I need to update the Sub Total on the form
Click to expand...
F Main and table T
Main. Please help!
Thank you in advance for your help and time.

Varun
Click to expand...


.
Click to expand...
 
Hi Varun

its on its way to you

Cheers
JulieD

Varun said:
Hi Julie,
Thanks for your reply! I love your solution. This had
been driving me nuts.

I fully agree with you about not storing calculated
values in the tables. It was like that but when I came
upon this problem of subtotal I thought it would help me
but it didnt!

I would like to see the demo database. If it is not too
big mail me at (e-mail address removed)

Thanks again,
Varun

-----Original Message-----
Hi Varun

it is not considered "good" practice to store calculated fields in tables,
but to do the calculation each time that it is needed.

However to show the calculation on the main form you need to include a
calculated field in the sub form's form FOOTER, e.g. control source of text
box =sum([Amount]) (and Name this field something like TotalAmount)
then on the main form you have another text box with the control source of
=[subform's name].[Form]![TotalAmount]
where the "subform's name" is retrieved by right mouse clicking on the
subform when viewed from the main form and looking at the name under
Properties.

Then i would have another text box with the tax amount, and a third to add
together the TotalAmount (from the subform) & the tax, but i would not store
the subtotal or grand total in the table.

Hope this makes sense and helps you.

Cheers
JulieD
PS i do have a demo database you could have a look at if you would like me
to email it to you.





news:20F3BBFC-A18E-4A1E-AE59-
Click to expand...
(e-mail address removed)...
Hi

I have two tables
T Main with columns
CNum
Customer
SubTotal
Tax
Grand Total

T Detail with columns
CNum
Product
Qty
Price
Amount

There are two forms F Main for table T Main and a sub
Click to expand...
form F Detail for
table T Detail. Both show the above columns on the form and are linked on
CNum. The amount is calculated on the F Detail and updated in the T Detail.
I kept this column in T Detail thinking it would ease the issue given below.
I need to show the Sub Total on form F main and update
Click to expand...
the Sub Total box
in the table T Main. Sub Total is the sum of all the Amount for which D Num'
s match.
If there is a Tax value it is added to Sub Total to give Grand Total.

The issue - I need to update the Sub Total on the form
Click to expand...
F Main and table T
Main. Please help!
Thank you in advance for your help and time.

Varun
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top