Access Look-Up Table

J

J4Wilch

I have a look up table with all of the different zip codes and coresponding
market that I work in. I am trying to set up the main form that when a user
types in the zip code, it will look at the ZipMarket Table and save the
Market to my main table. I am able to get it that it displays the market, but
I want to be able to save it to the table so that I can run other functions
off of that market. Once I get thios figured out, I want to be able to pull
up the list of salesmen that work in that market. I am pretty sure I can do
the second part once I get the first one figured out.
 
L

Lynn Trapp

What is stored in the ZipMarket table? Is it the Zip code and the Market? If
so then there is not need whatsoever to store the Market in the table behind
your main form. Simply use a query to pull the information you need.
 
J

J4Wilch

Thanks Lynn
The ZipMarket table is just a two column table with the zip and market. I am
able to display the market in the form, but am I able to run another query
off of the market that is just displayed?

Is it possible to store the market in the main table?
 
J

John Vinson

I have a look up table with all of the different zip codes and coresponding
market that I work in. I am trying to set up the main form that when a user
types in the zip code, it will look at the ZipMarket Table and save the
Market to my main table. I am able to get it that it displays the market, but
I want to be able to save it to the table so that I can run other functions
off of that market. Once I get thios figured out, I want to be able to pull
up the list of salesmen that work in that market. I am pretty sure I can do
the second part once I get the first one figured out.
Click to expand...

Storing the Market redundantly in your main table is neither necessary
nor good design. If you're storing the Zip, you can always use a Query
joining the main table to the zipcode table; pull the market from that
query for your functions.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
L

Lynn Trapp

The ZipMarket table is just a two column table with the zip and market. I
am
able to display the market in the form, but am I able to run another query
off of the market that is just displayed?
Click to expand...


Yes, you should have no problem running a query like that.

Select A.Field1, A.Field2, A.Zip, B.Market
From YourTable As A Join ZipMarket on YourTable.Zip = ZipMarket.Zip;

You'll need to change the names in the query above to appropriately
represent your table.
Is it possible to store the market in the main table?
Click to expand...

Is it possible? Yes. Is it a good idea? NO. As John has pointed out, you
would be storing the value of Market redundantly. Also, if you made a change
to your ZipMarket table, that change would NOT be reflected in your main
table. By using the query approach you don't have to worry about the update.
 
Top