combine fields in a payoff system

[Guest]

I have two tables tied together with reference to the customer name. I want
the amount owed on the customer to be updated with the amounts paid. There
is only one record in reference to the customer.

For example, I would type in Bob Smith as the customer and type in $150.00
owed in his name. On the other table it would show multiple payments tied to
this customer. Bob Smith could have two payments of $120.00 and $30.00,
therefore the query I have set up as a list box would show $0.00. However,
instead I have two records in the query with different balances.

What I have:
customer AmountOwed AmountPaid Balance
Bob Smith 150.00 120.00 30.00
Bob Smith 150.00 30.00 120.00

What I want:
Bob Smith 150.00 150.00 0.00

How do I combine these records by customer name?

 

[John Vinson]

I have two tables tied together with reference to the customer name. I want
the amount owed on the customer to be updated with the amounts paid. There
is only one record in reference to the customer.

For example, I would type in Bob Smith as the customer and type in $150.00
owed in his name. On the other table it would show multiple payments tied to
this customer. Bob Smith could have two payments of $120.00 and $30.00,
therefore the query I have set up as a list box would show $0.00. However,
instead I have two records in the query with different balances.

What I have:
customer AmountOwed AmountPaid Balance
Bob Smith 150.00 120.00 30.00
Bob Smith 150.00 30.00 120.00

What I want:
Bob Smith 150.00 150.00 0.00

How do I combine these records by customer name?

Create a Totals query by clicking the Greek Sigma icon; select Group
By for the unique customer ID (you might have two customers, Bob Smith
the nice guy who always pays on time, and Bob Smith that deadbeat
who's always six months behind). Select Sum for the AmountOwed and
AmountPaid fields, and put

Balance: Sum([AmountOwed]) - Sum([AmountPaid])

in a vacant Field cell to calculate the balance dynamically. Use First
as the function for the customer name to get each Bob Smith in his own
record.

John W. Vinson[MVP]