Solved: Group numbers together

[austenr]

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

[OBP]

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]

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/

[OBP]

Austen, if you just have the Tmp and not the GrossAmt in the select query what do you get?

[austenr]

I get the expected results. When adding the Abs it throws an error saying something about [GrossAmt] not being part of the aggregate.

[OBP]

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]

Can you post what you got to work?

[OBP]

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.