View Full Version : Solved: Group numbers together
austenr
05-22-2010, 02:51 PM
Can anyone give an example of how to group together positive and negative amounts in a currency field when writing a query?
Example, $1000.00, -$1000.00, $1000.00 would all be grouped together. I know that you have to do something with sorting and or grouping but I am not sure what?
Any help appreciated in advance. Thanks
To group both positive and negative values create a new Column Heading like this
Value: Abs([currency field name])
where currency field name is the actual name of your field, that will remove the + and - signs.
Now click on Totals to add the grouping, you have to be careful what fields you include in the query as they will all be grouped.
austenr
05-23-2010, 08:17 AM
Tony,
I got the query but it doesn't work:
SELECT ApTest.[GrossAmt], ApTest.[VenNum]
FROM ApTest
WHERE (((ApTest.[GrossAmt]) In (SELECT [GrossAmt] FROM [ApTest] As Tmp GROUP BY Abs ([GrossAmt]) HAVING Count(*)>1 )))
ORDER BY ApTest.[GrossAmt];
Any ideas/
Austen, if you just have the Tmp and not the GrossAmt in the select query what do you get?
austenr
05-23-2010, 08:34 AM
I get the expected results. When adding the Abs it throws an error saying something about [GrossAmt] not being part of the aggregate.
Then I would make the first query with the Abs in it a normal select query and create a second query that has the totals in it.
I have tested that OK.
austenr
05-23-2010, 08:55 AM
Can you post what you got to work?
Damned Access can be a pain sometimes, I imported the table & queries in to a Blank database and it comes up with Unidentified abs Function.
So I am posting the original database. See the 2 Order Line Queries.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.