Matching Negative and Positive Values

[cru]

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is 508.00 and 1
Negative is -508.00

Is there a function that will match negative 508 and postive 508? I need to
exclude those matching +/- values and export other 4 positive value.

Any thoughts? Thanks.
CRU

 

[KARL DEWEY]

Abs([YourField]) will make everything positive.

 

[cru]

Karl,

Thank you for the reply. Once I convert to Positive, how would I query to
exclude 508? Is there IF statements that can be used to state if
508=508,then exclude?
Thanks,
CRU

Abs([YourField]) will make everything positive.

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is 508.00 and 1
Negative is -508.00

Is there a function that will match negative 508 and postive 508? I need to
exclude those matching +/- values and export other 4 positive value.

Any thoughts? Thanks.
CRU

 

[Clifford Bass]

Hi,

Are these all in the same table? If so you might try something like
this:

select *
from tblMyTableName as A
where not exists
(select *
from tblMyTableName as B
where B.FieldName = -A.FieldName);

Clifford Bass

 

[Clifford Bass]

Hi again,

And if you only want positive values AND you have negative values with
no "matching" positives, you can change it so:

select *
from tblMyTableName as A
where FieldName > 0 and not exists
(select *
from tblMyTableName as B
where B.FieldName = -A.FieldName);

Clifford Bass

 

[KARL DEWEY]

Need more input. Are there two table you are working with?
Post sample data like this --
Table1 --
435
-324
650
-123

Table2 --
-678
123
409
-427

Karl,

Thank you for the reply. Once I convert to Positive, how would I query to
exclude 508? Is there IF statements that can be used to state if
508=508,then exclude?
Thanks,
CRU

Abs([YourField]) will make everything positive.

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is 508.00 and 1
Negative is -508.00

Is there a function that will match negative 508 and postive 508? I need to
exclude those matching +/- values and export other 4 positive value.

Any thoughts? Thanks.
CRU

 

[cru]

Karl,

I have one table with the following info:

Headers:
Unique#
Amount
Balance Type

Unique# | Amount | Balance Type
1001315 508.00 Debit
1001315 -508.00 Credit
1001315 472.00 Debit

So in this example, I am trying to match 508 Debit/Credit and exclude them
so that I only retrive 472.00

Need more input. Are there two table you are working with?
Post sample data like this --
Table1 --
435
-324
650
-123

Table2 --
-678
123
409
-427

Karl,

Thank you for the reply. Once I convert to Positive, how would I query to
exclude 508? Is there IF statements that can be used to state if
508=508,then exclude?
Thanks,
CRU

Abs([YourField]) will make everything positive.

:

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is 508.00 and 1
Negative is -508.00

Is there a function that will match negative 508 and postive 508? I need to
exclude those matching +/- values and export other 4 positive value.

Any thoughts? Thanks.
CRU

 

[KARL DEWEY]

