How do I make an update query that uses multiple tables?

[Dedren]

Access 2002
-Advisory-
First, I have very limited knowledge of Access but I need to get a temporary
database in to replace the massive paper mess that goes on in this
department. I heard something about normalization in some of the searches I
did but I would not have the time to get into this. This is a quick fix for
now.

-Situation-
I have two tables, CASES and PAYMENTS. CASES is a master table with almost
everything in it, for my limited knowledge I had to resort to this. CASES
also uses a unique case number to track any case. PAYMENTS tracks payments
made, per case file. So there are multiple payments for each case number.

-Goal-
I want to make a simple update query that updates a field, BALANCE, already
in the CASES table with the sum of the payments field, PAYMENTAMOUNT, in the
PAYMENTS table and deduct that amount from the initial value in the CASES
table called EXPOSURE.

-Mathmatical-
EXPOSURE=initial balance, never changes
PAYMENTAMOUNT=payments made per case number, changes often
BALANCE=EXPOSURE-(Sum of PAYMENTAMOUNT), is updated through query

I would appreciate any help I have such little knowledge of this I not sure
what to include or not to so I tried to be as detailed as possible.

 

[Denis Dougall]

This doesn't do all that you need but it should point you in the right
direction..

SELECT DISTINCTROW CaseFile.caseNumber, CaseFile.exposure,
First(paymentHistory.paymentNumber) AS [First Of paymentNumber],
First(paymentHistory.CaseNumber) AS [First Of paymentHistory_CaseNumber],
Sum(paymentHistory.Amount) AS [Sum Of Amount], Count(*) AS [Count Of
paymentHistory]

HTH,

Denis
FROM CaseFile INNER JOIN paymentHistory ON CaseFile.caseNumber =
paymentHistory.CaseNumber
GROUP BY CaseFile.caseNumber, CaseFile.exposure;

 

[Dedren]

Thanks, I think. I have no clue what to do with that bit of code. Currently
I use design view to work on queries, is this something I would put in the
wizard?

This doesn't do all that you need but it should point you in the right
direction..

SELECT DISTINCTROW CaseFile.caseNumber, CaseFile.exposure,
First(paymentHistory.paymentNumber) AS [First Of paymentNumber],
First(paymentHistory.CaseNumber) AS [First Of paymentHistory_CaseNumber],
Sum(paymentHistory.Amount) AS [Sum Of Amount], Count(*) AS [Count Of
paymentHistory]

HTH,

Denis
FROM CaseFile INNER JOIN paymentHistory ON CaseFile.caseNumber =
paymentHistory.CaseNumber
GROUP BY CaseFile.caseNumber, CaseFile.exposure;

Access 2002
-Advisory-
First, I have very limited knowledge of Access but I need to get a temporary
database in to replace the massive paper mess that goes on in this
department. I heard something about normalization in some of the searches I
did but I would not have the time to get into this. This is a quick fix for
now.

-Situation-
I have two tables, CASES and PAYMENTS. CASES is a master table with almost
everything in it, for my limited knowledge I had to resort to this. CASES
also uses a unique case number to track any case. PAYMENTS tracks payments
made, per case file. So there are multiple payments for each case number.

-Goal-
I want to make a simple update query that updates a field, BALANCE, already
in the CASES table with the sum of the payments field, PAYMENTAMOUNT, in the
PAYMENTS table and deduct that amount from the initial value in the CASES
table called EXPOSURE.

-Mathmatical-
EXPOSURE=initial balance, never changes
PAYMENTAMOUNT=payments made per case number, changes often
BALANCE=EXPOSURE-(Sum of PAYMENTAMOUNT), is updated through query

I would appreciate any help I have such little knowledge of this I not sure
what to include or not to so I tried to be as detailed as possible.

 

[Dedren]

Sorry I never knew what that code was for I just found the, extremely
obvious, SQL view in the... View menu so now I have seen it for the first
time. This is just to get rid of my previous post since I am now looking
over the code to see if I can make sense of it and work on it from there.
Thank you for real this time

Thanks, I think. I have no clue what to do with that bit of code. Currently
I use design view to work on queries, is this something I would put in the
wizard?

This doesn't do all that you need but it should point you in the right
direction..

SELECT DISTINCTROW CaseFile.caseNumber, CaseFile.exposure,
First(paymentHistory.paymentNumber) AS [First Of paymentNumber],
First(paymentHistory.CaseNumber) AS [First Of paymentHistory_CaseNumber],
Sum(paymentHistory.Amount) AS [Sum Of Amount], Count(*) AS [Count Of
paymentHistory]

HTH,

Denis
FROM CaseFile INNER JOIN paymentHistory ON CaseFile.caseNumber =
paymentHistory.CaseNumber
GROUP BY CaseFile.caseNumber, CaseFile.exposure;

Access 2002
-Advisory-
First, I have very limited knowledge of Access but I need to get a temporary
database in to replace the massive paper mess that goes on in this
department. I heard something about normalization in some of the searches I
did but I would not have the time to get into this. This is a quick fix for
now.

-Situation-
I have two tables, CASES and PAYMENTS. CASES is a master table with almost
everything in it, for my limited knowledge I had to resort to this. CASES
also uses a unique case number to track any case. PAYMENTS tracks payments
made, per case file. So there are multiple payments for each case number.

-Goal-
I want to make a simple update query that updates a field, BALANCE, already
in the CASES table with the sum of the payments field, PAYMENTAMOUNT, in the
PAYMENTS table and deduct that amount from the initial value in the CASES
table called EXPOSURE.

-Mathmatical-
EXPOSURE=initial balance, never changes
PAYMENTAMOUNT=payments made per case number, changes often
BALANCE=EXPOSURE-(Sum of PAYMENTAMOUNT), is updated through query

I would appreciate any help I have such little knowledge of this I not sure
what to include or not to so I tried to be as detailed as possible.