Many to Many relationship with tables 2

[mattymatt79]

Well I'm pretty rusty on the concepts of many to many relationships, but I'm assuming thats what I want to create for this situation.

What I'm creating is a database to track customers that enter my place of buisness. So far so good, but what I'm having issues with is 2 of the major things that I'm trying to track.

A Customer can visit multiple dealers : and a dealer can have multiple customers.

Also a Custoemr can visit multiple days and a day can have multiple customers.

What I'm not figuring out is how to relate each to situations back to back.

Due to the fact that I need to be able to see what happened based on date, as well based on customer, and salesman.

Any suggestions as to where to start?

 

[r937]

see if you can adapt this to access --

create table customers
( id integer not null primary key 
, name varchar(99)
)

create table dealers
( id integer not null primary key 
, name varchar(99)
)

create table customervisits
( customerid integer not null 
, dealerid integer not null 
, foreign key ( customerid ) 
     references customers ( id )
, foreign key ( dealerid ) 
     references dealers ( id )
, visited datetime not null
, [b]primary key ( customerid, dealerid, visited )[/b]
)

r937.com | rudy.ca

 

[mattymatt79]

I'm guessing thats SQL coding? or some kind of variant of that?

ok, I get that I'm creating 3 tables,
PK In Customers is ID (Autonumber in Access)
PK In Dealers is ID

Visits would be the intermediary table using the ID from Customers and Dealers as foriegn keys?


As of now I currently have the 3 tables

tblCustomerInfo (PK ID)
tblSalesMan (PK ID)
tblVisit (now this is where I think my problems are comming, I put the PK of this table as ID, primarily thinking because well I needed a way of creating serialized records in this one table, putting the 3 tables together)



As of right now I have a one to many on tblCustomerInfo and tblSalesman and a one to many on tblVisit to tblCustomerInfo

 

[r937]

the PK of tblVisit should be three columns -- customer ID, dealer ID, and visit date/time

r937.com | rudy.ca

 

[mattymatt79]

I'm having a hard time understanding what you mean? Access only allows me to label one primary key.

I made the primary key of tblVisit ID

 

[PHV]

Access only allows me to label one primary key
Access allows a composite (multi-field) key as PK.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mattymatt79]

I clicked all three fields in the tblVisit and made them primary keys, so now I just need to make the relationship to work,

I never knew I could do that in access with the multifield PK. Thanks for that.

 

[r937]

tblVisit doesn't need an ID

(it doesn't need the "tbl" prefix either, but that's a separate rant for a different day )

r937.com | rudy.ca

 

[mattymatt79]

Well if visit doesn't need an ID, then how would you create the relationship?
I figured it would need to have a serialized document on each entry so it knows its place, making it the PK

 

[Golom]

... how would you create the relationship?

You would join to the customers table on CustomerID and to the Dealers table using DealerID.

The PK in the Visits table is the multi-field key (CustomerID, DealerID, VisitDate) where
[tt]
CustomerID --> A foreign key into the Customer table
DealerID --> A foreign key into the Dealer table.
VisitDate --> The third part of the key that allows
multiple entries (one per day) for a
Customer/Dealer combination.
[/tt]
It does not need (nor should it have) a separate but unrelated ID field so that it "knows its place." "Its place" is determined by the composite key.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[r937]

golom, great answer, thanks for popping in to this thread and helping me out -- star for you

you know, you really should be writing articles ;-)

r937.com | rudy.ca

 

[mattymatt79]

Thanks guys for all the very very valuable info on this little project of mine, I think I got most of it figured out and you guys definetly helped.

 

[lespaul]

and for more information, read the link below.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design