Try this --
UniqueNotZero --
SELECT YourTable.Letter
FROM YourTable
GROUP BY YourTable.Letter
HAVING (((Sum(YourTable.QTY))<>0));

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable JOIN UniqueNotZero ON YourTable.[Unique#] =
UniqueNotZero.[Unique#];

Karl,

I have one table with the following info:

Headers:
Unique#
Amount
Balance Type

Unique# | Amount | Balance Type
1001315 508.00 Debit
1001315 -508.00 Credit
1001315 472.00 Debit

So in this example, I am trying to match 508 Debit/Credit and exclude them
so that I only retrive 472.00

Need more input. Are there two table you are working with?
Post sample data like this --
Table1 --
435
-324
650
-123

Table2 --
-678
123
409
-427

Karl,

Thank you for the reply. Once I convert to Positive, how would I query to
exclude 508? Is there IF statements that can be used to state if
508=508,then exclude?
Thanks,
CRU

:

Abs([YourField]) will make everything positive.

:

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is 508.00 and 1
Negative is -508.00

Is there a function that will match negative 508 and postive 508? I need to
exclude those matching +/- values and export other 4 positive value.

Any thoughts? Thanks.
CRU

 

[Petr Danes]

Wouldn't a self-join be better? Seems to me it would simpler and more
straightforward to use:

(Untested)

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable as YT1
LEFT OUTER JOIN YourTable as YT2
ON YT1.[Unique#]=YT2.[Unique#]
AND YT1.Amount= -YT2.Amount
AND YT1.[Balance Type] <> YT2.[Balance Type];
WHERE YT2.[Unique#] Is Null;

Self join on same unique id, negative of each other's amount and one of each
debit and credit, then exclude those that do in fact have such a match..

Just a thought.

Petr

Try this --
UniqueNotZero --
SELECT YourTable.Letter
FROM YourTable
GROUP BY YourTable.Letter
HAVING (((Sum(YourTable.QTY))<>0));

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable JOIN UniqueNotZero ON YourTable.[Unique#] =
UniqueNotZero.[Unique#];

Karl,

I have one table with the following info:

Headers:
Unique#
Amount
Balance Type

Unique# | Amount | Balance Type
1001315 508.00 Debit
1001315 -508.00 Credit
1001315 472.00 Debit

So in this example, I am trying to match 508 Debit/Credit and exclude
them
so that I only retrive 472.00

Need more input. Are there two table you are working with?
Post sample data like this --
Table1 --
435
-324
650
-123

Table2 --
-678
123
409
-427


:

Karl,

Thank you for the reply. Once I convert to Positive, how would I
query to
exclude 508? Is there IF statements that can be used to state if
508=508,then exclude?
Thanks,
CRU

:

Abs([YourField]) will make everything positive.

:

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is 508.00
and 1
Negative is -508.00

Is there a function that will match negative 508 and postive 508?
I need to
exclude those matching +/- values and export other 4 positive
value.

Any thoughts? Thanks.
CRU

 

[KARL DEWEY]

Not if credit was made in more than one payment.

Wouldn't a self-join be better? Seems to me it would simpler and more
straightforward to use:

(Untested)

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable as YT1
LEFT OUTER JOIN YourTable as YT2
ON YT1.[Unique#]=YT2.[Unique#]
AND YT1.Amount= -YT2.Amount
AND YT1.[Balance Type] <> YT2.[Balance Type];
WHERE YT2.[Unique#] Is Null;

Self join on same unique id, negative of each other's amount and one of each
debit and credit, then exclude those that do in fact have such a match..

Just a thought.

Petr

Try this --
UniqueNotZero --
SELECT YourTable.Letter
FROM YourTable
GROUP BY YourTable.Letter
HAVING (((Sum(YourTable.QTY))<>0));

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable JOIN UniqueNotZero ON YourTable.[Unique#] =
UniqueNotZero.[Unique#];

Karl,

I have one table with the following info:

Headers:
Unique#
Amount
Balance Type

Unique# | Amount | Balance Type
1001315 508.00 Debit
1001315 -508.00 Credit
1001315 472.00 Debit

So in this example, I am trying to match 508 Debit/Credit and exclude
them
so that I only retrive 472.00



:

Need more input. Are there two table you are working with?
Post sample data like this --
Table1 --
435
-324
650
-123

Table2 --
-678
123
409
-427


:

Karl,

Thank you for the reply. Once I convert to Positive, how would I
query to
exclude 508? Is there IF statements that can be used to state if
508=508,then exclude?
Thanks,
CRU

:

Abs([YourField]) will make everything positive.

:

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is 508.00
and 1
Negative is -508.00

Is there a function that will match negative 508 and postive 508?
I need to
exclude those matching +/- values and export other 4 positive
value.

Any thoughts? Thanks.
CRU

 

[Petr Danes]

Well, that's true, but the OP asked for a query that matched and excluded
those records that had matching positive and negative values, not the set
that added up to a match. Your way would provide a better selection of those
whose payment history balanced out their debt, if that was in fact what he
was after, but that didn't occur to me.

Petr

Not if credit was made in more than one payment.

Wouldn't a self-join be better? Seems to me it would simpler and more
straightforward to use:

(Untested)

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable as YT1
LEFT OUTER JOIN YourTable as YT2
ON YT1.[Unique#]=YT2.[Unique#]
AND YT1.Amount= -YT2.Amount
AND YT1.[Balance Type] <> YT2.[Balance Type];
WHERE YT2.[Unique#] Is Null;

Self join on same unique id, negative of each other's amount and one of
each
debit and credit, then exclude those that do in fact have such a match..

Just a thought.

Petr

Try this --
UniqueNotZero --
SELECT YourTable.Letter
FROM YourTable
GROUP BY YourTable.Letter
HAVING (((Sum(YourTable.QTY))<>0));

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable JOIN UniqueNotZero ON YourTable.[Unique#] =
UniqueNotZero.[Unique#];


:

Karl,

I have one table with the following info:

Headers:
Unique#
Amount
Balance Type

Unique# | Amount | Balance Type
1001315 508.00 Debit
1001315 -508.00 Credit
1001315 472.00 Debit

So in this example, I am trying to match 508 Debit/Credit and exclude
them
so that I only retrive 472.00



:

Need more input. Are there two table you are working with?
Post sample data like this --
Table1 --
435
-324
650
-123

Table2 --
-678
123
409
-427


:

Karl,

Thank you for the reply. Once I convert to Positive, how would I
query to
exclude 508? Is there IF statements that can be used to state if
508=508,then exclude?
Thanks,
CRU

:

Abs([YourField]) will make everything positive.

:

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is
508.00
and 1
Negative is -508.00

Is there a function that will match negative 508 and postive
508?
I need to
exclude those matching +/- values and export other 4 positive
value.

Any thoughts? Thanks.
CRU

 

[Clifford Bass]

Hi Petr,

So, it looks like my first solution should do what the OP wants. Have
you tried it (of course with your table and field names)?

Clifford Bass

Well, that's true, but the OP asked for a query that matched and excluded
those records that had matching positive and negative values, not the set
that added up to a match. Your way would provide a better selection of those
whose payment history balanced out their debt, if that was in fact what he
was after, but that didn't occur to me.

Petr

Not if credit was made in more than one payment.

Wouldn't a self-join be better? Seems to me it would simpler and more
straightforward to use:

(Untested)

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable as YT1
LEFT OUTER JOIN YourTable as YT2
ON YT1.[Unique#]=YT2.[Unique#]
AND YT1.Amount= -YT2.Amount
AND YT1.[Balance Type] <> YT2.[Balance Type];
WHERE YT2.[Unique#] Is Null;

Self join on same unique id, negative of each other's amount and one of
each
debit and credit, then exclude those that do in fact have such a match..

Just a thought.

Petr



"KARL DEWEY" <[email protected]> píše v diskusním
příspěvku Try this --
UniqueNotZero --
SELECT YourTable.Letter
FROM YourTable
GROUP BY YourTable.Letter
HAVING (((Sum(YourTable.QTY))<>0));

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable JOIN UniqueNotZero ON YourTable.[Unique#] =
UniqueNotZero.[Unique#];


:

Karl,

I have one table with the following info:

Headers:
Unique#
Amount
Balance Type

Unique# | Amount | Balance Type
1001315 508.00 Debit
1001315 -508.00 Credit
1001315 472.00 Debit

So in this example, I am trying to match 508 Debit/Credit and exclude
them
so that I only retrive 472.00



:

Need more input. Are there two table you are working with?
Post sample data like this --
Table1 --
435
-324
650
-123

Table2 --
-678
123
409
-427


:

Karl,

Thank you for the reply. Once I convert to Positive, how would I
query to
exclude 508? Is there IF statements that can be used to state if
508=508,then exclude?
Thanks,
CRU

:

Abs([YourField]) will make everything positive.

:

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is
508.00
and 1
Negative is -508.00

Is there a function that will match negative 508 and postive
508?
I need to
exclude those matching +/- values and export other 4 positive
value.

Any thoughts? Thanks.
CRU

 

[Petr Danes]

No, I would have to generate a bunch of test data and I have lots of work to
do right now. It just occurred to me that the self-join might be a tidier
way than the EXISTS clause and maybe faster as well, but I haven't actually
tried either one.

Petr

Hi Petr,

So, it looks like my first solution should do what the OP wants. Have
you tried it (of course with your table and field names)?

Clifford Bass

Well, that's true, but the OP asked for a query that matched and excluded
those records that had matching positive and negative values, not the set
that added up to a match. Your way would provide a better selection of
those
whose payment history balanced out their debt, if that was in fact what
he
was after, but that didn't occur to me.

Petr

Not if credit was made in more than one payment.

:

Wouldn't a self-join be better? Seems to me it would simpler and more
straightforward to use:

(Untested)

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable as YT1
LEFT OUTER JOIN YourTable as YT2
ON YT1.[Unique#]=YT2.[Unique#]
AND YT1.Amount= -YT2.Amount
AND YT1.[Balance Type] <> YT2.[Balance Type];
WHERE YT2.[Unique#] Is Null;

Self join on same unique id, negative of each other's amount and one
of
each
debit and credit, then exclude those that do in fact have such a
match..

Just a thought.

Petr



"KARL DEWEY" <[email protected]> píše v diskusním
příspěvku Try this --
UniqueNotZero --
SELECT YourTable.Letter
FROM YourTable
GROUP BY YourTable.Letter
HAVING (((Sum(YourTable.QTY))<>0));

SELECT [Unique#], Amount, [Balance Type]
FROM YourTable JOIN UniqueNotZero ON YourTable.[Unique#] =
UniqueNotZero.[Unique#];


:

Karl,

I have one table with the following info:

Headers:
Unique#
Amount
Balance Type

Unique# | Amount | Balance Type
1001315 508.00 Debit
1001315 -508.00 Credit
1001315 472.00 Debit

So in this example, I am trying to match 508 Debit/Credit and
exclude
them
so that I only retrive 472.00



:

Need more input. Are there two table you are working with?
Post sample data like this --
Table1 --
435
-324
650
-123

Table2 --
-678
123
409
-427


:

Karl,

Thank you for the reply. Once I convert to Positive, how would
I
query to
exclude 508? Is there IF statements that can be used to state
if
508=508,then exclude?
Thanks,
CRU

:

Abs([YourField]) will make everything positive.

:

Scenario:
1. 5 Positive Value and 1 Negative Value.
2. 4 Positive value have different numbers. 1 Postive is
508.00
and 1
Negative is -508.00

Is there a function that will match negative 508 and
postive
508?
I need to
exclude those matching +/- values and export other 4
positive
value.

Any thoughts? Thanks.
CRU

 

[Clifford Bass]

Hi Petr,

The self-join works in Access, although you obviously have to edit it
by hand and once you do so, it will not display in the query designer. The
query with an exists clause does display in the designer. Speedwise, I doubt
there would be a difference. The self-join and the exists clause would both
be joining on key fields as well as non-key fields. If you are concerned
about performance, indexing on a combination of all of the involved fields
probably would help. So the choice, in my opinion, is primarily stylistic.

Clifford Bass

 

[Petr Danes]

The self-join works in Access, although you
obviously have to edit it by hand and once you do
so, it will not display in the query designer.

True, it's an SQL-only query from that point on. The EXISTS clause also has
to be coded in SQL in the criteria box, though, doesn't it? But at least you
can see it in the QBE window, whereas the self-join either doesn't show at
all or munches the SQL if it does display. No idea why, either, it doesn't
seem like it would have been all that hard for the Access boyz at MS to draw
a self-join line. SQL Server Management Studio does it just fine, even
though in many things, its graphic interface is not nearly as pretty as
Access's.

Speedwise, I doubt there would be a difference. The
self-join and the exists clause would both be
joining on key fields as well as non-key fields.

Really? That's good to know; I've been generally avoiding EXISTS because I
thought I had read somewhere that it was usually (not always) slower to use
a subquery than a self-join, and a couple of places where I had used it, the
results were rather slow. Of course, I was doing some oddball one-off stuff
and probably did not have an optimal structure.

So the choice, in my opinion, is primarily
stylistic.

Likely so, a great many things are largely a matter of taste, and quite
often, those are the arguments that generate the most heat and least light,
since they're personal preferences and not things that can be substantiated
or defended on any firm ground. Interesting thread, though, and I appreciate
your thoughts on the subject.

Petr

 

[John W. Vinson]

True, it's an SQL-only query from that point on. The EXISTS clause also has
to be coded in SQL in the criteria box, though, doesn't it? But at least you
can see it in the QBE window, whereas the self-join either doesn't show at
all or munches the SQL if it does display.

Um?

I've used selfjoins frequently and they work fine. You need to alias the
second instance of course but it shows up fine in both the grid and the SQL
view.

 

[Clifford Bass]

Hi Petr,

Yes, you are right that the exists clause has to be coded (by hand).
However it is coded in the top line, not the criteria line. You would put a
True or False in the criteria line. This is an artifact of how Access does
it in the designer and really is not needed from a pure SQL perspective.

Regarding the exists clause's performance; it may, behind the scenes,
end up being flattened out to a join query by the product's query optimizer.
This can vary from one SQL database brand to another, depending on the smarts
of the optimizer. Regardless of that, it never hurts to test one method vs.
another when dealing with queries if you are having speed issues. For those
databases that allow you to see the query optimizer's plan, you can sometimes
see where an additional index or two can help. Of course that has to be
weighed against the overhead of maintaining additional indexes.

Glad you find it interesting; and hopefully useful.

Clifford Bass

 

[Clifford Bass]

Hi John,

His join involves equality with the negative of a column and an
inequality of another column:

LEFT OUTER JOIN YourTable as YT2
ON YT1.[Unique#]=YT2.[Unique#]
AND YT1.Amount= -YT2.Amount
AND YT1.[Balance Type] <> YT2.[Balance Type]

You cannot do that in the designer. It would be nice if when you
double-click on a linking line that it would give the option to specify an
operator (=, <>, >, <, etc.). It also would be nice if you could somehow
specify an expression as part of the join (-Field, Abs(Field), Field1 +
Field2, Field1 & Field2, etc.). Note that moving the second and third items
out of the join and into the where clause, changes the way the query works
related to finding rows that do not match. This would be due to the
inability to make those comparisons when there is no row on the right side of
the join.

If you use his query:

SELECT YT1.[Unique#], YT1.Amount, YT1.[Balance Type]
FROM YourTable AS YT1 LEFT JOIN YourTable AS YT2 ON (YT1.[Balance Type] <>
YT2.[Balance Type]) AND (YT1.Amount= -YT2.Amount) AND
(YT1.[Unique#]=YT2.[Unique#])
WHERE YT2.[Unique#] Is Null;

on his sample data you get the desired results:

Unique# Amount Balance Type
1001315 $472.00 Debit

If you move those two items out of the join and into the query:

SELECT YT1.[Unique#], YT1.Amount, YT1.[Balance Type]
FROM YourTable AS YT1 LEFT JOIN YourTable AS YT2 ON
YT1.[Unique#]=YT2.[Unique#]
WHERE YT1.[Balance Type] <> YT2.[Balance Type] AND YT1.Amount= -YT2.Amount
AND YT2.[Unique#] Is Null;

you do not get any rows returned.

Clifford Bass

 

[John W. Vinson]

Hi John,

His join involves equality with the negative of a column and an
inequality of another column:

LEFT OUTER JOIN YourTable as YT2
ON YT1.[Unique#]=YT2.[Unique#]
AND YT1.Amount= -YT2.Amount
AND YT1.[Balance Type] <> YT2.[Balance Type]

You cannot do that in the designer.

Sorry... jumped into the thread without reading back. You're quite right of
course; although you can display a Self Join in the grid, you cannot display a
Non-Equi Join (whether self or other join).

 

[Clifford Bass]

Hi John,

No problem. I've been managing not to read things today as thoroughly
today as I should either.

Clifford