Sum Query is returning double

[Buddha1]

I have a query that sums all of the credit card transaction totals. For example Visa 100.00, Mastercard 200.00 and American Express 190.00. The American express amount should only be 95.00 but it is showing 190.00 (double the amount). I checked the table to make sure that there were only two entries totaling 95.00. The query is pulling the name and credit card type from one table and the dollar amount from another table. They are joined on the credit card number. Basically whenever I have a patient that has to transactions entered the sum query is taking those two transactions and treating it like four.
Here is the code Iam using:

SELECT DISTINCT TblSitePaymentInfo.Location AS Expr1, TblMain.Date, TblSitePaymentInfo.TypeOfCard, Sum(TblMain.Payment) AS SumOfPayment
FROM TblMain INNER JOIN TblSitePaymentInfo ON TblMain.[Acct #] = TblSitePaymentInfo.CreditCardNumber
GROUP BY TblSitePaymentInfo.Location, TblMain.Date, TblSitePaymentInfo.TypeOfCard
HAVING (((TblSitePaymentInfo.Location)=[Forms]![FrmMainPosReport]![CmbLocation]) AND ((TblMain.Date) Between [Forms]![FrmMainPosReport]![TxtStartDate] And [Forms]![FrmMainPosReport]![TxtEndDate]));

 

[cmmrfrds]

FYI, this should be a where clause before the group by otherwise the all the data is read before applying the select criteria - inefficient.

WHERE (((TblSitePaymentInfo.Location)=[Forms]![FrmMainPosReport]![CmbLocation]) AND ((TblMain.Date) Between [Forms]![FrmMainPosReport]![TxtStartDate] And [Forms]![FrmMainPosReport]![TxtEndDate]))

Doubling up the amount is probably due to the cartesian effect of having multiple hits on the key fields in your join. Find a way of only joining one record for the multiple values. Maybe you are trying to do that with the having clause and changing to the where clause will fix it.

 

[lespaul]

In the Query Design grid, right click on the relationship line between TblMain and TblSitePayment. You will have three choices (the first is what you currently have):

Select only those records in each table that match.
Select all records from tblMain and only those that match from tblSitePayment.
Select all records from tblSitePayment and only those that match from tblMain.

the other two choices are a left join and a right join. Select the correct choice for what information you want to return and it should work!

HTH

Leslie

 

[Buddha1]

I tried the where clause and it didn't work.
The join is set up to only include fields that are equal in both tables. Are there any other suggestions??

 

[lespaul]

The join is set up to only include fields that are equal in both tables
That's right you have an INNER JOIN. You need to change the choice so that you end up with a RIGHT JOIN or a LEFT JOIN. You do this by checking one of the other options in the join relationship.

 

[JonFer]

Please post the structure of the two tables and what the primary key (identifies a unique record) is for each table.

Based on your query and the fact that you verified the payments from TblMain are correct, it appears that Acct# appears twice in table TblSitePaymentInfo. You need to make the query conditions such that only one record from TblSitePaymentInfo is included (within each Location at least).