John Clark
John.Clark at niagaracounty.com
Fri Mar 12 07:56:11 CST 2004
I have done a program, in the past, using the FMS zip code tables. This was updated once a month, and it included Canadian, US, and 'territories'. I don't want to go that large here, because the residences of the people in this database will be limited to 12 towns and 3 cities--roughly 19 zip codes. So I was just wondering if it was OK to have a single field table, or if I should include the zips with the towns. The reason I was seperating the zips from the towns was because some areas have multiple zips--for instance, Niagara Falls has 3 zip codes, not including a separate one for Niagara University--and I thought that might cause a problem. As a sidebar--sort of--why can't the zip itself be a primary key? >>> martyconnelly at shaw.ca 3/11/2004 9:29:46 PM >>> There are three or four good web services like USPS that provide this info, live via a soap call, of course you need an internet connection. You can interrogate these services from Access with just XML and a SOAP envelope over HTTP. You dont need all the soap toolkit jazz unless you want to write the proxy classes. They will also verify that the address is valid too. Such as street name spelling and number range. You can also buy the info via quarterly CD's. John W. Colby wrote: >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 > > > > > -- Marty Connelly Victoria, B.C. Canada -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com