Results 1 to 10 of 10
  1. #1
    NazmulAcess is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    5

    Post Relationship Design

    Hi, I am new in MS Access.



    I am designing a Database for a HRM.

    I have seven table

    1. emp_personal_info
    2. emp_office_info
    3. emp_activity
    4. salary
    5. increment
    6. leave
    7. extra

    Each table has the same ID and only emp_personal_info's ID has declared as primary key. all the tables are connected as one-to-many relation.

    The emp_personal_info table's ID is not taking duplicate value. But all the other IDs of other table ar taking duplicate value.

    How can i stop this ? Please suggest.



    Click image for larger version. 

Name:	hrmdb.png 
Views:	34 
Size:	41.2 KB 
ID:	34929

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    emp_personal_info table should NOT get duplicates. Thats the point of the ID Key.
    all other tables should allow it.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Also do not duplicate fields except for PK /FK. The field 'sl' should only be in the main table emp_personal_info

    All the other tables need a PK field added. Without that you will not be able to use update queries on them
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    NazmulAcess is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    5
    Hi Ridders,

    Thanks for your help. I think your reply should help me.
    But because of language gap your message is not clear to me.

    And I know this could be too much to ask, but would you please give me a screen shot of my database with the right relationship.

    I will be grateful to you.

    Thanks.

  5. #5
    NazmulAcess is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    5
    Hi Ridders,

    Thanks for your help. I think your reply should help me.
    But because of language gap your message is not clear to me.

    And I know this could be too much to ask, but would you please give me a screen shot of my database with the right relationship.

    I will be grateful to you.

    Thanks.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I can't show you what it should look like just with the info you've provided

    It may be fine once you:
    a) remove all 'sl' fields except in the main table - what is that field used for?
    b) add PK fields to all other tables e.g. ActivityID, ExtraID, SalaryID etx
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    NazmulAcess is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    5
    Oh nice... I think this is being clear to me... what should be the datatype for ActivityID, ExtraID, SalaryID ? Autonumber ? is this ok?

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Autonumber will be fine.
    Also strongly recommend you:
    a) remove all spaces in field names e.g. use Earned_Leave or better still EarnedLeave
    b) store the employee last name and first name as separate fields. Add a postcode field or equivalent for your country
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    NazmulAcess is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    5
    Thanks a ton... This will really help a lot.

    I express my gratitude to you.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As others have said --each table should have a Primary Key to uniquely identify each record in that table.
    Fields should have meaningful names with no embedded spaces nor special symbols.

    You may get some ideas from this free data model from Barry Williams' site
    http://www.databaseanswers.org/data_...ssie/index.htm

    Consider a table of LeaveType ((this list from Google search))

    • Bereavement
    • Pregnancy and/or pregnancy disability*
    • Public holidays
    • Vacation days
    • Sick days or leave time for family illness
    • Maternity/Paternity*
    • Other leave as required by a collective bargaining agreement
    • Adoption leave
    • Temporary disability leave
    • Voting*
    • Childbirth (separate from maternity leave)
    • Child care
    • Community service or volunteer work
    • Attendance at parent/teacher conferences
    • Administrative leave
    • Personal leave
    • Adverse weather
    • Comp time to compensate for extra hours worked
    • Other leave as appropriate for the industry or type of employer, which will vary. (One example would be leave to attend conferences or conventions.)
    • Leave to appear as a witness in court, which also may include time to prepare for such.



    and apply it to a table of EmployeeLeave (EmpId,LeaveStart,LeaveDuration,LeaveTypeId...)

    I recommend that you work through 1 or 2 of the tutorials from RogersAccessLIbrary mentioned in this link.

    Good luck.

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

Similar Threads

  1. Relationship Design
    By Boost in forum Database Design
    Replies: 0
    Last Post: 07-30-2018, 07:45 PM
  2. Relationship/Design Help
    By twc1001 in forum Database Design
    Replies: 1
    Last Post: 07-05-2013, 08:23 AM
  3. Table and Relationship Design
    By GrayWolf in forum Access
    Replies: 6
    Last Post: 01-31-2012, 02:04 PM
  4. Relationship Design
    By krymer in forum Database Design
    Replies: 3
    Last Post: 11-28-2008, 09:09 PM
  5. design using relationship...
    By dsnyder in forum Database Design
    Replies: 2
    Last Post: 10-21-2008, 12:00 PM

Tags for this Thread

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