getting duped records from a table

[Guest]

I have a table of transactions. There are additions, subtractions, and then
there are pairs of additions and subtractions (we refer to them as moves) -
the move pairs have two fields (aside from the autonumber) different -
location and quantity. For a move pair of transactions, the first record is
the subtraction, and the second is the addition (the locations are different,
but the quantities are the same except for a factor of -1).

I want to make a query that only grabs the move pairs.

Here's what I have so far on the pulling pairs query:

Sample set:
AutoN Qty Location Date Time Item
Comment Order
1 50 A 04/22/07 12:13:06 AM X this is
an add 0001
2 -20 A 04/22/07 12:13:06 AM X this is
a subt. 0001
3 -15 A 04/22/07 12:15:06 AM X this is
a move
4 15 B 04/22/07 12:15:06 AM X this is a
move
5 -5 B 04/22/07 12:22:08 AM X this is
a subt.


The pairs query would pull records 3 and 4. (Even though the timestamp is
the same on 1 and 2, the quantity(1)*-1 <> quantity(2).)



Here's the query:
SELECT tbl.TransDate, tbl.TransTime, tbl.Item, tbl.Comment, tbl.Order,
tbl.Qty, tbl.AutoN, tbl.Location
FROM tbl
WHERE (((tbl.TransDate) In (SELECT [TransDate] FROM [tbl] As Tmp GROUP BY
[TransDate],[TransTime],[Item],[Comment],[Order] HAVING Count(*)>1 And
[TransTime] = [tbl].[TransTime] And [Item] = [tbl].[Item] And [Comment] =
[tbl].[Comment] And [Order] = [tbl].[Order])))

ORDER BY tbl.TransDate, tbl.TransTime, tbl.Item, tbl.Comment, tbl.Order,
tbl.Qty;


I'm having 2 separate problems:
1) I would like to get the Qty check in the subquery - that the quantity on
the record is -1* the matching record. But if I put a quantity comparison in
the subquery (unless it's an absolute value function), the subquery returns
nothing. I'm trying to avoid the Absolute value function, because of the slim
possiblity that it could match a record incorrectly.

2) Not all of my records that are move pairs are being brought up - in fact,
only a few of them are. I can't see any thing - the ones being pulled as
pairs are matched pairs, and the ones being missed sure seem to be matched
pairs, right down to the time. Is this a time field problem, where even
though the records have the same time on them, it isn't considered the same
time?

Thanks in advance for any suggestions,
~yb

 

[Guest]

Try this:

SELECT *
FROM tbl AS T1
WHERE EXISTS
(SELECT TransDate + TransTime
FROM tbl AS T2
WHERE T2.TransDate + TransTime = T1.TransDate + TransTime
GROUP BY TransDate + TransTime
HAVING COUNT(*) > 1
AND SUM(Qty) = 0);

If you had a single TransDateTime column rather than the separate columns
for date and time then you'd avoid the need to add the values of course,
which should improve performance as it would use of an index on the column.

Ken Sheridan
Stafford, England

I have a table of transactions. There are additions, subtractions, and then
there are pairs of additions and subtractions (we refer to them as moves) -
the move pairs have two fields (aside from the autonumber) different -
location and quantity. For a move pair of transactions, the first record is
the subtraction, and the second is the addition (the locations are different,
but the quantities are the same except for a factor of -1).

I want to make a query that only grabs the move pairs.

Here's what I have so far on the pulling pairs query:

Sample set:
AutoN Qty Location Date Time Item
Comment Order
1 50 A 04/22/07 12:13:06 AM X this is
an add 0001
2 -20 A 04/22/07 12:13:06 AM X this is
a subt. 0001
3 -15 A 04/22/07 12:15:06 AM X this is
a move
4 15 B 04/22/07 12:15:06 AM X this is a
move
5 -5 B 04/22/07 12:22:08 AM X this is
a subt.


The pairs query would pull records 3 and 4. (Even though the timestamp is
the same on 1 and 2, the quantity(1)*-1 <> quantity(2).)



Here's the query:
SELECT tbl.TransDate, tbl.TransTime, tbl.Item, tbl.Comment, tbl.Order,
tbl.Qty, tbl.AutoN, tbl.Location
FROM tbl
WHERE (((tbl.TransDate) In (SELECT [TransDate] FROM [tbl] As Tmp GROUP BY
[TransDate],[TransTime],[Item],[Comment],[Order] HAVING Count(*)>1 And
[TransTime] = [tbl].[TransTime] And [Item] = [tbl].[Item] And [Comment] =
[tbl].[Comment] And [Order] = [tbl].[Order])))

ORDER BY tbl.TransDate, tbl.TransTime, tbl.Item, tbl.Comment, tbl.Order,
tbl.Qty;


I'm having 2 separate problems:
1) I would like to get the Qty check in the subquery - that the quantity on
the record is -1* the matching record. But if I put a quantity comparison in
the subquery (unless it's an absolute value function), the subquery returns
nothing. I'm trying to avoid the Absolute value function, because of the slim
possiblity that it could match a record incorrectly.

2) Not all of my records that are move pairs are being brought up - in fact,
only a few of them are. I can't see any thing - the ones being pulled as
pairs are matched pairs, and the ones being missed sure seem to be matched
pairs, right down to the time. Is this a time field problem, where even
though the records have the same time on them, it isn't considered the same
time?

Thanks in advance for any suggestions,
~yb