Hello,
I'm quite new to Access and I have a problem how to set up relationships between my tables so I could get desired results.
As far as I know the problem lies in JOIN type - I think just simple INNER JOIN is not enough. I probably have to apply LEFT, RIGHT or maybe FULL join but do not how exactly.
I will describe my database here in details and also attach it to the post so you can look into it and maybe help me somehow.
tbl_Input - contains input data, contents of this table will change, I will load new data each month
No Primary Key
1) Client - client ID: for example - C001, C002, C003, C004
2) Depatment - department name: Finance, Marketing, Derivatives, HR, Payroll
3) Amount - amount of money
tbl_Client - contains client information
1) Client (PK) - client ID: C001, C002, C003, C004, C005
2) Region - one of four regions: Asia, America, Europe, Africa
3) Active? - Yes/No field, determines if client is active or inactive
tbl_Department - contains department infomation, there can be the same Dept_Abbr for many Departments, e.g. ADM Dept_Abbr is mapped to HR department and Payroll department
1) Department (PK) - department name: Finance, Marketing, Derivatives, HR, Payroll
2) Dept_Abbr - department ID: FIN, MAR, ADM
tbl_Function - contains function information, there are 3 different Processes (P1, P2 and P3) and there is some Function for each different combination of Dept_Abbr + Procces
1) Function - function ID: F1, M1, A1, F2, M2, A2, F3, M3, A3
2) Dept_Abbr (PK) - department ID: FIN, MAR, ADM
3) Process (PK) - process ID: P1, P2, P3
tbl_Account - contains account numebers, there are some account numbers for each different combination of Client + Function
1) Account - account number
2) Client (PK) - client ID: C001, C002, C003
3) Function (PK) - function ID: F1, M1, A1, F2, M2, A2, F3, M3, A3
So I would like to display all Clients from tbl_Input for which in tbl_Client Region = "Europe" and Active? = True. For records that match these criteria I would like to display also Dept_Abbr (tbl_Department), Function (tbl_Function) and Account (tbl_Account).
This is how my tables are linked now:
tbl_Input.Client = tbl_Client.Client
tbl_Input.Department = tbl_Department.Department
tbl_Department.Dept_Abbr = tbl_Function.Dept_Abbr
tbl_Function.Function = tbl_Account.Function
There is no referential integrity - it's just simple INNER JOIN
So for example let's say that I want to display records for Process P1 and that in tbl_Input there is a record Client = C002, Department = HR. Let's supose that Client C002 belongs to Region Europe and is Active. Now the record will be displayed by query only if in tbl_Function there is any Function matched with Dept_Abbr and Process P1 and if in tbl_Account there is any Account matched with Client C002 and given Function from tbl_Function.
What I want to achieve is the record from tbl_Input to be displayed by query if given Client is from Europe and is Active no matter if there is any matching Function or Account for this specific Client and it's Department. If there is not I would like the query to display the record anyway with blank Function and Account fields.
How can I modify my relationships to make it work or maybe I should rethink the whole relationships and link my tables in a different way? Any help will be appreciated.
Here is the link to my sampel database:
Code:
https://app.box.com/s/dgd153yby1edhffxhqjn