Matching records for an update query

[Tracy]

I have two tables one with updated address information for some of the
records that I want use to do an update query to update second table. Both
tables have the columns in common that I can use to find duplicates. I have
FName, LName and Chapter. However in the updated address table I have the
first name only. In the other table I have some with first name and middle
initial. Can I find matching records that will only match the first 3 or 4
letters in the FName field when I join the LName and the Chapter fields? Do
I need to join all three fields and then add criteria to the FName field?

Any help you can give would be greatly appreciated.

 

[pietlinden]

I have two tables one with updated address information for some of the
records that I want use to do an update query to update second table. Both
tables have the columns in common that I can use to find duplicates. I have
FName, LName and Chapter. However in the updated address table I have the
first name only. In the other table I have some with first name and middle
initial. Can I find matching records that will only match the first 3 or 4
letters in the FName field when I join the LName and the Chapter fields? Do
I need to join all three fields and then add criteria to the FName field?

Any help you can give would be greatly appreciated.

SELECT ....
FROM tableA INNER JOIN tableB ON Left$(tableA.Field1,4)=Right$
(tableB.field2,4)

the entire field does not have to match... you can join on part of
it. Kinda unusual, but not impossible.

 

[Tracy]

Ok, I used this and received an error that I do not know how to fix-

SELECT FName, FName FROM BusinessChanges INNER Join RegisteredMembers ON
Left$(BusinessChanges.FName,4)=Right$(RegisteredMembers.FName,4)

Error - The syntax of the subquery in this expression is incorrect.
Check the sibquery's syntax and enclose the subquery in parentheses.

Can you help?

:

 

[Tracy]

Ok fixed that problem with -

(SELECT FName FROM RegisteredMembers INNER Join BusinessChanges ON
Left$(BusinessChanges.FName,4)=Right$(RegisteredMembers.FName,4))

Now I receive an error stating that the FName can match more than one table.
Both tables name the field FName. Should I rename one of the fields so they
are different?

Thanks

 

[John Spencer]

No, you don't need to rename the fields. You do need to specify the
tablename along with the field if you have the same field name in two (or
more) tables in the query.

Either BusinessChanges.FName OR RegisteredMembers.FName (whichever you want
returned)

SELECT RegisteredMembers.FName


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tracy]

That does not work either. I receive an error stating that only one record
would be returned. I should get most of the 957 records from the
BusinessChanges table. The RegisteredMembers table has more than 45k
records. I guess I need to give you more details. I am working in Access
2003. I begin with the select query. In the table listing area of the
design area I join the LName and Chapter of both tables. Then add the SQL to
the criteria of the FName field. I want the results to show for the
RegisteredMembers table so I can then update the RegisteredMembers table with
the address information from the BusinessChanges table.

Thanks for any input you can give me.

 

[John Spencer]

Post the SQL of your select query. I would expect to see something like the
following.

UPDATE RegisteredMembers as R INNER JOIN BusinessChanges as B
ON R.LName =B.LName
AND R.Chapter = B.Chapter
AND R.FName LIKE B.FName & "*"
SET R.Address = B.Address

Or perhaps like

UPDATE RegisteredMembers as R INNER JOIN BusinessChanges as B
ON R.LName =B.LName
AND R.Chapter = B.Chapter
SET R.Address = B.Address
WHERE R.FName LIKE B.FName & "*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tracy]

I still get an syntax error. You may have entered an operand without an
operator. It then highlights SET. To be sure I am doing this correctly, I
just wnter the SQL in the criteria are of the query, right?

Thanks,
Tracy

 

[John Spencer]

No, you do not write SET in criteria area. What I posted would be the
entire query. That is the SQL view of the query that you would need to run.

Can you post the original SELECT query you had (Open query in design view;
select View: SQL; Copy and paste the code)? With that I might be able to
write the update query you are looking for.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tracy]

OK, now I think I understand what you mean. Below is the SQL from the query
minus the joining and partial match for the first name.

SELECT RegisteredMembers.FName, RegisteredMembers.LName,
RegisteredMembers.Chapter, RegisteredMembers.WIndustry,
RegisteredMembers.WFunction, RegisteredMembers.WCompany,
RegisteredMembers.WAddress1, RegisteredMembers.WAddress2,
RegisteredMembers.WCity, RegisteredMembers.WState, RegisteredMembers.Wzip,
BusinessChanges.CHAPTER
FROM BusinessChanges INNER JOIN RegisteredMembers ON (BusinessChanges.LName
= RegisteredMembers.LName) AND (BusinessChanges.CHAPTER =
RegisteredMembers.Chapter);

Thanks,
Tracy

 

[John Spencer]

Sorry to take so long getting back to you

SELECT RegisteredMembers.FName, RegisteredMembers.LName,
RegisteredMembers.Chapter, RegisteredMembers.WIndustry,
RegisteredMembers.WFunction, RegisteredMembers.WCompany,
RegisteredMembers.WAddress1, RegisteredMembers.WAddress2,
RegisteredMembers.WCity, RegisteredMembers.WState, RegisteredMembers.Wzip,
BusinessChanges.CHAPTER
FROM RegisteredMembers INNER JOIN BusinessChanges
ON ( RegisteredMembers.LName=BusinessChanges.LName )
AND (RegisteredMembers.Chapter=BusinessChanges.CHAPTER)
AND (RegisteredMembers.FName Like BusinessChanges.FName & "*")

If that works, then the update query would look something like

UPDATE RegisteredMembers INNER JOIN BusinessChanges
ON ( RegisteredMembers.LName=BusinessChanges.LName )
AND (RegisteredMembers.Chapter=BusinessChanges.CHAPTER)
AND (RegisteredMembers.FName Like BusinessChanges.FName & "*")
SET RegisteredMembers.WAddress1 =BusinessChanges.WAddress1
, RegisteredMembers.WAddress2=BusinessChanges.WAddress2
, RegisteredMembers.WCity=BusinessChanges.WCity
, RegisteredMembers.WState=BusinessChanges.WState
, RegisteredMembers.Wzip=BusinessChanges.Wzip

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tracy]

John,

Sorry, this is the first time I have been able to get back to this project.
Your code worked perfectly. Thank you so very much. You are a life saver.

Sincerely,
Tracy