Relationship problem

  • Thread starter Thread starter K
  • Start date Start date
K

K

Heyas everyone...
I ve got a simple problem between a database with only 3 tables...
Clients, Order, Products.
I would like to make any type of relationship to relate Clients with
Products, but in a way that the table order could keep the information
like clients id, product id, and date in which the order was made...
in the way to have Orders like a history of clients purchases...

Can anyone help me?
 
Products don't really relate to Clients.
Products relate to Orders and Orders relate to clients.
There can be one to many Products in an Order.
A Client may place one to many Orders.
A Client may purchase the same Product on one to many Orders.

Therefore, the Client Table will be related to the Orders Header table which
will be related to the Orders Detail table which wiill be related to the
Products table.

So if what you are wanting to do is determine what products a client has
purchased over time, you have to start with the Orders Header table to find
all the orders for the Client. From there you need to extract the detail
records related to the header records to determine the products a client has
ordered.

Study the Norhtwind database that ships with Access. It should give you
some good ideas.
 
Hi K,

It sounds like what you need is a Query. Queries are great at bringing
together data from different tables and sorting that data according to your
preferences.
You can use a Form based on a query to display the results you want, you can
base a Report on a query and sort by order date, or you can just view the
query itself in datasheet view.

Here is the SQL from a sample query from the NorthWind Database

SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName,
Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate) Between #1/1/1995# And #12/31/1995#));

We can sort these orders by date by adding the following to the Query:
Order by Orders.OrderDate

This is the first few rows returned by the modified Query

Customer ID Company Name City Country Order Date
SPLIR Split Rail Beer & Ale Lander USA 02-Jan-95
CHOPS Chop-suey Chinese Bern Switzerland 03-Jan-95
LAMAI La maison d'Asie Toulouse France 03-Jan-95
QUEEN Queen Cozinha São Paulo Brazil 04-Jan-95
HUNGO Hungry Owl All-Night Cork Ireland 05-Jan-95
Grocers
WOLZA Wolski Zajazd Warszawa Poland 05-Jan-95
HUNGC Hungry Coyote Elgin USA 06-Jan-95
Import Store
MEREP Mère Paillarde Montréal Canada 09-Jan-95
SEVES Seven Seas Imports London UK 09-Jan-95
FOLKO Folk och fä HB Bräcke Sweden 10-Jan-95
QUEDE Que Delícia Rio de Brazil 11-Jan-95
Janeiro

Is this something like what you are looking for?

Hunter57
http://churchmanagementsoftware.googlepages.com/home
 
K escreveu:
Heyas everyone...
I ve got a simple problem between a database with only 3 tables...
Clients, Order, Products.
I would like to make any type of relationship to relate Clients with
Products, but in a way that the table order could keep the information
like clients id, product id, and date in which the order was made...
in the way to have Orders like a history of clients purchases...

Can anyone help me?
Click to expand...

If i understand your problem, your relationship must be:
Client (1-N) Order
Order (N-1) Product
That of course only permit one product in each order.

But if you want one order to have several products, you need another
table between the order and the Product. Your relationship should be:
Client (1-N) Order
Order (1-N) New table
New table (N-1) Product
If you want the relationship to show 1 and N, you should enforce
referencial integrity
Good luck!
 
Back
Top