Results 1 to 8 of 8
  1. #1
    RatExcel is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2014
    Posts
    7

    DB design problem - how to set up relationships to get desired results


    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1. INNER JOIN / outer is only for queries. All relationships should be INNER. Dont overcomplicate the db.

    I think you only need to make relationships on PARENT / CHILD tables. This ensures you dont create orphans in the child table if the parent is deleted.
    I wouldnt bother relating other tables...(from my point) it just hinders development, and is a pain when you have to modify.

    Make your inner/ outer joins in queries like tDept and various 'lookup' tables.

  3. #3
    RatExcel is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2014
    Posts
    7
    I haven't made any relationships in Database Tools > Relationships. I've joined the tables directly in query. My query works just fine if all needed information is in all tables. But for example if in tbl_Account there is no matching Account number for given Client and Function I get no results even if given Client belongs to Region Europe and is marked as Active. The question is how can I modify the relations in query to make it display the record even if there is no matching Account number.

    For exmaple:
    I want to generate results for all Active European Clients for Process P1.
    1. First of all I have to check each record in tbl_Input if the given Client exists in tbl_Client and if it's European Active client.
    2. If yes I need to check which Department in tbl_Input is related to this Client.
    3. Then I need to find related Dept_Abbr in tbl_Department for Department from tbl_Input.
    4. Then having the Dept_Abbr and Process (P1) I have to find related Function in tbl_Function.
    5. Then knowing the Client and the Function I have to find related Account in tbl_Account.
    The problem start at point 4. If in tbl_Function there is no Function related to Dept_Abbr and Process P1 the query will omit the whole record.
    I would like it to display the record anyway with fields Function and Account being blank so I could know that there is some missing data in one of my tables.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Change to RIGHT or LEFT JOIN. Double click the linking line in the query and set relationship.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    RatExcel is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2014
    Posts
    7
    I think I have solved the problem by writing several queries going step by step rather than one query.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Why do you have hidden global system tables linked in your relationship window?

    This is a very dangerous thing to do!!!!

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I have seen many posted db's with system tables linked. Always wonder why.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.
    I have been looking at your tables/relationships for a while. I think that as is, you will have problems getting the data displayed in the manner you want. Or at best, it will be difficult. You should rethink your table design and relationships.

    I did a lot of modifications to your tables, but didn't keep an unmodified copy of the dB.
    The link to download another copy has been removed, so I can't compare my mods to the original.

    I think I have solved the problem by writing several queries going step by step rather than one query.
    This is what I mean by being difficult.

    I use autonumber fields in my table as PK fields. If I need to have two fields be unique, I set a unique index using the two fields, not set the two fields as a composite PK.


    I think you will be doing and a lot!!! Who needs hair anyway??


    I would like to be able to offer some suggestions, but I don't know enough about your project. You have described what you have done, but not why (the purpose).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Design: Many-to-Many relationships
    By rmohaisen in forum Database Design
    Replies: 11
    Last Post: 02-13-2014, 12:23 PM
  2. Help getting design right first time (relationships)
    By Creakyknees in forum Database Design
    Replies: 4
    Last Post: 12-26-2013, 03:47 PM
  3. Design/Relationships
    By j9070749 in forum Database Design
    Replies: 2
    Last Post: 11-06-2013, 10:28 AM
  4. Replies: 5
    Last Post: 05-21-2013, 02:21 PM
  5. Table Design & Relationships
    By mastromb in forum Database Design
    Replies: 16
    Last Post: 12-30-2009, 10:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums