This has to be easy for someone but not me

[dick23]

I have 2 tables, customer info and products info. I put 2 drop down boxes in
a form, one box for customers and one for products. I am trying to pick a
customer from customer table, a product from the product table and put them
together as one customer with one product. Once that is accomplished I would
like to hook up one customer with more than one product.
I am using access 2003
Any help is appreciated, I apparently just don't get it

 

[Bob Quintal]

I have 2 tables, customer info and products info. I put 2 drop
down boxes in
a form, one box for customers and one for products. I am trying
to pick a
customer from customer table, a product from the product table and
put them together as one customer with one product. Once that is
accomplished I would like to hook up one customer with more than
one product. I am using access 2003
Any help is appreciated, I apparently just don't get it

What you describe is a many-to-many relationship, which must be
handled by using a third table in the database.

This third table contains the references to the customer ID and the
Product ID, plus additional fields such as quantity ordered.
Tables:
Customers: Customer_ID, Name, Address, etc
Products: Product_ID, Description, Price, Supplier ID
Customer_Product: Customer_ID, Product_ID, qty_Sold.

You also may want to get a little more sophisticated, and have a
purchase orders table, with the purchase order table containing the
common details of the Order such as date, customer, etc. The purchase
order ID would then replace the customer ID in the other table, but
you can get the customer info from the Purchase order table easily

That requires four tables.
Customers
Products
Orders: Order_ID, Customer_ID, dateOfSale, etc.
Order_Products: Order_ID, Product_ID, qty_Sold

 

[dick23]

Thank you Bob
I am sorry but I have been trying to work with access for years and there is
a huge gap in my understanding. I have to set up a relationship between all
3 tables before I can do anything else, correct?
If you don't want to be bothered with my questions, I understand because I
will ask until you finally don't reply

 

[Bob Quintal]

Thank you Bob
I am sorry but I have been trying to work with access for years
and there is a huge gap in my understanding. I have to set up a
relationship between all 3 tables before I can do anything else,
correct? If you don't want to be bothered with my questions, I
understand because I will ask until you finally don't reply

First you need to create the tables.
You can then create relationships in several ways.

The best way is to use the relationships view. You load the tables
you want to relate and drag the field(s) from one table and drop on
the field in the related table. Field names don't need to match, but
data type and size must match. You can set referential integrity,for
each relationship at this point.

Those relations are persistent, and will be used whenever you create
a new query and/or new forms and reports.

You can also just define relationships when you build the query, form
or report. These are only in effect within the scope of that object,
but do not exist in any new object unless you re-create them.

In either method, one side of the relationship must be field or a
group of up to 10 fields, that contains unique data.That's so the
query can know unambigiously what row to return

The other side uses a foreign key, with many copies of the key data
that identifies the one row in the first table.

A many to many relationship uses a third table with two foreign keys
(which together form a primary key for this table) that each point to
one row in the respective related table.

As to answering questions,,it's no bother. If I don't answer it's
probably because I'm away on business.

Don't be afraid to post new questions to the group. I'm not the only
person with answers, many of us love to help..

Q