Weird SQL Query?????
Posted on 2009-02-13
I have a dbase database that I am quering. I use MS Query to get the query to run as I want. Then to save me some time I copy and paste the SQL code into my asp page. I remove a couple things that are not neccessary like the path to the database etc.. Anyways when I run the query in MS Query it works then when I use it in the page it works, well kind of. We have a column called ACTHISTS.SREP_AMT that shows a GP and the SQL query queries the table for the previous day and the does a sum on the ACTHISTS.SREP_AMT column. But the values I get on the webpage do not match MS Query. After doing some investigation I found that the database may have a value like $9.99 GP for a single sale and then if a customer returned the item it shows again for $9.99 GP so it come up twice and the web adds that into the Sum but MS Query doesn't? I know that the returned record will have a date listed in DEACT_DT field so I tried to exclude any records from the Query that Have a value listed in the DEACT_DT field but I am still having some problems. Can someone help?
"SELECT Sum(ACTHISTS.SREP_AMT) AS SREP_AMT, ACTHIST.SREP_CODE FROM ACTHIST, ACTHISTS WHERE ACTHIST.SEQ_NO = ACTHISTS.SEQ_NO AND ((ACTHIST.ACT_DT Between #" & yes_date & "# And #" & yes_date & "#)) GROUP BY ACTHIST.SREP_CODE, ACTHIST.DEACT_DT HAVING (ACTHIST.DEACT_DT Is Null) ORDER BY Sum(ACTHISTS.SREP_AMT) DESC"
MS Query Code:
SELECT Sum(ACTHISTS.SREP_AMT) AS 'Sum of SREP_AMT', ACTHIST.SREP_CODE
FROM `Z:`\ACTHIST.DBF ACTHIST, `Z:`\ACTHISTS.DBF ACTHISTS
WHERE ACTHIST.SEQ_NO = ACTHISTS.SEQ_NO AND ((ACTHIST.ACT_DT Between ? And ?))
GROUP BY ACTHIST.SREP_CODE, ACTHIST.DEACT_DT
HAVING (ACTHIST.DEACT_DT Is Null)
ORDER BY Sum(ACTHISTS.SREP_AMT) DESC