adding post/zip codes mk2

J

jack

I have created what started out as a very small database to track salesreps
movements, but the boss wants more. Originally it just had names and
suburbs, but now i have to add post/zip codes to all entries, and as there
are 4000 entries i want to do it automatically. i guess an update query or
something but my skills are limited in this area any guidance will be much
appreciated
 
J

jack

basically, i have a table called "Leads", with fields called "date",
"Customer Name", "Suburb", and now the new one "Post Code" (Zip code to
Americans). I also have a table called "Post Codes", that has two fields
called "Suburb" and "Post Code". The Leads table currently has no data in
the Post Code field, but the Post Codes table has a complete list of all
Suburbs & their corresponding 4 digit Post Codes . I want to fill in the
data field for Post Code in the Leads table to match the suburb. I hope that
makes sense.
 
J

jack

that looks great Arvin, and i really appreciate the help, but how do i use
it. my experience with Access is limited
Arvin Meyer said:
This ought to do it:

UPDATE tblLeads INNER JOIN [tblPostCodes] ON tblLeads.Suburb =
[tblPostCodes].Suburb SET tblLeads.[Post Code] = [tblPostCodes].[Post
Code];
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


jack said:
basically, i have a table called "Leads", with fields called "date",
"Customer Name", "Suburb", and now the new one "Post Code" (Zip code to
Americans). I also have a table called "Post Codes", that has two fields
called "Suburb" and "Post Code". The Leads table currently has no data in
the Post Code field, but the Post Codes table has a complete list of all
Suburbs & their corresponding 4 digit Post Codes . I want to fill in the
data field for Post Code in the Leads table to match the suburb. I hope that
makes sense.
Click to expand...
Click to expand...
 
A

Arvin Meyer [MVP]

Make sure the field and table names match yours, and paste it into the SQL
window od a new query.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

jack said:
that looks great Arvin, and i really appreciate the help, but how do i use
it. my experience with Access is limited
Arvin Meyer said:
This ought to do it:

UPDATE tblLeads INNER JOIN [tblPostCodes] ON tblLeads.Suburb =
[tblPostCodes].Suburb SET tblLeads.[Post Code] = [tblPostCodes].[Post
Code];
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


jack said:
basically, i have a table called "Leads", with fields called "date",
"Customer Name", "Suburb", and now the new one "Post Code" (Zip code to
Americans). I also have a table called "Post Codes", that has two fields
called "Suburb" and "Post Code". The Leads table currently has no data in
the Post Code field, but the Post Codes table has a complete list of all
Suburbs & their corresponding 4 digit Post Codes . I want to fill in the
data field for Post Code in the Leads table to match the suburb. I hope that
makes sense.
For us to help, you need to tell us what you have: TableNames, FieldNames,
etc. ewhat data is available, and where it is.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I have created what started out as a very small database to track
salesreps
movements, but the boss wants more. Originally it just had names and
suburbs, but now i have to add post/zip codes to all entries, and as
there
are 4000 entries i want to do it automatically. i guess an update
query
or
something but my skills are limited in this area any guidance will be
much
appreciated
Click to expand...
Click to expand...
Click to expand...
 
Top