Compare values in a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
 
If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.
 
I'm sorry, my question was not clear. The values that I want to sum are in
the same column, debits/credits (pos & neg numbers). I want to find the
values in this column in Access, sorted by abs. value (in another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


Klatuu said:
If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.

Jim said:
I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
 
Is the other column in the same table or a different table?

Jim said:
I'm sorry, my question was not clear. The values that I want to sum are in
the same column, debits/credits (pos & neg numbers). I want to find the
values in this column in Access, sorted by abs. value (in another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


Klatuu said:
If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.

Jim said:
I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
The abs value column is in a query that has all the fields from the table.

Klatuu said:
Is the other column in the same table or a different table?

Jim said:
I'm sorry, my question was not clear. The values that I want to sum are in
the same column, debits/credits (pos & neg numbers). I want to find the
values in this column in Access, sorted by abs. value (in another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


Klatuu said:
If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.

:

I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
Sorry, Jim, maybe I did not ask the question correctly. So far I understand
you have a field in a table that may contain postive or negative numbers.
You have converted those numbers to all postive in a query using the Abs()
function.
Now, you want to match the values in this column of the query to values in
another column.

What I am still unclear on is:
Is the other column in the same table that you query is based on?
Are they included in the query you built?

Jim said:
The abs value column is in a query that has all the fields from the table.

Klatuu said:
Is the other column in the same table or a different table?

Jim said:
I'm sorry, my question was not clear. The values that I want to sum are in
the same column, debits/credits (pos & neg numbers). I want to find the
values in this column in Access, sorted by abs. value (in another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


:

If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.

:

I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
I should probably start over, I apologize for being so confusing.
I have a table (forget about the abs() for a moment) that has positive and
negative numbers in one column in Access. I want to find those values that
sum to zero.
Back to the ABS function; I tried to use it to group the #s, but it's not
necessary.
Essentially, I just want to find the numbers in the column of the original
table that net out. I can do it with or without the ABS() function.
Thanks very much for your help.
Jim


Klatuu said:
Sorry, Jim, maybe I did not ask the question correctly. So far I understand
you have a field in a table that may contain postive or negative numbers.
You have converted those numbers to all postive in a query using the Abs()
function.
Now, you want to match the values in this column of the query to values in
another column.

What I am still unclear on is:
Is the other column in the same table that you query is based on?
Are they included in the query you built?

Jim said:
The abs value column is in a query that has all the fields from the table.

Klatuu said:
Is the other column in the same table or a different table?

:

I'm sorry, my question was not clear. The values that I want to sum are in
the same column, debits/credits (pos & neg numbers). I want to find the
values in this column in Access, sorted by abs. value (in another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


:

If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.

:

I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
I feel like we are right back where we started. Let's try it like this:

Name of the Table with the field to match on:_________________________
Name of the field to match on:___________________

Name of the Table with the field to compare:__________________
Name of the field to compare:____________________________

What is the desired output format:___________________________

What is the logic that constitiues a match between [Match On] and [Compare]

For example Abs([MatchOn] = [Compare]
or Abs([MatchOn] = Abs([Compare])
or other

Jim said:
I should probably start over, I apologize for being so confusing.
I have a table (forget about the abs() for a moment) that has positive and
negative numbers in one column in Access. I want to find those values that
sum to zero.
Back to the ABS function; I tried to use it to group the #s, but it's not
necessary.
Essentially, I just want to find the numbers in the column of the original
table that net out. I can do it with or without the ABS() function.
Thanks very much for your help.
Jim


Klatuu said:
Sorry, Jim, maybe I did not ask the question correctly. So far I understand
you have a field in a table that may contain postive or negative numbers.
You have converted those numbers to all postive in a query using the Abs()
function.
Now, you want to match the values in this column of the query to values in
another column.

What I am still unclear on is:
Is the other column in the same table that you query is based on?
Are they included in the query you built?

Jim said:
The abs value column is in a query that has all the fields from the table.

:

Is the other column in the same table or a different table?

:

I'm sorry, my question was not clear. The values that I want to sum are in
the same column, debits/credits (pos & neg numbers). I want to find the
values in this column in Access, sorted by abs. value (in another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


:

If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.

:

I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
OK, this is funny. Thank you for your patience.
Name of the Table with the field to match on:___Suspense Funds______________
Name of the field to match on:____DOLLARS_______________
Name of the Table with the field to compare:_Suspense Funds_________________
Name of the field to compare:__________DOLLARS__________________
What is the desired output format:______CURRENCY_____________________

All data is in one table. I am comparing dollar values in one column with
dollar values in the same column.

For instance, in my Suspense Funds table, I have fields:

Fund Name Account DOLLARS
Fund1 1234 $500
Fund2 1234 $600
Fund3 1234 ($500)

I want to pull the records where the DOLLAR amount of one record is the
negative value of another. In the example, I'd pull records 1 & 3, which sum
to zero. I want all fields from the records that net to zero. So, I'd have
in the results:

Fund Name Account DOLLARS
Fund1 1234 $500
Fund3 1234 ($500)

Does that make sense?
JIm




Klatuu said:
I feel like we are right back where we started. Let's try it like this:

Name of the Table with the field to match on:_________________________
Name of the field to match on:___________________

Name of the Table with the field to compare:__________________
Name of the field to compare:____________________________

What is the desired output format:___________________________

What is the logic that constitiues a match between [Match On] and [Compare]

For example Abs([MatchOn] = [Compare]
or Abs([MatchOn] = Abs([Compare])
or other

Jim said:
I should probably start over, I apologize for being so confusing.
I have a table (forget about the abs() for a moment) that has positive and
negative numbers in one column in Access. I want to find those values that
sum to zero.
Back to the ABS function; I tried to use it to group the #s, but it's not
necessary.
Essentially, I just want to find the numbers in the column of the original
table that net out. I can do it with or without the ABS() function.
Thanks very much for your help.
Jim


Klatuu said:
Sorry, Jim, maybe I did not ask the question correctly. So far I understand
you have a field in a table that may contain postive or negative numbers.
You have converted those numbers to all postive in a query using the Abs()
function.
Now, you want to match the values in this column of the query to values in
another column.

What I am still unclear on is:
Is the other column in the same table that you query is based on?
Are they included in the query you built?

:

The abs value column is in a query that has all the fields from the table.

:

Is the other column in the same table or a different table?

:

I'm sorry, my question was not clear. The values that I want to sum are in
the same column, debits/credits (pos & neg numbers). I want to find the
values in this column in Access, sorted by abs. value (in another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


:

If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.

:

I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
I don't know how well this will work, but my first thought is to create a
second query. Is should include your existing query that gets the Abs of the
amount and and the table. Join the query on the Abs field to the Dollars
field in the table. Include whatever fields you need. I think the results
of this query will give you what you need. Sorry, but I can't test it at the
moment. I have too many things open in my database working on it and I don't
want to loose track.


Jim said:
OK, this is funny. Thank you for your patience.
Name of the Table with the field to match on:___Suspense Funds______________
Name of the field to match on:____DOLLARS_______________
Name of the Table with the field to compare:_Suspense Funds_________________
Name of the field to compare:__________DOLLARS__________________
What is the desired output format:______CURRENCY_____________________

All data is in one table. I am comparing dollar values in one column with
dollar values in the same column.

For instance, in my Suspense Funds table, I have fields:

Fund Name Account DOLLARS
Fund1 1234 $500
Fund2 1234 $600
Fund3 1234 ($500)

I want to pull the records where the DOLLAR amount of one record is the
negative value of another. In the example, I'd pull records 1 & 3, which sum
to zero. I want all fields from the records that net to zero. So, I'd have
in the results:

Fund Name Account DOLLARS
Fund1 1234 $500
Fund3 1234 ($500)

Does that make sense?
JIm




Klatuu said:
I feel like we are right back where we started. Let's try it like this:

Name of the Table with the field to match on:_________________________
Name of the field to match on:___________________

Name of the Table with the field to compare:__________________
Name of the field to compare:____________________________

What is the desired output format:___________________________

What is the logic that constitiues a match between [Match On] and [Compare]

For example Abs([MatchOn] = [Compare]
or Abs([MatchOn] = Abs([Compare])
or other

Jim said:
I should probably start over, I apologize for being so confusing.
I have a table (forget about the abs() for a moment) that has positive and
negative numbers in one column in Access. I want to find those values that
sum to zero.
Back to the ABS function; I tried to use it to group the #s, but it's not
necessary.
Essentially, I just want to find the numbers in the column of the original
table that net out. I can do it with or without the ABS() function.
Thanks very much for your help.
Jim


:

Sorry, Jim, maybe I did not ask the question correctly. So far I understand
you have a field in a table that may contain postive or negative numbers.
You have converted those numbers to all postive in a query using the Abs()
function.
Now, you want to match the values in this column of the query to values in
another column.

What I am still unclear on is:
Is the other column in the same table that you query is based on?
Are they included in the query you built?

:

The abs value column is in a query that has all the fields from the table.

:

Is the other column in the same table or a different table?

:

I'm sorry, my question was not clear. The values that I want to sum are in
the same column, debits/credits (pos & neg numbers). I want to find the
values in this column in Access, sorted by abs. value (in another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


:

If you are saying that you have some numeric fields in a table, then a query
will do this for you. You need to add a Calculated field to you query that
will add all the numeric fields together and then criteria on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] + [AnyNumberField]

Then make the criteria for this field 0.

:

I have a table with postive and negative numbers in it. I am trying to pull
just those records whose values sum to zero. I have created an ABS function
and sorted the data by ABS. How do I query the data to exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
What about a query that returns all of the negative values.

Join that query to the main data, but use ON (Table1.Dollars =
Abs(Table2.Dollars) OR Table1.Dollars = Table2.Dollars) plus, of course,
whatever other conditions are necessary)

Obviously you'll need to do this through SQL, as you won't be able to
represent the join condition in the graphical query builder.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
I don't know how well this will work, but my first thought is to create a
second query. Is should include your existing query that gets the Abs of
the
amount and and the table. Join the query on the Abs field to the Dollars
field in the table. Include whatever fields you need. I think the
results
of this query will give you what you need. Sorry, but I can't test it at
the
moment. I have too many things open in my database working on it and I
don't
want to loose track.


Jim said:
OK, this is funny. Thank you for your patience.
Name of the Table with the field to match on:___Suspense
Funds______________
Name of the field to match on:____DOLLARS_______________
Name of the Table with the field to compare:_Suspense
Funds_________________
Name of the field to compare:__________DOLLARS__________________
What is the desired output format:______CURRENCY_____________________

All data is in one table. I am comparing dollar values in one column
with
dollar values in the same column.

For instance, in my Suspense Funds table, I have fields:

Fund Name Account DOLLARS
Fund1 1234 $500
Fund2 1234 $600
Fund3 1234 ($500)

I want to pull the records where the DOLLAR amount of one record is the
negative value of another. In the example, I'd pull records 1 & 3, which
sum
to zero. I want all fields from the records that net to zero. So, I'd
have
in the results:

Fund Name Account DOLLARS
Fund1 1234 $500
Fund3 1234 ($500)

Does that make sense?
JIm




Klatuu said:
I feel like we are right back where we started. Let's try it like
this:

Name of the Table with the field to match on:_________________________
Name of the field to match on:___________________

Name of the Table with the field to compare:__________________
Name of the field to compare:____________________________

What is the desired output format:___________________________

What is the logic that constitiues a match between [Match On] and
[Compare]

For example Abs([MatchOn] = [Compare]
or Abs([MatchOn] = Abs([Compare])
or other

:

I should probably start over, I apologize for being so confusing.
I have a table (forget about the abs() for a moment) that has
positive and
negative numbers in one column in Access. I want to find those
values that
sum to zero.
Back to the ABS function; I tried to use it to group the #s, but it's
not
necessary.
Essentially, I just want to find the numbers in the column of the
original
table that net out. I can do it with or without the ABS() function.
Thanks very much for your help.
Jim


:

Sorry, Jim, maybe I did not ask the question correctly. So far I
understand
you have a field in a table that may contain postive or negative
numbers.
You have converted those numbers to all postive in a query using
the Abs()
function.
Now, you want to match the values in this column of the query to
values in
another column.

What I am still unclear on is:
Is the other column in the same table that you query is based on?
Are they included in the query you built?

:

The abs value column is in a query that has all the fields from
the table.

:

Is the other column in the same table or a different table?

:

I'm sorry, my question was not clear. The values that I want
to sum are in
the same column, debits/credits (pos & neg numbers). I want
to find the
values in this column in Access, sorted by abs. value (in
another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


:

If you are saying that you have some numeric fields in a
table, then a query
will do this for you. You need to add a Calculated field
to you query that
will add all the numeric fields together and then criteria
on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] +
[AnyNumberField]

Then make the criteria for this field 0.

:

I have a table with postive and negative numbers in it.
I am trying to pull
just those records whose values sum to zero. I have
created an ABS function
and sorted the data by ABS. How do I query the data to
exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
Interesting, but what do you want to happen if the records were
Fund Name Account DOLLARS
Fund1 1234 $500
Fund2 1234 $500
Fund3 1234 ($500)

Does fund3 match fund1, fund 2 or both? And throw in one more line
Fund4 1234 ($500)

Now, what matches with what?

Klatuu said:
I don't know how well this will work, but my first thought is to create a
second query. Is should include your existing query that gets the Abs of
the
amount and and the table. Join the query on the Abs field to the Dollars
field in the table. Include whatever fields you need. I think the
results
of this query will give you what you need. Sorry, but I can't test it at
the
moment. I have too many things open in my database working on it and I
don't
want to loose track.


Jim said:
OK, this is funny. Thank you for your patience.
Name of the Table with the field to match on:___Suspense
Funds______________
Name of the field to match on:____DOLLARS_______________
Name of the Table with the field to compare:_Suspense
Funds_________________
Name of the field to compare:__________DOLLARS__________________
What is the desired output format:______CURRENCY_____________________

All data is in one table. I am comparing dollar values in one column
with
dollar values in the same column.

For instance, in my Suspense Funds table, I have fields:

Fund Name Account DOLLARS
Fund1 1234 $500
Fund2 1234 $600
Fund3 1234 ($500)

I want to pull the records where the DOLLAR amount of one record is the
negative value of another. In the example, I'd pull records 1 & 3, which
sum
to zero. I want all fields from the records that net to zero. So, I'd
have
in the results:

Fund Name Account DOLLARS
Fund1 1234 $500
Fund3 1234 ($500)

Does that make sense?
JIm




Klatuu said:
I feel like we are right back where we started. Let's try it like
this:

Name of the Table with the field to match on:_________________________
Name of the field to match on:___________________

Name of the Table with the field to compare:__________________
Name of the field to compare:____________________________

What is the desired output format:___________________________

What is the logic that constitiues a match between [Match On] and
[Compare]

For example Abs([MatchOn] = [Compare]
or Abs([MatchOn] = Abs([Compare])
or other

:

I should probably start over, I apologize for being so confusing.
I have a table (forget about the abs() for a moment) that has
positive and
negative numbers in one column in Access. I want to find those
values that
sum to zero.
Back to the ABS function; I tried to use it to group the #s, but it's
not
necessary.
Essentially, I just want to find the numbers in the column of the
original
table that net out. I can do it with or without the ABS() function.
Thanks very much for your help.
Jim


:

Sorry, Jim, maybe I did not ask the question correctly. So far I
understand
you have a field in a table that may contain postive or negative
numbers.
You have converted those numbers to all postive in a query using
the Abs()
function.
Now, you want to match the values in this column of the query to
values in
another column.

What I am still unclear on is:
Is the other column in the same table that you query is based on?
Are they included in the query you built?

:

The abs value column is in a query that has all the fields from
the table.

:

Is the other column in the same table or a different table?

:

I'm sorry, my question was not clear. The values that I want
to sum are in
the same column, debits/credits (pos & neg numbers). I want
to find the
values in this column in Access, sorted by abs. value (in
another column),
that add up to zero.
Does that make sense?
Thanks,
Jim


:

If you are saying that you have some numeric fields in a
table, then a query
will do this for you. You need to add a Calculated field
to you query that
will add all the numeric fields together and then criteria
on that field to
include only those rows where this field is 0.

RecTotSum: [ANumberField] + [AnotherNumberField] +
[AnyNumberField]

Then make the criteria for this field 0.

:

I have a table with postive and negative numbers in it.
I am trying to pull
just those records whose values sum to zero. I have
created an ABS function
and sorted the data by ABS. How do I query the data to
exclude everything
that doesn't sum to zero?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top