single company with multiple locations, How to setup the table

[dmctiernan]

I'm a relatively new to creating databases in Access and recently I have created a database for tracking items manufactured on projects. So far so good, until today when one of my colleagues asked if I could include the following:

He wants to be able to filter all projects, per customer and also per customer location, as most of customers have multiple sites. What would be the best way to set this up.

Thanks,
Declan.

 

[lespaul]

So how do you currently determine the different locations? You would have to add that somewhere in the database, depending on your existing setup you may put it in it's own table linked to customers and then add the location to the project table.


Leslie

Have you met Hardy Heron?

 

[SoCalAccessPro]

Yeah, you'd have a new table, tblCustLoc for arguments sake.

In that table, you'd store the Main Customer ID and a new Location ID.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[dmctiernan]

Thanks guys for the quick replies.

What I basically have is the following

tblCustomers (PK: fldCustomerID)
tblLocations (PK: fldLocationID)
tblProjects (PK: fldProjectID, FK: fldCustomerID, )

The data in tblLocations is the LocationID, the CustomerID, LocationName.
Since each customer can have many locations, how do I setup to fill a combo box on the datasheet in my tblProjects table based on the selected CustomerID.

You can download the database from the following link:

http://rapidshare.com/files/133174857/Specification_Database.mdb.html

Maybe someone can take a look at the table relationships and make some suggestions on how to better organise them. I open to any and all suggestions.

Thanks in advance,
Declan.

 

[fneily]

Since you're new to Access, don't know if you've read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Your tables are almost perfect. I'd make the following changesRemember, only items pertaining to the tables topic should be in the table.)

Take out LocationID from tblCustomers. Location has nothing to do with a customer. Customer dies, location is still there.

Take out CustomerID from tblLocations. Location disappears, customer is still around.

Take out LocationID from tblProjects. As suggested, build a
tblCustLoc table with
CustLocID
CustID
LocationID

Now you can see you can get a customers projects and at what locations through the tables connections.

 

[dmctiernan]

Hi fneily,
Thanks for the excellent link.... some great information. I'm not too sure how the relationships will work if I make the suggested changes, I might be missing something.

Maybe you could elaborate a little on your explanation.

Anyhow, my understanding is to create a relationship between the tblCustLoc table and the tblProjects using the CustLocID. Here's where I'm getting lost.....

I create a table called tblCustLoc
CustLocID (PK)
CustID (FK)
LocationID (???????)

Rather than having a LoctionID surely I can just use a Location Name (Datatype: Text). This would save creating another table and having to actually list all locations for every customer, which would be a mammoth task in itself as the actual list would probably contain about 2000 records.

Cheers,
Declan.

 

[fneily]

Yes and no. Let's say a location is Minneapolis-Saint Paul. And you have many customers in that area. In the tblCustLoc table, you'd be typing Minneapolis-Saint Paul quite a few times. If it was in a Location table you'd type it once and it would have an LocID, let's say MSP. Also, maybe in the future, you'd want to use Twin Cities. If the full name was typed in tblCustLoc table you'd have to do an update query, if you'd remember. In the Location table you'd just change it once. The LocID would stay the same so it wouldn't effect any other tables or relationships. But it's your call. Either way is good. However, if you have more info on a location such as address, city, state, phone, then you need the Location table.

All projects per customer - in a query connect tblProject to tblCustomer through CustID. One-to-many, a customer can have many projects.

All projects per customer location - in a query connect tblProject to tblCustLoc through CustID then tblCustLoc to tblLocation through LocID. A many-to-many relationship: a project can span many locations, a location can be used by many projects. So the junction table tblCustLoc must be used.