Question regarding Fee's and applying to multiple records

[3Mark3]

Good morning,
I'm having a problem building a query on a type I've not worked before. Here's the situation.

I have a field (we'll call this a FEE) within a table that contains multiple fees for a given loan number. I have another field (we'll call this a REMAINING AMOUNT) that contains an amount that I can use to remove those fee's. What I'm trying to figure out is how I can subtract each FEE from the REMAINING AMOUNT.

Normally if this was a constant 2 fee's I would know how to do this. However, there can be any number of fee's. I would like to avoid having to recreate a bunch of queries to eliminate the fee's. After I remove all of the fee's any remaining sum needs to extract from other amounts (in different tables). Is there an efficient way to do this? I'm rather stuck right now.

Any help would be greatly appreciated

Data Example

LOAN FEE REMAINING AMOUNT
1234 30.22 100.00
1234 34.22 100.00
1234 18.54 100.00
1234 10.22 100.00
1239 05.22 100.00
1239 08.12 100.00

 

[Shirley]

Create an update query, add a new field called Amount: ([Remaining]-[Fee]). Then update the table with the update query. All you would have to do the next time is to rerun the update query to update the information in the table.

 

[lespaul]

storing a calculated value breaks normalization rules.

This query should do what you are asking for (A2000 and above):
[tt]
SELECT Loan, Amount - TotalFees As TotalDue FROM
TableName T
INNER JOIN (SELECT Loan, SUM(FEE) As TotalFees FROM TableName GROUP BY Loan) F on F.Loan = T.Loan
[/tt]
if A97 two queries:

[tt]

qryFeeTotal

SELECT Loan, SUM(FEE) As TotalFees FROM TableName GROUP BY Loan

qryAmountDue

SELECT Loan, Amount - TotalFees As TotalDue FROM
TableName T
INNER JOIN qryFeeTotal F on F.Loan = T.Loan[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi