Thread: Help with query

I have very little knowledge of access, but knowledge of how databases in general function. I inherited this database and did not create it.

I am using Access 2007.

I have a report that is driven by a query. It is returning records that the sum is 0. I would like to not display the records where the sum is 0.
I tried modifying the query in design view but I can't seem to get it to work. So I switched to the SQL view and have been trying to get that working. I can get access to accept the SQL but when I run it it asks me for the value that I put >0 in the query.

Here is the SQL Query

Code:

SELECT LotNumber.LotNumber, MetalSizes.MetalSize, MetalSizes.Plating, MetalSizes.Thickness, Sum(LotAdjustment.Adjustment) AS SumOfAdjustment, MetalSizes.MetalID
FROM (MetalSizes INNER JOIN LotNumber ON MetalSizes.MetalID = LotNumber.MetalID) INNER JOIN LotAdjustment ON LotNumber.LotNumber = LotAdjustment.LotNumber
WHERE ((([SumOfAdjustment])>0))
GROUP BY LotNumber.LotNumber, MetalSizes.MetalSize, MetalSizes.Plating, MetalSizes.Thickness, MetalSizes.MetalID
ORDER BY LotNumber.LotNumber;

With the code above it prompts me for the value of SumOfAdjustment. I am unsure of what I am doing wrong.

In the design view I was able to get access to add a HAVING line in SQL. This did not ask for the value but it did not generate the correct results either.

The end result would be showing just the records where SumOfAdjustment >0.



Thanks

Brian

In the query SQL, you can't reference field aliases in the FROM, WHERE, GROUP BY, HAVING, or ORDER BY sections.

Try this:

Code:

SELECT 
  LotNumber.LotNumber, 
  MetalSizes.MetalSize, 
  MetalSizes.Plating, 
  MetalSizes.Thickness, 
  Sum(LotAdjustment.Adjustment) AS SumOfAdjustment, 
  MetalSizes.MetalID
FROM 
  (MetalSizes INNER JOIN LotNumber ON MetalSizes.MetalID = LotNumber.MetalID) 
  INNER JOIN LotAdjustment ON LotNumber.LotNumber = LotAdjustment.LotNumber
WHERE (Sum(LotAdjustment.Adjustment)>0)
GROUP BY 
  LotNumber.LotNumber, 
  MetalSizes.MetalSize, 
  MetalSizes.Plating, 
  MetalSizes.Thickness, 
  MetalSizes.MetalID
ORDER BY LotNumber.LotNumber;

Does that help?

How about this:

Code:

SELECT LotNumber, MetalSize, Plating, Thickness, SumOfAdjustment, MetalID
FROM (SELECT LotNumber.LotNumber, MetalSizes.MetalSize, MetalSizes.Plating, MetalSizes.Thickness, Sum(LotAdjustment.Adjustment) AS SumOfAdjustment, MetalSizes.MetalID
FROM (MetalSizes INNER JOIN LotNumber ON MetalSizes.MetalID = LotNumber.MetalID) INNER JOIN LotAdjustment ON LotNumber.LotNumber = LotAdjustment.LotNumber;)
WHERE ([SumOfAdjustment])>0
GROUP BY LotNumber, MetalSize, Plating, Thickness, MetalID
ORDER BY LotNumber;

Cheers,

Originally Posted by extrapulp

Ron,

Access now throws an error dialog with this text in it "Cannot have aggregate function in WHERE clause (Sum(LotAdjustment.Adjustment)>0)."

Thanks for your quick reply!

Brian

Try moving the criteria into a HAVING clause:

Code:

SELECT 
  LotNumber.LotNumber, 
  MetalSizes.MetalSize, 
  MetalSizes.Plating, 
  MetalSizes.Thickness, 
  Sum(LotAdjustment.Adjustment) AS SumOfAdjustment, 
  MetalSizes.MetalID
FROM 
  (MetalSizes INNER JOIN LotNumber ON MetalSizes.MetalID = LotNumber.MetalID) 
  INNER JOIN LotAdjustment ON LotNumber.LotNumber = LotAdjustment.LotNumber
GROUP BY 
  LotNumber.LotNumber, 
  MetalSizes.MetalSize, 
  MetalSizes.Plating, 
  MetalSizes.Thickness, 
  MetalSizes.MetalID
HAVING 
  (Sum(LotAdjustment.Adjustment)>0)
ORDER BY 
  LotNumber.LotNumber;