AutoFill on a Relationship?

[Guest]

Hello all,
I seem to be having a bit of difficulty in setting up a relationship
between two tables, and I was looking to see if I may ask for some
assistance. My knowledge of Access is elementary at best, and I haven't coded
anything since BASIC (which is all but forgotten now), just for a frame of
reference. In a nutshell...

Table A - A customer index, broken down by stock number, name, address, etc,
ending with the zip code and a geographical region code (each region
encompasses several zip codes).

Table B - A simple listing of all the local zip codes with their respective
region codes filled in beside them.

I was trying to set this up so that when I add a new customer entry in
Table A, when I fill in the zip code, it would automatically reference Table
B for the pertinent zip code and fill in the region code. I've tried using a
relationship to no effect, and a query to even less effect; I'm just quite
simply stumped, and don't know how to proceed or even what questions to ask.
Any assistance that can be provided would be greatly appreciated.

 

[Jeff Boyce]

It sounds like you might be trying to do this directly in the tables.
Don't!

Access uses tables to store data, and forms (and reports) to display it.
Use a form and a combo box for the zip code.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hello there,
Thanks for the advice. I keep seeing combo boxes mentioned, but don't
know of them personally, so I'm looking over the relavent training page on
the Office site. My initial reaction, though, seems to think that this would
be good on a 'from now on' circumstance, but what do I do with the customer
entries that are already present? Is there any way to get the region code for
those, short of manually looking them up?

 

[John Vinson]

Hello there,
Thanks for the advice. I keep seeing combo boxes mentioned, but don't
know of them personally, so I'm looking over the relavent training page on
the Office site. My initial reaction, though, seems to think that this would
be good on a 'from now on' circumstance, but what do I do with the customer
entries that are already present? Is there any way to get the region code for
those, short of manually looking them up?

You don't need to start from scratch.

Your TableA should NOT have a Region Code field. It's redundant, since
the region code is stored in the zipcode table.

Create a new Query joining TableA to TableB by Zip code.

Pull all the fields you want to see from TableA, and the region code
corresponding to the record's zip code from TableB.

This Query can now be used as the basis for a form, a report, or
whatever you wish. It's not necessary to store the region in two
different places!

John W. Vinson[MVP]

 

[Guest]

Thank you! That worked wonderfully; it's nice to see that folks are willing
and able to help the total rookies with stuff like this.