Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Unanswered: SQL problem. Heeeeeelp

    I created the query below in access 2000 and got:

    SELECT Contracts.ContractNumber, Contracts.Invoice, Sum(ContractInvoices.InvoiceValue) AS SumOfInvoiceValue,
    ([SumOfInvoiceValue]/[Invoice])*100 AS PercExpended
    FROM Contracts INNER JOIN ContractInvoices ON Contracts.ContractNumber = ContractInvoices.ContractNumber
    GROUP BY Contracts.ContractNumber, Contracts.Invoice;

    Breeds:

    ContractNumber Invoice SumOfInvoiceValue PercExpended
    11 added $1,100,000.00 1388666 126.242363636364
    5 $500,000.00 1033000 206.6
    6 $600,000.00 122500 20.4166666666667
    8 $800,000.00 3300 0.4125
    9 $900,000.00 364700 40.5222222222222


    (by the way, how can I avoid those crazy decimals?)

    I want to get only those rows where the %-age is less than 40.
    When I insert

    “where PercExpended < 40”

    to get

    SELECT Contracts.ContractNumber, Contracts.Invoice, Sum(ContractInvoices.InvoiceValue) AS SumOfInvoiceValue,
    ([SumOfInvoiceValue]/[Invoice])*100 AS PercExpended
    FROM Contracts INNER JOIN ContractInvoices ON Contracts.ContractNumber = ContractInvoices.ContractNumber where PercExpended < 40
    GROUP BY Contracts.ContractNumber, Contracts.Invoice;

    I pasted this back in Access query sql window, ran it and I got queried for PercExpended??

    Can someone help???

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    column aliases are often problematic

    you did not report an error on using SumOfInvoiceValue in

    ([SumOfInvoiceValue]/[Invoice])*100 AS PercExpended

    so sometimes it works and sometimes it don't

    i would suggest not using aliases in expressions -- it may take a bit of cut & paste but it always works

    Code:
    select Contracts.ContractNumber
         , Contracts.Invoice
         , Sum(ContractInvoices.InvoiceValue)
                as SumOfInvoiceValue 
         , (Sum(ContractInvoices.InvoiceValue)/Invoice)*100 
                as PercExpended 
      from Contracts 
    inner 
      join ContractInvoices 
        on Contracts.ContractNumber 
         = ContractInvoices.ContractNumber
     where (Sum(ContractInvoices.InvoiceValue)/Invoice)*100
              < 40 
    group
        by Contracts.ContractNumber
         , Contracts.Invoice
    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Thanks a lot for your help. It worked.

Posting Permissions

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