distinct total? 2

[wshm]

I have 2 tables.
tbl_DeptBudget
which contains .DeptbudNo, .DeptbudAmt,etc
and
tbl_BudgetDetail
which contains .DeptbudNo, .BudTxAmt,etc

DeptBudget is the main table and DeptbudNo is unique.
however, BudgetDetail can contain same DeptbudNo.
The sum of tbl_BudgetDetail.BudTxAmt is equal to
tbl_DeptBudget.DeptbudAmt.

I need to create a form to add or update BudgetDetail
information and before I do I just want to make sure
that I know what Iam doing.

How can i find records that share same DeptbudNo and sum the budget amount?

ie. BudNo | BudTxAmt
a1 | 20,000
a1 | 10,000
a2 | 23,000

need to return
BudNo | DeptBudAmt
a1 | 30,000

I need update current and upcoming information so Im
guessing update query is the way to go...
I have no clue how this can be done.

Any suggestions?

 

[Remou]

You can create a query that groups by the BudNo and sums DeptBudAmount. Use the sigma [Σ]

 

[PHV]

A starting point (SQL code):
SELECT DeptbudNo, Sum(BudTxAmt) AS DeptbudAmt
FROM tbl_BudgetDetail
GROUP BY DeptbudNo

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

 

[wshm]

So i can group and sum same time /w one query?
im really not familiar with queries.. i have
no idea to put in the fields..

 

[wshm]

ok.. nvm. i played around and i understood what you meant.
one last question. How do I use that information to
update the other table?

 

[PHV]

UPDATE tbl_DeptBudget
SET DeptbudAmt = DSum("BudTxAmt","tbl_BudgetDetail","DeptbudNo='" & DeptbudNo & "'")

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

 

[wshm]

thank you guys so much! my heroes of the day!