Re: Matching records for an update query
- From: Tracy <Tracy@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 6 Sep 2007 10:30:01 -0700
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
"John Spencer" wrote:
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" <Tracy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0D8A4596-7369-4BA6-B6B9-C55FCE53D0E0@xxxxxxxxxxxxxxxx
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" wrote:
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" <Tracy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CE0F3A2D-4819-4890-ACCB-F9CB6DBFF31F@xxxxxxxxxxxxxxxx
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" wrote:
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" <Tracy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7DD53813-E5FC-46FD-9A03-FEC05B3C04DE@xxxxxxxxxxxxxxxx
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" wrote:
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" <Tracy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7CEE614E-0E78-41F0-83FF-C69BA88FD922@xxxxxxxxxxxxxxxx
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
"Tracy" wrote:
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?
"pietlinden@xxxxxxxxxxx" wrote:
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.
- Prev by Date: Re: Message box pop up when no reults
- Next by Date: Re: How to ID a table source being used in a Union Query
- Previous by thread: Re: CONCATENATE/TRIM WITH WHITE SPACE (RETURN KEY)
- Next by thread: Re: How to ID a table source being used in a Union Query
- Index(es):
Relevant Pages
|
