Can't get Top Values to sort a formula

[JimAA]

I have a query that is based on two tables. Table 1 (JOB) has Job Contract
data in it. Table 2 (Building Data) has Job Size data in it. Table 1 can
have duplicate Job Names but each has a unique Job Number. Table 2 (Building
Data) can have multiple Job Sizes and consequently multiple Job Numbers. I
want the top 100 values (Areas) grouped by Job Name. The query below groups
by Job Name and sums the areas - my problem is that the Top 100 selects the
first 100 Job Names in alphabetical order (only Jobs beginning with A), not
the Top 100 Total areas. Any suggestions??

SELECT TOP 100 Sum([Length]*[Width]) AS AREA, JOB.[Job Name]
FROM JOB RIGHT JOIN [BUILDING DATA] ON JOB.[Job Number] = [BUILDING
DATA].[Job Number]
GROUP BY JOB.[Job Name]
HAVING (((Sum([Length]*[Width])) Is Not Null And (Sum([Length]*[Width]))>0));

 

[BobT]

You just need an order by:

SELECT Sum([Length]*[Width]) AS AREA, JOB.[Job Name]
FROM JOB RIGHT JOIN [BUILDING DATA] ON JOB.[Job Number] = [BUILDING
DATA].[Job Number]
GROUP BY JOB.[Job Name]
HAVING (((Sum([Length]*[Width])) Is Not Null And (Sum([Length]*[Width]))>0))
ORDER BY AREA DESC;

 

[John Spencer]

One small change that M I G H T be needed is to replace AREA with the
actual formula.

SELECT Sum([Length]*[Width]) AS AREA, JOB.[Job Name]
FROM JOB RIGHT JOIN [BUILDING DATA] ON JOB.[Job Number] = [BUILDING
DATA].[Job Number]
GROUP BY JOB.[Job Name]
HAVING (((Sum([Length]*[Width])) Is Not Null And (Sum([Length]*[Width]))>0))
ORDER BY Sum(Length * Width) DESC;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

You just need an order by:

SELECT Sum([Length]*[Width]) AS AREA, JOB.[Job Name]
FROM JOB RIGHT JOIN [BUILDING DATA] ON JOB.[Job Number] = [BUILDING
DATA].[Job Number]
GROUP BY JOB.[Job Name]
HAVING (((Sum([Length]*[Width])) Is Not Null And (Sum([Length]*[Width]))>0))
ORDER BY AREA DESC;

I have a query that is based on two tables. Table 1 (JOB) has Job Contract
data in it. Table 2 (Building Data) has Job Size data in it. Table 1 can
have duplicate Job Names but each has a unique Job Number. Table 2 (Building
Data) can have multiple Job Sizes and consequently multiple Job Numbers. I
want the top 100 values (Areas) grouped by Job Name. The query below groups
by Job Name and sums the areas - my problem is that the Top 100 selects the
first 100 Job Names in alphabetical order (only Jobs beginning with A), not
the Top 100 Total areas. Any suggestions??

SELECT TOP 100 Sum([Length]*[Width]) AS AREA, JOB.[Job Name]
FROM JOB RIGHT JOIN [BUILDING DATA] ON JOB.[Job Number] = [BUILDING
DATA].[Job Number]
GROUP BY JOB.[Job Name]
HAVING (((Sum([Length]*[Width])) Is Not Null And (Sum([Length]*[Width]))>0));