[wango]

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???

[TimCottee]

Lets address the second point first, you cannot use a calculated column in a where clause, you have to repeat the calculation itself:

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 ([SumOfInvoiceValue]/[Invoice])*100 < 40
GROUP BY Contracts.ContractNumber, Contracts.Invoice;

As to the first, you get these results because of the precision of the stored data, you can simplify this to return integer values or similar if this is more appropriate:

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

[wango]

Thanks a million Tim.