Building a customer database - how do i set it up? - relationships??

[faciaface]

I'm building a customer database for personal use at work. I currently have about 6 different spread sheets and a MAS 90 system that I use to keep track of everything and I'd like to get everything narrowed down into Access. I learned this stuff 10 years ago, and I've been watching tutorials but I just can't figure out how to get the relationships to work right.
Basically, here's how our customers are set up...

Generally speaking, electrical supply houses are our customers - Eck Supply, Steiner Electric, Viking Electric ....
Each of those businesses has multiple branches and is treated as a separate customer Eck SUpply is located in Ashland, Blacksburg, Charlottseville, etc...
Each of those branches has multiple employees that I would like to keep track of as separate contacts. At Eck Ashland - Joe is the manager, Tim is in sales, Jeanne is in reception. They all have a unique email address.

My goal is to keep current and potential customers all in the same database.
Starting at the bottom, I need many contacts to go into one branch. Many branches go into one company. Seems like the simplest thing but it's not happening for me.
I figured I would need 3 separate tables for the 3 levels of info. Am I on the right track?
I can't get past picking a primary key and creating appropriate relationships and I feel like an idiot.

Help! Please!

Thanks!

[pbaldy]

Sounds like you're on the right track. An example would have CompanyID as the primary key in the company table. CompanyID would be a foreign key in the branches table. The branches table would have a primary key of BranchID, which would be a foreign key in the contacts table. Each of those is a one-to-many relationship.

[faciaface]

Sounds like you're on the right track. An example would have CompanyID as the primary key in the company table. CompanyID would be a foreign key in the branches table. The branches table would have a primary key of BranchID, which would be a foreign key in the contacts table. Each of those is a one-to-many relationship.

Wow that was a quick response!

I have set it up like that, but the branchID is not matching up because the fields are not the same type. BranchID in the Branch table is an Auto Number and BranchID in the COntact table is just text. THat's the barrier I can't get past.

[pbaldy]

It should be Number/Long in the Contact table.

[faciaface]

Ok that worked and I'm ecstatic.
Here's my new problem...
The BranchID is an Access generated number (which I will not be keeping track of since my customers already have Customer numbers that are generated from within the company).
I can't wrap my brain around how I am going to dump all my data into the database.
Do I first enter every company? (This will give each company a CompanyID. Will I need that number to enter the Branch info in?)

I guess I'm just confused about the entry process. Am I able to make a form that will dump info into all three table at once? Or do i need to take all the time to create each table individually?

My goal was to get the database formatted correctly and then reformat my existing spreadsheets to match, so i could import all the info in. Will that work?

I know I'm asking a ton of questions right now. I hope they all make sense.

[pbaldy]

Well, I might just use your existing customer number as the primary/foreign key. Some people feel that every table should have an autonumber primary key, but I'm not in that camp (search on natural vs surrogate keys if you want the debate). I suspect that would make the import process easier. Don't get me wrong, I use autonumbers often, but if there's a reasonable value that can be used instead, I often use it.

If you want to stay with the autonumbers, I'd expect to be able to either import everything except the foreign key, then populate it with an update query that joins on the company table to get the appropriate autonumber value for each record in the branch table.

[pbaldy]

Oops, I said "either" and only listed one option. The other would be to import the companies, then link to that table when importing the branches, enabling you to populate that field during the import (assumes the Excel files are linked). Same for contacts.

[faciaface]

I have to stick with the autonumber because many of the "customers" i'll be entering are not yet customers and therefore do not have a number assigned to them yet.

Thank you for all your help.

I think the point where I'm stumbling, is that I don't trust that all my branches will automatically hook up with the appropriate company, and all the contacts will slip into their appropriate branches.
I've never made a database from scratch before, I've only ever done the ones in the workbook where they already know that everything is going to work out ok.

None of the excel files are linked, they're all individually created.

I'll play with it tomorrow morning and I'm sure I'll have more questions then.

[pbaldy]

It would help to see the Excel files, but presuming they have the appropriate company, you should be able to get that into Access correctly. By linked I meant linked to the Access database so you could run queries against them. Post back tomorrow if you're stuck.

[Subwind]

This is very much like something I have created at work. I have mine for customers and suppliers.

I would create the main structure as below.

tblCustomer

• cusCustomerID (Primary - Autonumber)
• cusCustomerName (Primary - Text - Indexed No Duplicates)

tblCustomerBranch

• braBranchID (Primary - Autonumber)
• braCustomerID (Primary - Number - Indexed Duplicates Allowed)

tblCustomerContact

• conContactID (Primary - Autonumber)
• conBranchID (Primart - Number - Indexed Duplicates Allowed)

cusCustomerID related to braCustomerID, One-to-Many, Cascade Delete Related Records
braBranchID related to conBranchID, One-to-Many, Cascade Delete Related Records

This would then link the contacts to their branches only, and the branches to their customer only.

Any fields you have specific to your data requirement in the related tables.

Hope this gives some help.

~Matt