Table Analyzer Wizard problem

[Linda]

I used the table analyzer wizard on a flat database file containing name and
address information. It suggested creating a second table for the city, state
and zip code data. I did this. It creates a lookup table and works ok.
Selecting the zip code from the list fills in the city and state fields. My
problem is that I occasionally need to add records that have different zip
codes and cities than those in the zip code lookup table. It will not let me
do this. The relationship is many to one. I tried turning off referential
integrity, but this did not make a difference. I am now thinking that
dividing this information into separate tables was a mistake.
Is there a way to make this work?

 

[Al Campagna]

Linda,
Given the fact that your just collecting Name and Address info, I don't see the reason
for a realtionship between your Adresses and the Zip table. It's just a value provider
for your ZipCode field on the Addresses form. Your just trying to "automate the City,
State, Zip entry on the form. You don't need to, nor should you, "relate" Zips to
Addresses.

IMHO... the table analyzer can cause more problems than it's worth... particularly for
new users.

If you had multiple Phone Nos for each Address, that would be a related table, *One to
Many* (One Address to Many PhoneNos)
And, you would build a subform on the Address form to display all the related PhoneNos.
I don't see ZipCodes as needing any of that.

Most probably, your Zip "list" (I'd use a combo) is set to LimitToList = Yes, so you
can't eneter a Zip not in the supporting Zip table... via the combo.
You'd need to have the NotInList event trigger code to open the supporting Zip table
and allow you to enter the New Zip and Associated City and State... return to the Adress
form, and select the new entry from the requeried combo.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Al Campagna]

Linda,
By the by, I have a V97 and V2003 file on my website below that demonstrates how to use
the NotInList to allow the user to add the new item to the supporting table, and return to
the form to complete the entry (Not In List ComboBox). Should do the trick for you.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Linda]

Thanks, Al, I will give this a try!
--
Linda

Linda,
By the by, I have a V97 and V2003 file on my website below that demonstrates how to use
the NotInList to allow the user to add the new item to the supporting table, and return to
the form to complete the entry (Not In List ComboBox). Should do the trick for you.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Linda]

I looked at your notinlist example, and I think that is much more than what I
need. So back to fixing this. I will add the city, State and Zip code fields
back to the main table and eliminate the lookup zip code table. Is there a
way to do this where I don't have to enter the city, State and Zip codes for
each record manually?

Once that is done, is there any way to set up the zipcode field so that it
completes the data in the city and state fields? Or do I just make each field
an independent lookup list? This opens up the possibility of incorrect data
being entered ... such as selecting the wrong zipcode for a city.

btw I am using Access 2007.

 

[Al Campagna]

Linda,
Also on my site, I have a sample A97 and A2003 file called Combo Updates Multiple
Fields.
It... when customized for your situation, would allow the user to select a Zip, and the
combo would fill in the City and State from the Zip table into your Address form fields.
If you have difficulties with just taha part... get back with a post on that aspect.
Good Luck,
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."