Weird SQL Query?????
Hello everyone,
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?
ASP Query:
"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
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?
ASP Query:
"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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If I drill down the query some more I am noticing that the web query will show what looks like a duplicate value but MS Query will only show one record. Is there a way to mark a record as a negative without actually showing a minus number? The webpage will show to records at $15.25 and MS Query will only show one. There is a unique field called SEQ_NO and that shows a different value to the website but in MS Query that whole record doesn't appear at all?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sure thing,
MS Query
SELECT ACTHIST.SEQ_NO, ACTHIST.ORDER_NO, ACTHIST.SREP_CODE, ACTHIST.CELL_NUM, ACTHIST.DIRCODE, ACTHIST.ACT_DT, ACTHIST.DEACT_DT, ACTHISTS.SEQ_NO, ACTHISTS.SREP_AMT
FROM `Z:`\ACTHIST.DBF ACTHIST, `Z:`\ACTHISTS.DBF ACTHISTS
WHERE ACTHIST.SEQ_NO = ACTHISTS.SEQ_NO AND ((ACTHIST.ACT_DT Between ? And ?) AND (ACTHIST.SREP_CODE='AUM'))
ASP Page:
Dim yes_date
yes_date = Date() - 1
'response.Write(yes_date & "<br/>")
SQL_ACT = "SELECT ACTHIST.SEQ_NO, ACTHIST.ORDER_NO, ACTHIST.SREP_CODE, ACTHIST.CELL_NUM, ACTHIST.DIRCODE, ACTHIST.ACT_DT, ACTHIST.DEACT_DT, ACTHISTS.SEQ_NO, ACTHISTS.SREP_AMT FROM ACTHIST, ACTHISTS WHERE ACTHIST.SEQ_NO = ACTHISTS.SEQ_NO AND ((ACTHIST.ACT_DT Between #" & yes_date & "# And #" & yes_date & "#) AND (ACTHIST.SREP_CODE='AUM'))
MS Query
SELECT ACTHIST.SEQ_NO, ACTHIST.ORDER_NO, ACTHIST.SREP_CODE, ACTHIST.CELL_NUM, ACTHIST.DIRCODE, ACTHIST.ACT_DT, ACTHIST.DEACT_DT, ACTHISTS.SEQ_NO, ACTHISTS.SREP_AMT
FROM `Z:`\ACTHIST.DBF ACTHIST, `Z:`\ACTHISTS.DBF ACTHISTS
WHERE ACTHIST.SEQ_NO = ACTHISTS.SEQ_NO AND ((ACTHIST.ACT_DT Between ? And ?) AND (ACTHIST.SREP_CODE='AUM'))
ASP Page:
Dim yes_date
yes_date = Date() - 1
'response.Write(yes_date & "<br/>")
SQL_ACT = "SELECT ACTHIST.SEQ_NO, ACTHIST.ORDER_NO, ACTHIST.SREP_CODE, ACTHIST.CELL_NUM, ACTHIST.DIRCODE, ACTHIST.ACT_DT, ACTHIST.DEACT_DT, ACTHISTS.SEQ_NO, ACTHISTS.SREP_AMT FROM ACTHIST, ACTHISTS WHERE ACTHIST.SEQ_NO = ACTHISTS.SEQ_NO AND ((ACTHIST.ACT_DT Between #" & yes_date & "# And #" & yes_date & "#) AND (ACTHIST.SREP_CODE='AUM'))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER