Update Query - Want to Update Only the First Match

L

Lynn Johnson

I have two tables of financial transactions from two different systems as
follows:

Table A
CompanyID
TranAmt
TRID (transaction ID from system A)
TranID (to hold Table B transaction identifier)

Table B
CompanyID
TranAmt
TranID (transaction ID generated by autonumbering in Table B to give unique
identifer)
TRID (to hold TableA transaction identifier)

I want to compare the tables such that where the CompanyID and TranAmt match
in both tables the TableA.TranID (identifier from other Table B) and
TableB.TRID (identifier from Table A) are updated. My update query is
working, except that there may be multiple matches of company ID and amount
such that one record in Table A could have several matches in Table B and
vice versa. What I want is only the first match to result in the IDs being
updated.

I don't know VBA so I need some advice on doing this through the Access
Query Wizard or the SQL view.

Many thanks in advance for any help you can provide.
 
L

Lynn Johnson

I can't get this to work. Even with the 'Top 1' I still got multiple records
in one table being tagged with the ID from the other table. I've reverted to
a select query to just try to get it return one record from each table as a
match. Perhaps the problem is I have a many to many relationship because the
only unique feature is the uno in one table and the auto-generated tran
number in the other. At this point, as soon as I put the 'top 1' I get no
records returned, regardless of what other criteria I take out. I also tried
min(disb_uno) but no luck. Any other ideas?

SELECT CMSTRAN.DISB_UNO, CMSTRAN.TOBILL_AMT, CORISTRAN.CORISTRAN,
CORISTRAN.USAmount
FROM CORISTRAN INNER JOIN CMSTRAN ON CMSTRAN.ROCREF = CORISTRAN.[FILE NO]
WHERE CMSTRAN.DISB_UNO IN
(SELECT TOP 1 DISB_UNO
FROM CMSTRAN
WHERE CMSTRAN.TOBILL_AMT = CORISTRAN.USAmount
ORDER BY CMSTRAN.DISB_UNO)
 
A

Allen Browne

Suggestions:

1. You have CMSTRAN table in both the main query and in the subquery, so you
must alias one of them.

2. Add the primary key from CMSTRAN to the ORDER BY clause in the subquery.
That way, Access will be able to decide which one to return when there are
duplicates.

Perhaps something like this:

SELECT CMSTRAN.DISB_UNO,
CMSTRAN.TOBILL_AMT,
CORISTRAN.CORISTRAN,
CORISTRAN.USAmount
FROM CORISTRAN INNER JOIN CMSTRAN
ON CMSTRAN.ROCREF = CORISTRAN.[FILE NO]
WHERE CMSTRAN.DISB_UNO IN
(SELECT TOP 1 DISB_UNO
FROM CMSTRAN AS Dupe
WHERE Dupe.TOBILL_AMT = CORISTRAN.USAmount
ORDER BY Dupe.DISB_UNO, Dupe.ID);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Lynn Johnson said:
I can't get this to work. Even with the 'Top 1' I still got multiple
records
in one table being tagged with the ID from the other table. I've reverted
to
a select query to just try to get it return one record from each table as
a
match. Perhaps the problem is I have a many to many relationship because
the
only unique feature is the uno in one table and the auto-generated tran
number in the other. At this point, as soon as I put the 'top 1' I get no
records returned, regardless of what other criteria I take out. I also
tried
min(disb_uno) but no luck. Any other ideas?

SELECT CMSTRAN.DISB_UNO, CMSTRAN.TOBILL_AMT, CORISTRAN.CORISTRAN,
CORISTRAN.USAmount
FROM CORISTRAN INNER JOIN CMSTRAN ON CMSTRAN.ROCREF = CORISTRAN.[FILE NO]
WHERE CMSTRAN.DISB_UNO IN
(SELECT TOP 1 DISB_UNO
FROM CMSTRAN
WHERE CMSTRAN.TOBILL_AMT = CORISTRAN.USAmount
ORDER BY CMSTRAN.DISB_UNO)


Allen Browne said:
Use a subquery to get the ID value of the first match.

Here's an introduction to subqueries:
http://allenbrowne.com/subquery-01.html

Your query will end up with a WHERE Clause like this:
WHERE ID = (SELECT ID FROM ...
Click to expand...
Click to expand...
 
L

Lynn Johnson

Thanks for your feedback Allen. I did try something similar to what you
suggested after reading the document from the link you provided but that
didn't return any records. I copied the script from your latest response and
still no records returned.

At this point I'm giving up on trying to match the individual records and
seeing if I can get it to work better with summaries.

Thanks for your advice as I'm sure it will be useful for the next project.

Lynn

Allen Browne said:
Suggestions:

1. You have CMSTRAN table in both the main query and in the subquery, so you
must alias one of them.

2. Add the primary key from CMSTRAN to the ORDER BY clause in the subquery.
That way, Access will be able to decide which one to return when there are
duplicates.

Perhaps something like this:

SELECT CMSTRAN.DISB_UNO,
CMSTRAN.TOBILL_AMT,
CORISTRAN.CORISTRAN,
CORISTRAN.USAmount
FROM CORISTRAN INNER JOIN CMSTRAN
ON CMSTRAN.ROCREF = CORISTRAN.[FILE NO]
WHERE CMSTRAN.DISB_UNO IN
(SELECT TOP 1 DISB_UNO
FROM CMSTRAN AS Dupe
WHERE Dupe.TOBILL_AMT = CORISTRAN.USAmount
ORDER BY Dupe.DISB_UNO, Dupe.ID);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Lynn Johnson said:
I can't get this to work. Even with the 'Top 1' I still got multiple
records
in one table being tagged with the ID from the other table. I've reverted
to
a select query to just try to get it return one record from each table as
a
match. Perhaps the problem is I have a many to many relationship because
the
only unique feature is the uno in one table and the auto-generated tran
number in the other. At this point, as soon as I put the 'top 1' I get no
records returned, regardless of what other criteria I take out. I also
tried
min(disb_uno) but no luck. Any other ideas?

SELECT CMSTRAN.DISB_UNO, CMSTRAN.TOBILL_AMT, CORISTRAN.CORISTRAN,
CORISTRAN.USAmount
FROM CORISTRAN INNER JOIN CMSTRAN ON CMSTRAN.ROCREF = CORISTRAN.[FILE NO]
WHERE CMSTRAN.DISB_UNO IN
(SELECT TOP 1 DISB_UNO
FROM CMSTRAN
WHERE CMSTRAN.TOBILL_AMT = CORISTRAN.USAmount
ORDER BY CMSTRAN.DISB_UNO)


Allen Browne said:
Use a subquery to get the ID value of the first match.

Here's an introduction to subqueries:
http://allenbrowne.com/subquery-01.html

Your query will end up with a WHERE Clause like this:
WHERE ID = (SELECT ID FROM ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have two tables of financial transactions from two different systems
as
follows:

Table A
CompanyID
TranAmt
TRID (transaction ID from system A)
TranID (to hold Table B transaction identifier)

Table B
CompanyID
TranAmt
TranID (transaction ID generated by autonumbering in Table B to give
unique
identifer)
TRID (to hold TableA transaction identifier)

I want to compare the tables such that where the CompanyID and TranAmt
match
in both tables the TableA.TranID (identifier from other Table B) and
TableB.TRID (identifier from Table A) are updated. My update query is
working, except that there may be multiple matches of company ID and
amount
such that one record in Table A could have several matches in Table B
and
vice versa. What I want is only the first match to result in the IDs
being
updated.

I don't know VBA so I need some advice on doing this through the Access
Query Wizard or the SQL view.

Many thanks in advance for any help you can provide.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update query vs make query 5
delete Using Join Statement 4
Query to show latest review only 4
update query help 2
Senior moment on capturing all records from joined query 2
Query out transactions Continued.. 4
Query?? or something else? 5
Update Query 2

Top