Debugging Queries

[ncopeland]

Hi

Is there a way to debug/step through a query to see what it is doing. I am using Access XP.

KR

NC

 

[lespaul]

depends on how you are running the query, what kind of problem you are having.

If you are writing a dynamic query in VBA and aren't sure what the SQL string contains, you can display that.

If you are running a static query and you aren't getting the results you expect, what I usually do is start with the basics of the query, make sure I get the results I expect, add in something (a join, a where clause..) and verify I'm still getting the correct results and keep adding sections of the query until I figure out which portion is making the results incorrect and then look at that data and figure out why it's messing up the query.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ncopeland]

The query involved was written by somebody else. It is an append query. I have rewritten the query to see if I can find out where the problem lies. I rewrote it as a test as I do not want it append data to the tables. There is four groupings in the query but the calculation part of the query seems to double up quantities if the product is displayed more than once. A product is displayed more than once if the package count or price of each package has changed during the week.

Query as displayed

SELECT DISTINCTROW [Forms]![FrmEDITransfer]![LstInvoiceNo] AS INVNO, Abs([tblprod]![Product Code]) AS UPC, Products.[Product Description], tblProd.[Cost Price], tblProd.[Units per Outer], Sum(IIf([invoice date]=DateAdd("d",1,[Forms]![FrmEDITransfer]![WeekBeginning]),[order qty],0)) AS Sun, Sum(IIf([invoice date]=DateAdd("d",2,[Forms]![FrmEDITransfer]![WeekBeginning]),[order qty],0)) AS Mon, Sum(IIf([invoice date]=DateAdd("d",3,[Forms]![FrmEDITransfer]![WeekBeginning]),[order qty],0)) AS Tue, Sum(IIf([invoice date]=DateAdd("d",4,[Forms]![FrmEDITransfer]![WeekBeginning]),[order qty],0)) AS Wed, Sum(IIf([invoice date]=DateAdd("d",5,[Forms]![FrmEDITransfer]![WeekBeginning]),[order qty],0)) AS Thu, Sum(IIf([invoice date]=DateAdd("d",6,[Forms]![FrmEDITransfer]![WeekBeginning]),[order qty],0)) AS Fri, Sum(IIf([invoice date]=DateAdd("d",7,[Forms]![FrmEDITransfer]![WeekBeginning]),[order qty],0)) AS Sat
FROM (tblMSArea INNER JOIN (Products INNER JOIN tblProd ON Products.[Product Code] = tblProd.[Product Code]) ON tblMSArea.Client = tblProd.Client) INNER JOIN qselMSInvDeptI ON tblProd.[Product Code] = qselMSInvDeptI.[Product Code]
WHERE (((tblProd.[Invoice Date])>=DateAdd("d",1,[Forms]![FrmEDITransfer]![WeekBeginning])))
GROUP BY [Forms]![FrmEDITransfer]![LstInvoiceNo], Abs([tblprod]![Product Code]), Products.[Product Description], tblProd.[Cost Price], tblProd.[Units per Outer], tblMSArea.StrArea
HAVING (((tblMSArea.StrArea)=[Forms]![FrmEDITransfer]![Area]));

It calculates what has been dispatched for each day in the week Sunday through to Saturday. If the product code has had its package count or unit price changed during the week then it doubles up the quantity for each day.

KR

NC.

 

[lespaul]

and what is the problem you're having with the query?
Did it suddenly stop working?
Does it not insert the correct information?
Is this the original query or the one you're trying to correct?

I would expect an INSERT INTO clause for an append query....

Additionally, Access is notorious for using the HAVING clause incorrectly, which it has in this case...you can move the tblMSArea.StrArea = [Forms]![FrmEDITransfer]![Area] to the WHERE clause without causing additional problems.

Leslie

Leslie

 

[ncopeland]

Hi Leslie

This is the revised query I took the insert out as I did not want it to update the tables.

Problem is if the table has 2 entries for the same product ie 123456 outers 10 cost 12.00 Sun = 0 Mon = 1 Tues = 2 Wed Thurs Fri Sat = 0. If I have another row 123456 outers 6 cost 10.00 Sun = 0 Mon = 0 Tues = 0 Wed = 0 Thurs = 1 Fri = 0 Sat = 0. I get two runs. fisrt row has mon= 2 Tues = 2. The second row has Thurs = 2. It seems to double up quantities per day if a product has 2 rows in the same week.

KR

NC.

 

[lespaul]

what do you want the query to report when it finds two?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ncopeland]

Hi Leslie

It should produce a line for each product even if it has changed during the week ie package count or price. But what it shouldn't do is double up the daily quanities.

ie on previous example Mon should equal 1 not 2 and Tuesday should equal 2 not 4(mistake with previous post tuesday should have said 4 not 2). on the first product row. The second row that relates to the product should have Thursday equal to = 1 not 2.

Hope this gives a flavour

KR

NC