Sum Query

[Irina]

I have to link a table that contains payments with a table that contains events for each contract. However, every time I try to display the sum of all the payments, all the payments are repeated as many times as there are events. e.g. if there are two payments: 100 and 50 and two events, the query will return 300 as the sum (instead of 150).
The query looks like this:
SELECT Sum(Payments.AMOUNT) AS SumOfAMOUNT
FROM Payments INNER JOIN Events ON Payments.KNUM = Events.Knum
WHERE (((Payments.KNUM)="11111"));

How can I get the sum to reflect an accurate amount independent of the number of events (but still include the Events table in my query)?
Thank you

 

[GumbyD]

Irina -

Looking at your SQL I can't see any reason you would need the events table in your query all it is doing by being there is doubling your records and causing your sum value to be to large. Why do you need that table in the query? Try the SQL:

SELECT Sum(Payments.AMOUNT) AS SumOfAMOUNT
FROM Payments
WHERE (((Payments.KNUM)="11111"));

See if that gives you want you want.

 

[Irina]

Sorry,
I guess I was not very clear...this query will be part of a much larger query that includes some fields from the Events table. I didn't know if it can all be done in one query or if I will have to create a temporary table to store the sum of the payment and then link it to the Events table (which I didn't want to do). Thank you for your reply.

 

[GumbyD]

Since there are multiple records in the other table it will contine to cause you issues. You are going to need to either use this query as a base or create a table like you mentioned.