Results 1 to 5 of 5
  1. #1
    osfar is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6

    help in redesigning my tables

    i got 2 tables : 1 for member personnel detail (MemberID,MemberName,College,Department,Degree,Gen eral_specifation,
    Specialization,Date_Of_Hiring,MasterDate,PHD_Date)

    and the other table for mission for each member

    (MemberID,MissionType,Date_of_approve,Destination,TravelDate,ReturnDate,
    DepartmenapproveDate,FacultyApproveDate,UnivApproveDate,ExtendDate1,ExtendDate2,ExtendDate3)

    i have mission types
    type1 ,type2 , type3 , type 4

    type1 duration 4 years and can't be extended


    type 2 can be extended for 2 years , each time for 3 monthes only ,for ex : from 1/3/2017 to 1/6/2017 , can be extended to 1/9 , 1/12 , 1/3/2018
    and so on


  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You don't provide enough data to decide how to create the tables, but there could be 3 to 5 tables.

    Take a look at the attached Excel WS -- fill out about 4 records for the first two tables and the 4 records for the Mission Type table.
    Don't have to use real names, just need example data.


    For your purposes:
    Can a member have more than 2 degrees in multiple disciplines? (ex. 2 masters and a Phd?)
    Can the degrees be from different Universities? And in different departments?
    Attached Files Attached Files

  3. #3
    osfar is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    For your purposes:
    Can a member have more than 2 degrees in multiple disciplines? (ex. 2 masters and a PhD?)
    Can the degrees be from different Universities? And in different departments?
    for degree i only have 5 choices : Teaching Assistant ,assistant teacher ,Teacher ,Assistant Professor ,professor.
    and i only care about his department he got his Bsc , so i don't want to track his different departments .
    the main purpose of that program is track the scientific scholarships , and to make sure he is eligible for it or not as he has limited scholarships / year .
    and make statistic for each educational year (from july to july next year) about how many member of each college go a schoolarship.

    and this is the filled excel fileMissionDB Structure.zip

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    So you want track the person over years?

    College is where the person works? Over the years a person can leave one college and join another - so you must have a separate table to follow such movements!
    MemberColleges: MemberCollegeID, MemberID, College, DateOfHiring, [DateOfLeaving]

    Department is a unit within college? People sometaims move from one department to another - so again additional table to follow those movements!
    MemberDepartments: MemberDepartmentID, MemberID, College, Department, StartDate, [EndDate]

    All professors had mostly to start as assistants some years ago. And current assistants are hoping to work as professors in future. So you'll get a table again! (It looks like degree in your table means occupation - maybe you change it, it looks confusing for me.)
    MemberOccupations: MemberOccupationID, MemberID, Occupation, StartDate, [EndDate]

    I don't know, what you mean with general specification, so no comments about this.b

    I'm sure sometimes people can change their specialization, or even they can have several of them at once. A Table again!
    MemberSpecializations: MemberSpecializationID, MemberID, Specialization, StartDate, [EndDate]

    And instead of having in Personell table a field for every scientific degree, put those also into separate table!
    MemberDegrees: MemberDegreeID, MemberID, Degree, GettingDate

    An I almost forgot! People sometimes change names (marring or simply as they want). So maybe a table for names too?
    MemberNames: MemberNameID, MemberID, ForeName, LastName, StartDate, [EndDate]

    and now what is left about members!
    Members: MemberID, ... (you may want/need here some additional info about person - but be sure that this info willn never change)

    A good idea would be also have separate tables for colleges and college departments - so you can use combos to select them and avoid typing errors.

    The mission table is better to split to 3 tables:
    Missions: MissionID, MemberID, Destination, TravelDate;
    MissionApproving: ApprovingID, MissionID, Approver, ApprovingDate;
    MissionEnd: MissionEndID, MissionID, ReturnDate, [Comments]

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    osfar.... no data in the Excel SS in Post #3 - just the original SS I posted.......

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

Similar Threads

  1. Redesigning a very old, very inefficient database
    By CryLittleSister in forum Database Design
    Replies: 3
    Last Post: 03-16-2017, 10:58 AM
  2. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  3. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  4. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  5. Redesigning an access database
    By frankl in forum Database Design
    Replies: 0
    Last Post: 08-17-2007, 10:52 AM

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