Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    19

    Unanswered: help debugging a append query

    Can anyone look at this, the query runs fine, as long as I keep the "Minimum Cost" value at 0, if I change the value (5,10, 500, 5000...) it returns no results?

    INSERT INTO Report_Data ( Commodities, [Delivery Date], Program, Province, [Cost of Goods], [Installation Cost], Miscellaneous, [Transportation Cost], [Tax Rate] )
    SELECT Commodities.Commodities, Invoices.[Delivery Date], Invoices.Program, Invoices.Province, Invoices.[Cost of Goods], Invoices.[Installation Cost], Invoices.Miscellaneous, Invoices.[Transportation Cost], (Select [Tax Rate] FROM tax WHERE Invoices.Province=tax.Region AND Invoices.[Delivery Date]>= tax.[Start Date] AND Invoices.[Delivery Date]<= tax.[End Date]) AS [Tax Rate]
    FROM (Commodities INNER JOIN [Program Information] ON Commodities.Commodities=[Program Information].CONTRACTDESCRIPTION) INNER JOIN Invoices ON [Program Information].[StandingOffer/Arrangement]=Invoices.Program
    WHERE (((Commodities.Commodities) Like ('*'+[PROGRAM_NAME]+'*')) And ((Invoices.[Delivery Date])>=[START_DATE] And (Invoices.[Delivery Date])<=[END_DATE]) And ((Invoices.Program) Like ('*'+[SUPPLIER]+'*')) And ((Invoices.[Cost of Goods]+Invoices.[Installation Cost]+Invoices.Miscellaneous+Invoices.[Transportation Cost])>=[Minimum Cost] And (Invoices.[Cost of Goods]+Invoices.[Installation Cost]+Invoices.Miscellaneous+Invoices.[Transportation Cost])<=[Maximum Cost]));

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Have you checked the WHERE clause's parentheses? It's hard to decipher, and partially because there are so many double parentheses. I think there should be a right paren after the mention of minimum cost in the clause, but it's hard to figure out where it should come from.

    Good luck,

    Sam

  3. #3
    Join Date
    Oct 2011
    Posts
    19
    Sorry for taking so long to reply. I cleaned it up and played with the format to make it easier to review, I found what's causing the error, but i can't figure out why or how to fix it.

    It's seams to mess up both the min and max cost:

    INSERT INTO Report_Data ( Commodities, [Delivery Date], Program, Province, [Cost of Goods], [Installation Cost], Miscellaneous, [Transportation Cost], [Tax Rate] )
    SELECT Commodities.Commodities, Invoices.[Delivery Date], Invoices.Program, Invoices.Province, Invoices.[Cost of Goods], Invoices.[Installation Cost], Invoices.Miscellaneous, Invoices.[Transportation Cost],
    (Select [Tax Rate] FROM tax WHERE Invoices.Province=tax.Region AND Invoices.[Delivery Date]>= tax.[Start Date] AND Invoices.[Delivery Date]<= tax.[End Date]) AS [Tax Rate]
    FROM (Commodities INNER JOIN [Program Information] ON Commodities.Commodities=[Program Information].CONTRACTDESCRIPTION) INNER JOIN Invoices ON [Program Information].[StandingOffer/Arrangement]=Invoices.Program

    WHERE (
    ((Commodities.Commodities) Like ('*'+[PROGRAM_NAME]+'*'))
    And ((Invoices.[Delivery Date])>=[START_DATE])
    And ((Invoices.[Delivery Date])<=[END_DATE])
    And ((Invoices.Program) Like ('*'+[SUPPLIER]+'*'))
    And ((Invoices.[Cost of Goods]+Invoices.[Installation Cost]+Invoices.Miscellaneous+Invoices.[Transportation Cost])>=[Minimum Cost])
    And ((Invoices.[Cost of Goods]+Invoices.[Installation Cost]+Invoices.Miscellaneous+Invoices.[Transportation Cost])<=[Maximum Cost])
    );

    From what I can determine it appears to be doing a greater then (value) for all the field separately instead of summing up the field and comparing that value.

    in other words it's doing
    if [Cost of Goods] is greater
    and if [Installation cost] is greater
    and if [Miscellaneous] is greater
    and if [Transportation Cost] is greater

    so as soon as one of the values doesn't meet the min value it rejects the entire record

    example: if minimum is set to "$5.00"


    [Cost of Goods] is $1000 (passes)
    [Installation cost] is $250 (passes)
    [Miscellaneous] is $3 (fails)
    [Transportation Cost] is $500 (passes)

    Because one of the value is bellow it rejects the record instead of doing

    [Cost of Goods] is $1000
    [Installation cost] is $250
    [Miscellaneous] is $3
    [Transportation Cost] is $500
    $1753.00 (passes)

    any clues?

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi, Anthony, sorry for taking so long.

    To start with, why don't you change all your "<=X and >=Y" tests to using the BETWEEN keyword, as in

    Invoices.[Delivery Date]>= tax.[Start Date] AND Invoices.[Delivery Date]<= tax.[End Date]) AS [Tax Rate]

    change to

    Invoices.[Delivery Date] BETWEEN (tax.[Start Date] AND tax.[End Date]).

    You have that form 2 other times in the SQL, in the WHERE clause, namely at delivery date and at maximum cost and minimum cost.

    This change will probably not solve the problem, but it will at least shorten the code by a few lines.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •