Copy A Tables Field Data To Another Field In Same Table

  • Thread starter Thread starter Chris Moore
  • Start date Start date
C

Chris Moore

I have a name & address table. The zip code is zip+4 data. I need to keep
that but I also need a zip that contains only the 1st 5 digits. I have
created a 2nd field called ZIP5 but how do Icopy the contents of zip+4 over
to ZIP5? I have 1,000,000+ records so it must be automated somehow (SQL,
macro, whatever).

Thanks

Chris
 
Chris,
Don't bother.
The same code you would use to add another field to the table, duplicating
the data, you could use to print the 5 number Zip codes when you need to.

In a Report control:
= IIf(Len([Zip])>6,Left([Zip],5) & "-" & Right([Zip],4),Left([Zip],5))

The expression works whether or not the Zip code is 5 or 9 digits, and
whether or not the "-" is stored with the data.

You would use the same expression in an Update query, but what happens if
someone's Zip code changes? Now you have to update 2 fields.
It's not a good idea.
The idea of a relational database, is that fields contain the smallest level
of needed data, and that data not be duplicated.
 
I have a name & address table. The zip code is zip+4 data. I need to keep
that but I also need a zip that contains only the 1st 5 digits. I have
created a 2nd field called ZIP5 but how do Icopy the contents of zip+4 over
to ZIP5? I have 1,000,000+ records so it must be automated somehow (SQL,
macro, whatever).

Well... DON'T. It's an utter and absolute waste of space.

If you need to see the five-digit zip anywhere, just extract it on the
fly in a Query by typing

Zip5: Left([zip], 5)

in a vacant Field cell.

This calculated field can then be used for reporting. It *could* be
used for sorting and searching too, but it would be inefficient since
you can't index it; if you want to search by zipcode use a criterion
of

LIKE [Enter zip:] & "*"

to take advantage of the index on the existing zip field.
 
This is not for display purposes. I use that technique everywhere else I
need to just display the data. I need this already defined as 5
characters to be used in a query. It makes a fairly big difference
between using WHERE LEFT(ZIP, 5) = "12345" vs ZIP = "12345".

Thanks to all.

Chris


I have a name & address table. The zip code is zip+4 data. I need to
keep that but I also need a zip that contains only the 1st 5 digits. I
have created a 2nd field called ZIP5 but how do Icopy the contents of
zip+4 over to ZIP5? I have 1,000,000+ records so it must be automated
somehow (SQL, macro, whatever).

Well... DON'T. It's an utter and absolute waste of space.

If you need to see the five-digit zip anywhere, just extract it on the
fly in a Query by typing

Zip5: Left([zip], 5)

in a vacant Field cell.

This calculated field can then be used for reporting. It *could* be
used for sorting and searching too, but it would be inefficient since
you can't index it; if you want to search by zipcode use a criterion
of

LIKE [Enter zip:] & "*"

to take advantage of the index on the existing zip field.
 
Chris Moore said:
This is not for display purposes. I use that technique everywhere else I
need to just display the data. I need this already defined as 5
characters to be used in a query. It makes a fairly big difference
between using WHERE LEFT(ZIP, 5) = "12345" vs ZIP = "12345".

But there should be relatively little difference if you used. . .

WHERE ZIP LIKE "12345*"

My understanding is that as long as the wildcard is not at the beginning that your
index on the extended Zip field will still be used for the starting character string.
 
Back
Top