Update table with form calculation?

[tpann]

Noob here, sorry if this has been addressed elsewhere. I don't know how to look for it.

So I have two tables, tbl_Main and tbl_Details. tbl_Main contains information about individual transactions. The total amount of a transaction can be spread across multiple budget categories so tbl_Details contains specific amounts for each category for a given transaction -- sub-transactions. A transaction and its details are linked by tbl_Main's transID.

Amounts are tracked in tbl_Details but no amount is kept in tbl_Main.

The two tables are represented by a form (tbl_Main) and a subform (tbl_Details). I have managed to get a transaction total to display on the main form based on the sum of the sub-transactions in the main form.



But now I want to create reports that display both transaction total amounts and transactions broken out by category. So somewhere the report has to get a given transaction's total from tbl_Details. I am stuck because I don't know how to do that.

I have assumed I don't want to store the transaction total in tbl_Main, is that correct? But if I do then I don't know how to dump that value from the form back into the table. Like I said, noob.

Thank you!

[ranman256]

the form can use tDetails as a subform for you to see 1 person & all their transactions.
for the report, make a query that joins the 2 tables and this is used in the report.
the report would be similar to the form, a master record group, and all records in the details row would show the tDetails.

the report wizard could do this for you.