Report Ignoring negative numbers

[Jen]

I have two columns. One is Company name and the other is positive and
negative numbers. I need to group by company name. I also need to group by
the pos/neg numbers (ignoring the neg sign) and excluding orphans.

I have tried using the ABS, but it changes the neg to a positive. How can I
get it to ignore, but still display the neg number.

i.e. $134.00
-$134.00
$134.00

$122.14
$122.14
-$122.14

If I had only one $155.00 or -$164.00, etc. I do not want it to show up on
my report.

 

[KARL DEWEY]

Display your number field as is but group on a copy of the field with the ABS.

 

[Jen]

I dropped another Amt field into the query and used the formula below.
ABS: Format(Abs([Amt]),"Fixed")

I then added both the 'Amt' and the abs'Amt' fields to the report and
grouped by the abs field, but made it not visible.

I used this one to get rid of any orphans (without dups) - the standard
script from the dup query.
In (SELECT [Amt] FROM [ApTest] As Tmp GROUP BY [Amt] HAVING Count(*)>1 )

Thanks.

 

[John Spencer]

First base your report on a query that screens out companies with only one record.

SELECT [Company Name]
, [Numbers]
, Abs([Numbers]) as SpecialSort
FROM [Some Table]
WHERE Abs([Numbers] in
(SELECT Abs([Numbers])
FROM [Some Table] as Temp
WHERE Abs(Temp.[Numbers]) = [Some Table].Numbers
AND Temp.[Company Name] = [Some Table].[Company Name]
GROUP BY Abs([Numbers])
HAVING Count(*) > 1)

Perhaps a simpler and faster process would be to use two queries. First query
would identify the records to use. Save this query a qFindRecords
SELECT [Company Name], Abs(Numbers) as NumValue
FROM [Some Table]
GROUP BY [Company Name], Abs(Numbers)
HAVING Count([Company Name])>1

Now using that saved query, you can use a query like the following.
SELECT [Some Table].[Company Name]
, [Some Table].[Numbers]
, Abs([Some Table].[Numbers]) as SpecialSort
FROM [Some Table] INNER JOIN qFindRecords
ON [Some Table].[Company Name] = qFindRecords.[Company Name]
AND (Abs([SomeTable].[Numbers]) = qFindRecords.NumValue

If you need instructions on how to build the two queries in design view, post
back and request help.




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

 

[John Spencer]

Small problem, I think you want to include records if you had two records for
a company with amt field values of
-134 and 134

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

I dropped another Amt field into the query and used the formula below.
ABS: Format(Abs([Amt]),"Fixed")

I then added both the 'Amt' and the abs'Amt' fields to the report and
grouped by the abs field, but made it not visible.

I used this one to get rid of any orphans (without dups) - the standard
script from the dup query.
In (SELECT [Amt] FROM [ApTest] As Tmp GROUP BY [Amt] HAVING Count(*)>1 )

Thanks.

Display your number field as is but group on a copy of the field with the ABS.

 

[Jen]

I have two tables of data to combine, one table having only two columns of
data, ID and Company Name. (I should have mentioned this.) The other having
ID and Amount. I created two queries - one a make table. I didn't want to
have to create a new table of data, but when I tried to combine both fields
of 'numbers' - 'Amount' and 'absAmount' using one query or even based on a
first query, I couldn't get it to work. But when I ran a make table creating
the extra column of 'absAmount' and then running another query for the
exclusion of orphans using the below queries, I was able to get it to work.

In this query I joined both tables and then created the additional column
for absAmount calling it 'Gross', this was my make table query. I added all
of the other fields in this query too.
Gross: Format(Abs([GrossAmt]),"Currency")

The following query was based on the Make Table and took out any single
instances of 'Gross'
In (SELECT [Gross] FROM [MTAbsAmt] As Tmp GROUP BY [Gross] HAVING Count(*)>1 )

I did the sorting and grouping within the report itself.

Can you tell me if there is a way to join two tables together, create an
'absAmt' column, and only collect dups without creating a make table.

Thank you.

First base your report on a query that screens out companies with only one record.

SELECT [Company Name]
, [Numbers]
, Abs([Numbers]) as SpecialSort
FROM [Some Table]
WHERE Abs([Numbers] in
(SELECT Abs([Numbers])
FROM [Some Table] as Temp
WHERE Abs(Temp.[Numbers]) = [Some Table].Numbers
AND Temp.[Company Name] = [Some Table].[Company Name]
GROUP BY Abs([Numbers])
HAVING Count(*) > 1)

Perhaps a simpler and faster process would be to use two queries. First query
would identify the records to use. Save this query a qFindRecords

SELECT [Company Name], Abs(Numbers) as NumValue
FROM [Some Table]
GROUP BY [Company Name], Abs(Numbers)
HAVING Count([Company Name])>1

Now using that saved query, you can use a query like the following.
SELECT [Some Table].[Company Name]
, [Some Table].[Numbers]
, Abs([Some Table].[Numbers]) as SpecialSort
FROM [Some Table] INNER JOIN qFindRecords
ON [Some Table].[Company Name] = qFindRecords.[Company Name]
AND (Abs([SomeTable].[Numbers]) = qFindRecords.NumValue

If you need instructions on how to build the two queries in design view, post
back and request help.




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

I have two columns. One is Company name and the other is positive and
negative numbers. I need to group by company name. I also need to group by
the pos/neg numbers (ignoring the neg sign) and excluding orphans.

I have tried using the ABS, but it changes the neg to a positive. How can I
get it to ignore, but still display the neg number.

i.e. $134.00
-$134.00
$134.00

$122.14
$122.14
-$122.14

If I had only one $155.00 or -$164.00, etc. I do not want it to show up on
my report.

.

 

[Access Developer]

Use the ABS function on a column that you only use for Sorting, but do not
display that value; continue to display the signed values... there's a
checkbox in the Query Builder that allows you to display or not display a
column.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

Did you get anywhere with this I need to do the same thing?

I have two columns. One is Company name and the other is positive and
negative numbers. I need to group by company name. I also need to group
by
the pos/neg numbers (ignoring the neg sign) and excluding orphans.

I have tried using the ABS, but it changes the neg to a positive. How
can I
get it to ignore, but still display the neg number.

i.e. $134.00
-$134.00
$134.00

$122.14
$122.14
-$122.14

If I had only one $155.00 or -$164.00, etc. I do not want it to show up
on
my report.

On Tuesday, December 29, 2009 12:38 PM KARL DEWEY wrote:
Display your number field as is but group on a copy of the field with
the ABS.
--
Build a little, test a little.


"Jen" wrote:

On Tuesday, December 29, 2009 3:12 PM Jen wrote:
I dropped another Amt field into the query and used the formula below.
ABS: Format(Abs([Amt]),"Fixed")

I then added both the 'Amt' and the abs'Amt' fields to the report and
grouped by the abs field, but made it not visible.

I used this one to get rid of any orphans (without dups) - the standard
script from the dup query.
In (SELECT [Amt] FROM [ApTest] As Tmp GROUP BY [Amt] HAVING
Count(*)>1 )

Thanks.

"KARL DEWEY" wrote:

On Tuesday, December 29, 2009 3:21 PM John Spencer wrote:
First base your report on a query that screens out companies with only
one record.

SELECT [Company Name]
, [Numbers]
, Abs([Numbers]) as SpecialSort
FROM [Some Table]
WHERE Abs([Numbers] in
(SELECT Abs([Numbers])
FROM [Some Table] as Temp
WHERE Abs(Temp.[Numbers]) = [Some Table].Numbers
AND Temp.[Company Name] = [Some Table].[Company Name]
GROUP BY Abs([Numbers])
HAVING Count(*) > 1)

Perhaps a simpler and faster process would be to use two queries.
First query
would identify the records to use. Save this query a qFindRecords
SELECT [Company Name], Abs(Numbers) as NumValue
FROM [Some Table]
GROUP BY [Company Name], Abs(Numbers)
HAVING Count([Company Name])>1

Now using that saved query, you can use a query like the following.
SELECT [Some Table].[Company Name]
, [Some Table].[Numbers]
, Abs([Some Table].[Numbers]) as SpecialSort
FROM [Some Table] INNER JOIN qFindRecords
ON [Some Table].[Company Name] = qFindRecords.[Company Name]
AND (Abs([SomeTable].[Numbers]) = qFindRecords.NumValue

If you need instructions on how to build the two queries in design
view, post
back and request help.




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

Jen wrote:

On Tuesday, December 29, 2009 3:30 PM John Spencer wrote:
Small problem, I think you want to include records if you had two
records for
a company with amt field values of
-134 and 134

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

Jen wrote:

On Thursday, December 31, 2009 2:16 PM Jen wrote:
I have two tables of data to combine, one table having only two
columns of
data, ID and Company Name. (I should have mentioned this.) The
other having
ID and Amount. I created two queries - one a make table. I did not
want to
have to create a new table of data, but when I tried to combine both
fields
of 'numbers' - 'Amount' and 'absAmount' using one query or even
based on a
first query, I could not get it to work. But when I ran a make
table creating
the extra column of 'absAmount' and then running another query for
the
exclusion of orphans using the below queries, I was able to get it
to work.

In this query I joined both tables and then created the additional
column
for absAmount calling it 'Gross', this was my make table query. I
added all
of the other fields in this query too.
Gross: Format(Abs([GrossAmt]),"Currency")

The following query was based on the Make Table and took out any
single
instances of 'Gross'
In (SELECT [Gross] FROM [MTAbsAmt] As Tmp GROUP BY [Gross] HAVING
Count(*)>1 )

I did the sorting and grouping within the report itself.

Can you tell me if there is a way to join two tables together,
create an
'absAmt' column, and only collect dups without creating a make
table.

Thank you.

"John Spencer" wrote:

 

[Access Developer]

Oh, I just realized I should have mentioned that sorting in Reports is
controlled by the SortingAndGrouping property of the Report, not by the
order of data in the Query, but my other comments still apply... sort on the
absolute value, but display the signed value.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

Use the ABS function on a column that you only use for Sorting, but do not
display that value; continue to display the signed values... there's a
checkbox in the Query Builder that allows you to display or not display a
column.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

Did you get anywhere with this I need to do the same thing?

I have two columns. One is Company name and the other is positive and
negative numbers. I need to group by company name. I also need to
group by
the pos/neg numbers (ignoring the neg sign) and excluding orphans.

I have tried using the ABS, but it changes the neg to a positive. How
can I
get it to ignore, but still display the neg number.

i.e. $134.00
-$134.00
$134.00

$122.14
$122.14
-$122.14

If I had only one $155.00 or -$164.00, etc. I do not want it to show up
on
my report.

Display your number field as is but group on a copy of the field with
the ABS.
--
Build a little, test a little.


:

I dropped another Amt field into the query and used the formula below.
ABS: Format(Abs([Amt]),"Fixed")

I then added both the 'Amt' and the abs'Amt' fields to the report and
grouped by the abs field, but made it not visible.

I used this one to get rid of any orphans (without dups) - the
standard
script from the dup query.
In (SELECT [Amt] FROM [ApTest] As Tmp GROUP BY [Amt] HAVING
Count(*)>1 )

Thanks.

:

First base your report on a query that screens out companies with
only one record.

SELECT [Company Name]
, [Numbers]
, Abs([Numbers]) as SpecialSort
FROM [Some Table]
WHERE Abs([Numbers] in
(SELECT Abs([Numbers])
FROM [Some Table] as Temp
WHERE Abs(Temp.[Numbers]) = [Some Table].Numbers
AND Temp.[Company Name] = [Some Table].[Company Name]
GROUP BY Abs([Numbers])
HAVING Count(*) > 1)

Perhaps a simpler and faster process would be to use two queries.
First query
would identify the records to use. Save this query a qFindRecords
SELECT [Company Name], Abs(Numbers) as NumValue
FROM [Some Table]
GROUP BY [Company Name], Abs(Numbers)
HAVING Count([Company Name])>1

Now using that saved query, you can use a query like the following.
SELECT [Some Table].[Company Name]
, [Some Table].[Numbers]
, Abs([Some Table].[Numbers]) as SpecialSort
FROM [Some Table] INNER JOIN qFindRecords
ON [Some Table].[Company Name] = qFindRecords.[Company Name]
AND (Abs([SomeTable].[Numbers]) = qFindRecords.NumValue

If you need instructions on how to build the two queries in design
view, post
back and request help.




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

Jen wrote:

Small problem, I think you want to include records if you had two
records for
a company with amt field values of
-134 and 134

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

Jen wrote:

I have two tables of data to combine, one table having only two
columns of
data, ID and Company Name. (I should have mentioned this.) The
other having
ID and Amount. I created two queries - one a make table. I did
not want to
have to create a new table of data, but when I tried to combine
both fields
of 'numbers' - 'Amount' and 'absAmount' using one query or even
based on a
first query, I could not get it to work. But when I ran a make
table creating
the extra column of 'absAmount' and then running another query for
the
exclusion of orphans using the below queries, I was able to get it
to work.

In this query I joined both tables and then created the additional
column
for absAmount calling it 'Gross', this was my make table query. I
added all
of the other fields in this query too.
Gross: Format(Abs([GrossAmt]),"Currency")

The following query was based on the Make Table and took out any
single
instances of 'Gross'
In (SELECT [Gross] FROM [MTAbsAmt] As Tmp GROUP BY [Gross] HAVING
Count(*)>1 )

I did the sorting and grouping within the report itself.

Can you tell me if there is a way to join two tables together,
create an
'absAmt' column, and only collect dups without creating a make
table.

Thank you.

"John Spencer" wrote: