Results 1 to 4 of 4
  1. #1
    sabrish72 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    12

    Newbie Need Guide and advise on Query

    Hi All...


    I have a problem here and I wish some of you can help me out.
    I'm at very new stage in MS Access and my English is not that good.

    I've created a table name tblentitlement ( table - entitlement )
    Fields :
    Jobgred 0to3 3to5 5to10 - (this is year service)
    JG1 12 15 17
    JG2 14 16 18

    Then I have a staff table
    StaffNo Jobgred DateJoint
    0001 JG1 1-6-2011(d-mm-yyyy)
    0002 JG2 1-3-2007(d-mm-yyyy)
    0003 JG2 1-7-2001(d-mm-yyyy)

    What I want to do is a query that can give me a correct figure related to the jobgred and datejoint
    The basis of year calculation will be early of the year example 1-1-2011(d-mm-yyyy) and later can be change to 1-1-2012(d-mm-yyyy) and so on when the time comes

    Base on calculation
    For staffno 0001 related to JG1 and less 3 years service (since he joint 1 june 2011 and the year starts at 1 Jan 2011) he will entitle 12days leave but in the same time his leave will be prorated since he joint in May so the result will show 7 days [(7/12)*12=7] - 7month divide by 12months times 12day equal to 7 days

    For staffno 0002 related to JG2 and less 5 and more than 3 years service he will get 16days ( no need prorated since he serve more than 1 year)

    For staffno 0003 related to JG2 and less 10 and more than 5 years service he will get 18days ( no need prorated since he serve more than 1 year)

    Can anyone help and in the same time I am zero knowledge about VBA ( still reading the Dummies Book to improved)

    Your time reading and helping is highly appreciated

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Since each job grade has many entitlements depending on the years of service, you have a one-to-many relationship which requires a separate but related table. Additionally, since the years of service category can apply to many job grades you have another one-to-many relationship. This many-to-many relationship needs to be represented in a junction table

    tblJobGrades
    -pkJobGradeID primary key, autonumber
    -txtJobGrade

    tblYearsOfServiceCategories
    -pkYoSCatID primary key, autonumber
    -txtYoSCategory

    tblJobGradeYearsOfServiceCategories
    -pkJobGrYoSCatID primary key, autonumber
    -fkJobGradeID foreign key to tblJobGrades
    -fkYoSCatID foreign key to tblYearsOfServiceCategories
    -Entitlement

    With the above table structure, you can proceed with calculating the entitlement of each staff member. You will need a subquery to pull the correct entitlement for each person. You will then need to apply the proration based for those staff member who have less than 1 year of service.

    I have attached an example database with the structure above. I have also included a query: qryCurrentEntitlements that illustrates the calculations needed.

    As a note SQL always evaluates dates in the mm/dd/yyyy format.

  3. #3
    sabrish72 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    12
    Wow, this is a very great help, million thanks ...
    I'll go thru the attachment and reply as soon as i get results

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome; please post back with any questions.

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

Similar Threads

  1. Newbie Help with Access Query
    By MartyMojito in forum Access
    Replies: 1
    Last Post: 03-17-2011, 02:50 PM
  2. Patients Record Table - Guide me someone!
    By cluendo in forum Access
    Replies: 16
    Last Post: 01-24-2011, 11:47 AM
  3. Newbie has a Query Question
    By Bruce in forum Queries
    Replies: 2
    Last Post: 11-20-2009, 10:38 AM
  4. Can anyone advise?
    By doceo in forum Access
    Replies: 0
    Last Post: 10-18-2009, 02:44 PM
  5. Some advise for a complete novice...!
    By 450nick in forum Access
    Replies: 1
    Last Post: 09-11-2009, 02:23 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