update, lookups, and data type

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sorrywm - 03 Nov 2005 17:04 GMT

I apologize if this type of question has been posted before, but I had
difficulty finding it in the threads.

My database has a tblAddr in which county information is stored using
CountyID using a lookup from tblAddrCounty. I would like to update this table
using a table I generated that includes counties by zip codes (zip is also a
category in tblAddr). My questions are:
1. How do I ensure that zip codes with "0" at the beginning do not show up
as 4 digits when I import this table (the data type for zip in tblAddr is
text)?
2. How can I populate the countyID field in the table I created with the
correct ID based on the county? I tried to set up a lookup from
tblAddrCounty, but this did not work.
3. How should I set up the update query to update records in tblAddr with a
blank for countyID and a zip corresponding to the correct county? I am more
familiar with design than SQL view.

Reply to this Message

John Vinson - 03 Nov 2005 20:29 GMT

>I apologize if this type of question has been posted before, but I had
>difficulty finding it in the threads.
[quoted text clipped - 3 lines]
>using a table I generated that includes counties by zip codes (zip is also a
>category in tblAddr). My questions are:

You might want to consider getting rid of your Lookup Fields
altogether. They are a source of a slight benefit (it's a bit easier
to create a combo box on a Form) and many major hassles; see
http://www.mvps.org/access/lookupfields.htm for a critique.

>1. How do I ensure that zip codes with "0" at the beginning do not show up
>as 4 digits when I import this table (the data type for zip in tblAddr is
>text)?

Where are you importing FROM? If the field is Text, then the text
string "02138" should not lose its leading zero. If (for some reason)
it does, you can run an Update query updating Zip to

Right("00000' & [Zip], 5)

if the table contains only five-digit Zips.

>2. How can I populate the countyID field in the table I created with the
>correct ID based on the county? I tried to set up a lookup from
>tblAddrCounty, but this did not work.

What's the structure of tblAddrCounty? A county doesn't have "a zip
code" or, often, even a defined range of zip codes.

>3. How should I set up the update query to update records in tblAddr with a
>blank for countyID and a zip corresponding to the correct county? I am more
>familiar with design than SQL view.

IF - and I'm guessing here - you have tblAddrCounty with fields
CountyID and Zip, with Zip as the Primary Key, create a query joining
your tblAddr to tblAddrCounty by Zip. Change it to an Update query.
Update tblAddr.CountyID to

[tblAddrCounty].[CountyID]

The brackets are required. BACK UP YOUR DATABASE FIRST, and post back
with field definitions for your two tables if I've misunderstood your
structure!

John W. Vinson[MVP]

Reply to this Message

sorrywm - 03 Nov 2005 21:56 GMT

> >I apologize if this type of question has been posted before, but I had
> >difficulty finding it in the threads.
[quoted text clipped - 8 lines]
> to create a combo box on a Form) and many major hassles; see
> http://www.mvps.org/access/lookupfields.htm for a critique.

I did not design the database, and I'm a bit confused about the distinction
between Lookup Fields and combo boxes. Our database has many line item tables
with primary keys such as SalutationID, SuffixID, CountyID, JobID,
AgeRangeID, etc. The table tblAddr has a field called "CountyID," which has
Display Control: Combo Box. I called this a "lookup" because it links to the
line item table tblAddrCounty, which contains only County and CountyID.

> >1. How do I ensure that zip codes with "0" at the beginning do not show up
> >as 4 digits when I import this table (the data type for zip in tblAddr is
[quoted text clipped - 7 lines]
>
> if the table contains only five-digit Zips.

I am importing from an Excel file, and when I change the cell format to text
in Excel the leading zeros disappear, so apparently this is an Excel problem
and not an ACCESS problem.

> >2. How can I populate the countyID field in the table I created with the
> >correct ID based on the county? I tried to set up a lookup from
> >tblAddrCounty, but this did not work.
>
> What's the structure of tblAddrCounty? A county doesn't have "a zip
> code" or, often, even a defined range of zip codes.

tblAddrCounty contains only County and CountyID (primary key). tblAddr
contains addrID (primary key), zip, and CountyID, as well as several other
fields.

> >3. How should I set up the update query to update records in tblAddr with a
> >blank for countyID and a zip corresponding to the correct county? I am more
[quoted text clipped - 10 lines]
> with field definitions for your two tables if I've misunderstood your
> structure!

I am working on a copy of my database, so there should be no problem with
backing it up. I'll try to fix up the Excel file and see if I can figure out
the Update from there. Thank you for your help!

> John W. Vinson[MVP]

Reply to this Message