I am using a this query to get the sum of monthly statements on a website:
SELECT DISTINCT SUM(purch_amt) AS p_purchases, SUM(sales_amt) AS p_sales, SUM(fees) AS p_fees, SUM(fees_paid) AS p_fees_paid FROM mtd_trans WHERE acct_holder='"&p_username&"' AND trans_date BETWEEN '"&begDate&"' AND '"&endDate&"'
The query is returning results, but it is doubling the sums. Can anyone tell me what might be going on? Elsewhere on this same page, I am using another query to return all of the transactions for the month. These are displaying fine. Here is the query I am using for that:
SELECT DISTINCT mtd_trans.auth_num, mtd_trans.acct_holder, mtd_trans.trans_date, mtd_trans.trans_with, mtd_trans.purch_amt, mtd_trans.sales_amt, mtd_trans.fees, mtd_trans.fees_paid, mtd_trans.card_num, mtd_trans.descr, members.company FROM mtd_trans INNER JOIN members ON mtd_trans.trans_with=members.username WHERE acct_holder='"&p_username&"' AND trans_date BETWEEN '"&begDate&"' AND '"&endDate&"' ORDER BY trans_date
It is the sums from the first query that are incorrect. The recordset from the second query return all the correct transactions. However, when the transactions are added manually on a calculator, you can easily see that the sums returned from the first query are incorrect.
Does anyone know why this might be happening? Or, has anyone seen anything similar and have a suggestion for a work around?