Help with query

[extrapulp]

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

Please Login or Register  to view this content.

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

[Ron Coderre]

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

Try this:

Please Login or Register  to view this content.

Does that help?

[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

[ConneXionLost]

How about this:

Please Login or Register  to view this content.

Cheers,

[Ron Coderre]

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:

Please Login or Register  to view this content.

[extrapulp]

Ron,

That works great!

Thank you!

Brian