Need help in understanding Relationships

[Goat.Cheese]

Recently had to start working with access 2010. Im a layman on a deadline so, I need to ask for some aid.

For a project i have been tasked to create relationship tables which link a pre-given list of company names to the specific items they purchase. I am given the names of said companiess, along with a list of their purchases for the past 3 months. Try as I might I am unable to make the Relationships arrows point correctly.

And thats it in a nutshell. Any help would be appreciated.

 

[recyan]

Till some one comes along, thinking out loud :
One CUSTOMER can PURCHASE many ITEMS.
One ITEM can be purchased by many CUSTOMERS.
Is the concept of ORDER also involved i.e. An ORDER can have many ITEMS in it ( A CUSTOMER can purchase many ITEMS against a single ORDER (Purchase))?

Thanks

 

[Goat.Cheese]

Till some one comes along, thinking out loud :
One CUSTOMER can PURCHASE many ITEMS.
One ITEM can be purchased by many CUSTOMERS.
Is the concept of ORDER also involved i.e. An ORDER can have many ITEMS in it ( A CUSTOMER can purchase many ITEMS against a single ORDER (Purchase))?

Thanks

Your post looks like a riddle, but for the life of me i dont get it.
The premise of the entire thing is to simulate a given list of buyer names and connect it to a company's nomenclature of some 5000 individual items.

One customer could have purchased many items - we are assuming that the list contains companies which shop electric hardware regularily and in large quantities.

Likewise, a single item could've been bought by many different people, say something more common, such as a lightbulb. Everybody likes lightbulbs.

I dont quite get the "order" concept as you explain it.

Lets say that we sell said electric hardware to three companies: Larry INC, Moe LTD and Curly UTD

Larry for the past 3 months has bought Lightbulbs, electric outlets, gas detectors, wall fixtures
Moe , his arch-rival, in turn buys Lightbulbs, Circuit Breakers, Rail strips, smoke alarms
Curly as the newcomer starts small with Lightbulbs, electric outlets, cables, batteries

So what i need to do is create a relationship table where on one side we have Larry, Moe and Curly and on the other, say, 1000 items that my imaginary company sells.
I need to make the relation arrows point to all 3 for the light bulbs, the ones for outlets - to Larry and Curly and a lone relation arrow for the other sales ( which do not overlap between buyers)

 

[recyan]

I dont quite get the "order" concept as you explain it.

As an example :
Say Larry places an order / purchases on 5th July for Light Bulbs.
Again on 9th July Larry places an order / purchases on 5th July for Light Bulbs, Gas Detectors.
So basically these are 2 different Orders / Purchases.
Each Order / Purchase will have a unique OrderID / PurchaseID & in turn each OrderID / PurchaseID will have one or many Items, Quantity Purchased, etc.
If this is the way, you want things handled, then we should have, perhaps, something roughly like below :

tblCustomers
CustomerID - PK
.......

tblItems
ItemID - PK
......

tblOrders
OrderID - PK
OrderDate
CustomerID - FK
..........

tblOrderDetails
OrderDetailsID - PK
ItemID - FK
QuantityOrdered
...........

Thanks

 

[jdraw]

Here are a number of free video tutorials related to database design concepts.

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

 

[Bryan]

I need to make the relation arrows point to all 3 for the light bulbs, the ones for outlets - to Larry and Curly and a lone relation arrow for the other sales ( which do not overlap between buyers)

If by "relation arrows" you are referring to the arrows that point between tables in the Access relationship view, I think you may be misunderstanding their purpose.

For a number of reasons generally set forth under rules termed Normalization, databases are organized into tables that are for the most part related to each other. Once you have properly established your tables, you configure the relationships (the arrows you referred to, I think) so that Access knows how the data in the tables relate to one another.

When you talk about arrows pointing to data for sales that do not overlap is not DIRECTLY related to establishing relationships in Access. The relationships are crucial, but the identification of that sort of data comes after you write queries to filter that data in your database. So when you talk about looking for sales that don't overlap, that will come from a query that you'll construct AFTER your tables have been defined and relationships among the tables established.

@recyan - Shouldn't your suggested tables have a relationship between tblOrders and tblOrderDetails?

tblCustomers -> tblOrders -> tblOrderDetails <--> tblItems ???

 

[Goat.Cheese]

<- Short on time, wanted to say thanks for the asnwers, will read them later and respond

 

[recyan]

@recyan - Shouldn't your suggested tables have a relationship between tblOrders and tblOrderDetails?

tblCustomers -> tblOrders -> tblOrderDetails <--> tblItems ???

Absolutely right, Bryan. Missed out. Thanks for the correction.

Thanks

 

[Goat.Cheese]

Ok, i got a grasp on that particular issue, now I have another : ). But that is a story for another thread.