Many to Many relationships (1 Viewer)

[mking]

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?

 

[jeremie_ingram]

Many to many is never the answer. You need a junction table. From the description, I would call it tblVisits. The table would contain a key from both the customers and dealers. The new table would assign a unique ID to each visit, and contain the customers ID as well as the ID for the dealer they visited. You could also use this table to track the date/time that the visit occurred.
The many to many idea looks tempting at first, but when you get to generating reports and creating forms you soon realize that it is not the way to go.

tblDelaers
DealerID (Primary Key)
DealerName
DealerAddress
DealerPhNum

tblCust
CustID (Primary Key)
CustFname
CustLName
CustStAdd
CustCity
CustState
CustZip

tblVisits
VisitID (Primary Key)
Cust (lookup from the customer table)
Dealer (lookup to the dealer table)
VisDate
VisTime

Let me know if this makes sense or not.

 

[mking]

Currently this is how I have it setup

tblCustomerInfo
ID Autonumber
Prefix
FName
LName
Suffix
Street
City
State
Zip
Phone
DateVisit
TimeVisit
Description
SalesmanName
Uptype

tblVisit
DateVisited
TimeVisited
TypeVisit
SalesmanName

tblSalesman
ID
SalesmanName
SalesmanNumber (PK)

 

[KeithG]

I would add the customer ID to tblVisits and I would replace the salesman name field in tblVisits with Salesman ID.

 

[jeremie_ingram]

You’re on the right track, but have to correct a few things. I have attached a sample for you to look at. In the tblVisits, look at the fields and their relationships.
The tblVisits is linked to the other two, which normalizes the database and eliminates the need for many to many relationships.

Let me know if this helps

 

[mking]

Ok I made the changes like you said replacing the fields.
Now my question is how to relate the fields making them now multiple 1 to 1 relationships?

 

[mking]

didn't see the attachment

 

[mking]

Oh, that looks good, however; the other thing I'm needing to see is that when a salesman has a customer so you can see what customer was dealt with.

1 customer can have many salesman
1 salesman has many customers

customers can have many dates
dates have many customers

Does this make sense or am I not explaining it right?

 

[KeithG]

In table visits you are going to want one to many relationship. Because one customer can have many visits and one salesman can help with multiple visits.

 

[mking]

Well try as I might access will only allow me to create a 1:1 or an intermediate relationship from tblVisits to tblSalesman no matter what fields I try to use.

 

[KeithG]

Can you post your Db and I will have a look?

 

[mking]

Yeah, here it is

 

[KeithG]

You need to create a field in tblVisit called VisitID and make it the primary key. Then you should be able to create your relationships. I would of made an example but it would not let me because you posted a replica.

 

[mking]

Even creating the field VisitID and making it the PK it still only allows for intermediate and 1:1

All itll let me open is the replica, I can try zipping the entire folder and seeing if you could open it that way.

 

[jeremie_ingram]

When you create the fileds in tblVisits, use the look up feature. Then go into relationships and edit it to have referental integrity. It will then display as one to many.
You can edit the field lookup as well to change the display of the data from 2 fileds to one.

 

[mking]

I used the lookup option for the salesmanID, should I be using it as well for the customerID and the date as well?

 

[jeremie_ingram]

Personally I would not do it for the dates, but the customer is a good idea. I know that you will have many reoccurrences of dates & times within the Visits table but that is expected. I would have the date/time entries in the VISITS table, and two lookup fields to customers and salesperson.

For normalization purposes, you would want to eliminate as many reoccurrences of data as possible ~ hence the relationships. Since the majority of the records in the visits table will have a unique date & time I would not attempt to link them to another table.

 

[mking]

Ok ninja edit:

I took out the date/time out of the customer table and put them into the visit table and the visit table alone.

I added CustID to the Visit table and created a lookup from Visit to Customer, and Visit to Salesman


things are starting to take shape I must say, thank you for all of your help

 

[jeremie_ingram]

Glad to have been of assistance. Let me know how it all comes out for you. Sounds like you have it straightened out.