Comparing 2 calculations in query 1

[eadiem]

I have a query which is performing 2 calculations. The first one is a 2-part calculation called AcresDiv5. In the first part, it sums all the crop acreage on a particular site, and in the second part this sum is divided by 5. The second calculation, called SumDevices, is the sum of all noisemaking devices on each site.

The goal is to find those farms at or exceeding the allowable noisemaking devices (1 device allowed per 5 acres of crop).

I want to add a "Where" or "Having" clause to find those records where SumDevices >= AcresDiv5.

Here is my SQL so far, looks very bulky but works fine, just can't get this final step. Any ideas??

Thanks in advance for any help.
Eadie



SELECT [Address LUT].ID, [Address LUT].Address, People.Name, People.Phone, Sum(Varieties.Acres) AS SumOfAcres, (Sum([Acres])/5) AS AcresDiv5, [Bird Control].[Triple Shots], [Bird Control].Cannons, [Bird Control].[AV Alarms], [Bird Control].[Orchard Pistols], (Nz([Bird Control].[Orchard Pistols],0))+(Nz([Bird Control].[Cannons],0))+(Nz([Bird Control].[Triple Shots],0))+(Nz([Bird Control].[AV Alarms],0)) AS SumDevices
FROM [Address LUT], Varieties, [Bird Control], People
WHERE ((([Address LUT].ID)=[Varieties].[ID]) AND (([Bird Control].ID)=[Address LUT].[ID]) AND ((People.GrowerID)=[Address LUT].[GrowerID]))
GROUP BY [Address LUT].ID, [Address LUT].Address, People.Name, People.Phone, [Bird Control].[Triple Shots], [Bird Control].Cannons, [Bird Control].[AV Alarms], [Bird Control].[Orchard Pistols], [Address LUT].GrowerID;

 

[PHV]

Have you tried this:
HAVING Sum([Acres]) <= 5 * (Nz([Bird Control].[Orchard Pistols],0))+(Nz([Bird Control].[Cannons],0))+(Nz([Bird Control].[Triple Shots],0))+(Nz([Bird Control].[AV Alarms],0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

This seems to select some records but not all of them. Will try to work with this some more.

 

[eadiem]

But for some reason when I reversed your Having clause it seems to select all matching:

HAVING (Nz([Bird Control].[Orchard Pistols],0))+(Nz([Bird Control].[Cannons],0))+(Nz([Bird Control].[Triple Shots],0))+(Nz([Bird Control].[AV Alarms],0)) >= Sum([Acres])/5 ;

Your clause selects 16 records, reverse selects 70. (??).

Thanks for your help!!