Subquery to pair off line items

F

Frank

Hi there, I'm doing a reconcilation between two database journals and I need
to be able to compare records within the same table - ie if two entries have
the same ID and are within $5 of each other, I can create a field to "pair
off" the records, and later delete them. Anyone know how to compare values in
rows.
Thanks
 
J

John Spencer

Is there a primary key for the rows? Obviously ID is not the primary key
since you have duplicates?

Assumption:
You have a primary key on the rows of the table.

SELECT *
FROM TABLE
WHERE Table.PrimaryKey IN
(SELECT T1.PrimaryKey
FROM Table as T1 Inner Join Table as T2
ON T1.ID = T2.ID
WHERE Abs(T1.Amount-T2.Amount) <=5
AND T1.PrimaryKey <> T2.PrimaryKey)
Order by Table.ID
 
M

Michel Walsh

Hi,


Another solution:


SELECT a.*, b.*
FROM mytable As a INNER JOIN myTable as b
ON a.primarykey > b.primarykey AND a.id = b.id and ABS(a.price -
b.price) <= 5.00



Note that if a matches b, then b matches a, and also, a matches a. That is
why I used a.primaryKey > b.primaryKey, to break these symmetry and
reflection.


Hoping it may help,
Vanderghast, Access MVP
 
F

Frank

Thanks guys.
Both of your queries worked very well. They only show one side of the
matching leg (vertically) as the tables are shown side by side. I have a to
run an update query to add a match status on the original table and doing a
union with
SELECT b.*, a.* ...
seemed to give me both sides of the records. Prob not the prettiest way to
do it but thanks again.

Frank
 
F

Frank

Michel,
I think I spoke too soon. the problem is that this query will give me
multiple instances of the of the same primary key if that record matches more
than one other record. What I was hoping for was that once the record finds a
macth, both the records are flagged and no longer used in the
population/calculation. How could I limit my results to one match.
Thanks again
 
F

Frank

Here's an example of waht I mean.
It would be great to pair off the first line, mark the 2 IDs (174038, 58497
as a pair off) and move to the next record.

a.PK a.ID a.Amount b.PK b.ID b.Amount
174038 271887L 60416.67187 58497 271887L -60416.67
174038 271887L 60416.67187 124368 271887L -60416.67
174038 271887L 60416.67187 28631 271887L -60416.67
174038 271887L 60416.67187 102247 271887L -60416.67

Thanks again for any additional help.
 
F

Frank

John,
Thanks for the code. I tried your method and was wondering what would happen
if you had 3 records that matched the criteria. Would the first 2 that match
be paired off and the 3rd be left off the query -- this is what I am looking
for. Since the query doesn't show the matching record its hard to tell.
Thanks again for all your help.
Frank
 
J

John Spencer

If the query works the way I think it would work, all three records would
show up in the result.

You could try the following to show you the combinations of records. I'm
not at all sure it will work.

SELECT *
FROM Table as T1 Inner Join Table as T2
ON T1.ID = T2.ID
WHERE Abs(T1.Amount-T2.Amount) <=5
AND T1.PrimaryKey < T2.PrimaryKey
 
Top