Jsherman said:
I have a table that contains customer information along with a shipping code
for each customer. Some customers can have more than one shipping code so I
would like to turn that field into a multi value lookup field. That way I
only have one record per customer. I created a new table with each of the
fields I need along with the multi val look up field and have all my customer
info entered. I wanted to do an update query or insert query to populate the
values in the new look up field from the data that is in the old table. Only
problem is it doesn't look like Access will allow you to do that type of
query. Any suggestions?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
What you are suggesting violates the first rule of the Normal Form:
Each cell will be atomic. This means only one value per cell
(column/row intersect - Access calls them Fields). If your tables
conform to the Normal Form criteria your data will be easier and faster
to access.
It is easier to have multiple rows (aka Records) for each shipping
code/customer ID combination. Relational databases (what an Access DB
is) are designed to handle that set up MUCH, MUCH better than what you
suggest.
My guess is that you have combined the shipping info with the customer
info into one table. This is a mistake. You should have at least two
tables: one for the customer info, only; and one for the shipping
information, only. The two tables would be related (linked) by the
customer identification columns. E.g.:
Customers table fields:
customer_id - a number of some sort is usually best
customer_fname
customer_lname
... etc. ...
(Hint: if customers have more than one address, put that info in a
separate table and link to the Customers table by the customer_id
columns.)
Shipping table fields:
customer_id - this links to Customers table
product_id - this links to your Products table
ship_date
ship_weight
... etc. ...
Read up on relational database design in the Access Help articles (in
the Help table of contents titles, see):
Creating and Working with Databases
About designing a database
If you want to read a book on database design I usually recommend
_Database Design for Mere Mortals_ by Michael Hernandez.
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSTJaXIechKqOuFEgEQLPwACg5OnmTQ6w5JrbFcbGCeQmxuirBGkAoNZk
etqNj6plR32QdshovhxGw80d
=5lum
-----END PGP SIGNATURE-----
