Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2008
    Posts
    15

    Unanswered: db query expression

    Hi,
    I wonder if anyone can help.
    I have a query which lists invoices against different order numbers as shown below:
    orderNo, Order value invoice amount
    1 1000 200
    1 1000 200
    1 1000 500
    2 1500 100
    2 1500 1100
    3 500 500

    I have created a sum query to total the invoice amounts per order & added a commitment column to calculate the difference between order value & total invoice amount – i.e. the following:
    orderNo Order value SumOfinvoice amount Commitment
    1 1000 900 100
    2 1500 1400 100
    3 500 500 0

    I now want o remove the lines with the commitment value = 0. So in the criteria part of the query design window for the ‘Commitment’ column I put
    ‘ > 0’. This does not work!
    It comes up with a window & asks me to enter a value for the ‘SumOfinvoiceamount’.

    Any ideas?

    Regards,

    Samy5

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please could you post the SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2008
    Posts
    15

    sql for db query expression

    SELECT [T-Purchase Order Details 06/07].Supplier, [T-Purchase Order Details 06/07].[IS Unique Ref No], [T-Purchase Order Details 06/07].[PO Number], [T-Purchase Order Details 06/07].[Req Value Gross], Sum([T-Invoice Details 06/07].[Invoice Value Gross]) AS [SumOfInvoice Value Gross], Max([T-Invoice Details 06/07].[Invoice Date]) AS [MaxOfInvoice Date], [Req Value Gross]-[SumOfInvoice Value Gross] AS Commitment
    FROM [T-Purchase Order Details 06/07] INNER JOIN [T-Invoice Details 06/07] ON [T-Purchase Order Details 06/07].[IS Unique Ref No] = [T-Invoice Details 06/07].[IS Unique Ref No]
    GROUP BY [T-Purchase Order Details 06/07].Supplier, [T-Purchase Order Details 06/07].[IS Unique Ref No], [T-Purchase Order Details 06/07].[PO Number], [T-Purchase Order Details 06/07].[Req Value Gross], [T-Purchase Order Details 06/07].[Description 2]
    HAVING (((NZ([Description 2],""))=""));


    Thanks for your help.

    Samy5

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Couple of changes:

    Code:
    SELECT [T-Purchase Order Details 06/07].Supplier, [T-Purchase Order Details 06/07].[IS Unique Ref No], [T-Purchase Order Details 06/07].[PO Number], [T-Purchase Order Details 06/07].[Req Value Gross], Sum([T-Invoice Details 06/07].[Invoice Value Gross]) AS [SumOfInvoice Value Gross], Max([T-Invoice Details 06/07].[Invoice Date]) AS [MaxOfInvoice Date], [Req Value Gross]-[SumOfInvoice Value Gross] AS Commitment
    FROM [T-Purchase Order Details 06/07] INNER JOIN [T-Invoice Details 06/07] ON [T-Purchase Order Details 06/07].[IS Unique Ref No] = [T-Invoice Details 06/07].[IS Unique Ref No]
    WHERE [Description 2] = "" OR [Description 2]  IS NULL
    GROUP BY [T-Purchase Order Details 06/07].Supplier, [T-Purchase Order Details 06/07].[IS Unique Ref No], [T-Purchase Order Details 06/07].[PO Number], [T-Purchase Order Details 06/07].[Req Value Gross], [T-Purchase Order Details 06/07].[Description 2]
    HAVING [Req Value Gross]-[SumOfInvoice Value Gross] > 0
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Try a LEFT JOIN in place of a INNER JOIN...
    learn how to use the power and flexibility you get form selecting the correct Join syntax

    mind you I'd also want to learn the reasons not to use spaces in table & column names!

    you could also benefit from table aliases, although that's just a style issue, it can make the SQL easier to read

    Code:
    SELECT PO.Supplier, PO.[IS Unique Ref No], PO.[PO Number], PO.[Req Value Gross], Sum([Inv.[Invoice Value Gross]) AS [SumOfInvoice Value Gross], Max(INV.[Invoice Date]) AS [MaxOfInvoice Date], [Req Value Gross]-[SumOfInvoice Value Gross] AS Commitment
    FROM [T-Purchase Order Details 06/07] as PO LEFT JOIN [T-Invoice Details 06/07] as INV ON PO.[IS Unique Ref No] = INV.[IS Unique Ref No]
    GROUP BY PO.Supplier, PO.[IS Unique Ref No], PO.[PO Number], PO.[Req Value Gross], PO.[Description 2]
    HAVING (((NZ([Description 2],""))=""));
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2008
    Posts
    15

    db query expression

    Thanks for both your help. I inherited this query & did not design it myself. I changed the SQL by adding the line:
    HAVING [Req Value Gross]-[SumOfInvoice Value Gross] > 0
    at the end.
    When I try to run it asks for a value to be entered for 'SumOfInvoice Value Gross'!
    My SQL currently looks like the following:

    SELECT [T-Purchase Order Details 06/07].Supplier, [T-Purchase Order Details 06/07].[IS Unique Ref No], [T-Purchase Order Details 06/07].[PO Number], [T-Purchase Order Details 06/07].[Req Value Gross], Sum([T-Invoice Details 06/07].[Invoice Value Gross]) AS [SumOfInvoice Value Gross], Max([T-Invoice Details 06/07].[Invoice Date]) AS [MaxOfInvoice Date], [Req Value Gross]-[SumOfInvoice Value Gross] AS Commitment, [T-Purchase Order Details 06/07].[Description 2]
    FROM [T-Purchase Order Details 06/07] INNER JOIN [T-Invoice Details 06/07] ON [T-Purchase Order Details 06/07].[IS Unique Ref No] = [T-Invoice Details 06/07].[IS Unique Ref No]
    WHERE ((([T-Purchase Order Details 06/07].[Description 2])="" Or ([T-Purchase Order Details 06/07].[Description 2]) Is Null))
    GROUP BY [T-Purchase Order Details 06/07].Supplier, [T-Purchase Order Details 06/07].[IS Unique Ref No], [T-Purchase Order Details 06/07].[PO Number], [T-Purchase Order Details 06/07].[Req Value Gross], [T-Purchase Order Details 06/07].[Description 2] HAVING [Req Value Gross]-[SumOfInvoice Value Gross] > 0 ;

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Samy5
    I changed the SQL by adding the line:
    HAVING [Req Value Gross]-[SumOfInvoice Value Gross] > 0
    at the end.
    When I try to run it asks for a value to be entered for 'SumOfInvoice Value Gross'!
    Sorry - I'm trying to get off caffeine this week. It isn't working out

    Code:
    SELECT [T-Purchase Order Details 06/07].Supplier, [T-Purchase Order Details 06/07].[IS Unique Ref No], [T-Purchase Order Details 06/07].[PO Number], [T-Purchase Order Details 06/07].[Req Value Gross], Sum([T-Invoice Details 06/07].[Invoice Value Gross]) AS [SumOfInvoice Value Gross], Max([T-Invoice Details 06/07].[Invoice Date]) AS [MaxOfInvoice Date], [Req Value Gross]-[SumOfInvoice Value Gross] AS Commitment, [T-Purchase Order Details 06/07].[Description 2]
    FROM [T-Purchase Order Details 06/07] INNER JOIN [T-Invoice Details 06/07] ON [T-Purchase Order Details 06/07].[IS Unique Ref No] = [T-Invoice Details 06/07].[IS Unique Ref No]
    WHERE ((([T-Purchase Order Details 06/07].[Description 2])="" Or ([T-Purchase Order Details 06/07].[Description 2]) Is Null))
    GROUP BY [T-Purchase Order Details 06/07].Supplier, [T-Purchase Order Details 06/07].[IS Unique Ref No], [T-Purchase Order Details 06/07].[PO Number], [T-Purchase Order Details 06/07].[Req Value Gross], [T-Purchase Order Details 06/07].[Description 2] 
    HAVING [Req Value Gross]-Sum([T-Invoice Details 06/07].[Invoice Value Gross]) > 0
    I'm also nervous about this:
    Code:
    [T-Purchase Order Details 06/07]
    Are you planning one new table each year?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2008
    Posts
    15

    db query expression

    the databse is already set up with a separate tables for each year. I will join the tables using a 'union' query to create one table in future & then run my query just on the new table. For now I just need to get it working on this table.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - but for future ref it is a design flaw and adds an unnecessary maintainenance overhead. Probably worth rethinking if ever you need to do similar again.

    Has the query worked ok?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2008
    Posts
    15
    No it hasn't. It asks for a value to be entered for 'SumOfInvoice Value Gross'!
    Please see the modified SQL (above).

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yup - and I re-modified it after then (post #7). Does my SQL work ok?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2008
    Posts
    15

    db query expression

    Sorry, I cannot test the query at the moment as the network is down & I have no access to the database. I will test it tomorrow & send a reply.

    Thanks for all your help.

    Samy5

  13. #13
    Join Date
    Jan 2008
    Posts
    15

    db query expression

    Your sql does work.

    Thanks very much for your help.

Posting Permissions

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