[AccessD] question on normalization

John Clark John.Clark at niagaracounty.com
Fri Mar 12 07:56:01 CST 2004


I would be very interested in seeing a sample David. This may work for
me.
 
Thanks!
 
John W Clark

>>> DMcAfee at haascnc.com 3/11/2004 5:16:16 PM >>>

It was given to me many years ago, and I've just maintained it. I can
give you a sample if you need.

Our company has purchased "Perfect Address" software which can be
called from Access. This is a system that corrects addresses. they put
out updates monthly (IIRC).

I use it in conjunction with my table.

David

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W.
Colby
Sent: Thursday, March 11, 2004 1:55 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] question on normalization


David,

Did you fill in this table with which Zips were in which town etc.?  Or
is
there a standard db out there somewhere with this stuff?

John W. Colby
www.ColbyConsulting.com 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of David McAfee
Sent: Thursday, March 11, 2004 3:49 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] question on normalization


I our last system, we had a Zipcode table with 5 fields (and 45K+
records):
ZipID (PK Autonumber)
ZipCode (text,5)
City (text, 30)
State (text, 2)
DefaultCity (yes/no)

I would look up a city based on zipcode entry. One of three results
could
happen:

0 records returned: Prompt to check if zipcode entry (later evolved
into
adding new city)
1 record returned: populate city and state onto form
>1 record returned: pop up a mini screen with a list box showing all of
the
cities using that zipcode. The default city is selected with the OK
button
having focus.

I would store every part of the address together for a given address.

In our new system, we have the similar set up as above, but we only
keep the
ZipID (ID, for the city, state and Zip) with the address.
The extra joins needed now are a bit of a pita, so I could imagine how
much
more it would be to use a junction table containing an CSZid, CityID,
Stid &
ZipID.

I guess it all boils down to what level of normalization do you want to
go?
3rd, 4th?

Oh, btw, are you only dealing with US addresses or INTL addresses too?
Because that will add some twists too ;)
David



-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Clark
Sent: Thursday, March 11, 2004 12:02 PM
To: accessd at databaseadvisors.com 
Subject: [AccessD] question on normalization


quick question:

I have a program that will be populated with a 'regional' group of
people--this meaning that everyone's address will be within this
county.
So I am thinking that I can speed up entry, by using a drop-down for
zip
codes and filling the town name from that. My question is, would it be
proper to have a separate table for town and another for zip code,
even
though zip code would be a single field table? The town table would
have
three tables, with one being a link to the Zip Code table, and the Zip
Code table would have a single field--"Zip".

Thanks

John W Clark


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 




More information about the AccessD mailing list