Struggling with Normalisation

[Gaudete]

I have set several tables up in Access 2013 that all work fine along with their relationships and look ups. I have a particular issue that I cannot resolve.

Tables
CUSTOMERS
CustID which is unique example ABC001
CustName example Acme Construction
etc

BRANCHES
BranchID 2(X)
CustID Lookup to Customers One customer many branches.
BranchNo 2(X) example 02
BranchName example London

The are duplicate BranchNo s as each unique customer has multiple branches all starting 01 onwards.

all the above works fine and branches can be created against customers by lookup.

I then have a Jobs file and this is where my problems begin.

JOBS
JobID
JobNo (X)3 example 001
CustID Looks up Customer all works fine
BranchNo This looks up Branch number in BRANCHES . Here is the issue the branch look up will list all branches irrespective of CustID.

For example if I give BranchNo 02 it will list all Branches 02 in lookup list. My feeling is that I may have a many to many relationship. Many jobs to many branches.

I am new to this and although I think I understand normalisation, I clearly have a misunderstanding here.

Any help would be greatly appreciated.

 

[namliam]

Well since customer is a "child" of branch, basicaly you should only need to join to BranchNo in your JOBS table.

You should have either a UNIQUE branchID number that is a hidden database key to the branch so you dont need to worry about the customer part of the equation (since that is administrated in the Branch-customer relation).
Or you allow duplicate BranchID, but you need to have a combined key of Customer + BranchID to make it unique.

FYI, making branch numbers (or any numbers) text fields like 01,02 or 001, 002 is just ASKING for trouble. Instead just keep them numbers and if you need the leading zero's use a format to add them in the display part. Numbers are numbers, what will happen if you get BranchNo 100 or JobNo 1001??

FYI 2: You may be intrested in a discussion surrounding this subject:
https://www.google.nl/#q=database natural vs surrogate key

 

[jdraw]

Work through this tutorial.

And these Design Principles

 

[Gaudete]

Thank you for your ideas. I will make it a priority this evening to work through your recommendations

 

[Foe]

Work through this tutorial.

And these Design Principles

good stuff!