Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Weird SQL Query?????

Posted on 2009-02-13
7
Medium Priority
?
257 Views
Last Modified: 2015-01-05
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
0
Comment
Question by:awinstead
  • 3
  • 3
6 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 23640515
I don't see how MS Query is returning the correct value - you're SUMMING the SREP_AMT field, which should take into account BOTH your Sale and REturned items, if they're stored in that column (and if they're returned by your WHERE filter). Are returned values stored as a negative value? Or are both stored as a Positive value?
0
 

Author Comment

by:awinstead
ID: 23651640
Far as I know both values are positive values. This has been a weird problem for us as of lately MS Query for some strange reason won't see certain things. Sometimes we will run a Query and it will not show a couple records but we know they are there. Is there any problems between DBase and MSQuery? I was under the impression that DBase that I could use standard SQL Queries with a DBase database? Am I correct in that assumption?
0
 

Author Comment

by:awinstead
ID: 23652550
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 23653476
Unless you've got a calculated column, and you're using the Abs() function, your query should return whatever value is in the field.

The ONLY thing could be the Date filter, at least as far as I can see. Can you printout the actual SQL statement that's being passed to the database from your ASP page? And the one being used by MS Query?
0
 

Author Comment

by:awinstead
ID: 23653600
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'))"
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 25214834
Delete Refund.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question