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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Total query displaying double the amount
- Thread starter Buddha1
- Start date
To become one with everything, please post the sql of your query here, otherwise, we'll have no idea what's going on.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
- Thread starter
- #3
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]));
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]));
Hmm. Do you use the credit card number as the account number? If so, I would _strongly_ suggest you change that. Access is no where near secure enough to store your customers' credit card numbers.
It looks like you're getting multiples because you're displaying (and grouping by) too many fields. I would take out the the date field. If you're seeing duplicate totals but different data in the other fields, you're probably just including too many fields.
But I'm very serious about the first bit. Storing credit card numbers temporarily, while a transaction is executed, is one thing. But I would _NOT_ store ccNumbers on a regular basis in an Access database--way too much liability involved.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
It looks like you're getting multiples because you're displaying (and grouping by) too many fields. I would take out the the date field. If you're seeing duplicate totals but different data in the other fields, you're probably just including too many fields.
But I'm very serious about the first bit. Storing credit card numbers temporarily, while a transaction is executed, is one thing. But I would _NOT_ store ccNumbers on a regular basis in an Access database--way too much liability involved.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
- Status
Similar threads
