Results 1 to 3 of 3
  1. #1
    rlesage1645 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    2

    Looking up a value in one table within a date range in a second table

    I’m looking for some thoughts on looking up a value in one table between a range of dates in another table. The data is in two tables as follows:

    Table 1: tblHR_JOBS
    Field1 – EMPLID


    Field2 – JOB_CODE
    Field3 – EFFECTIVE_DATE
    Field4 – MONTHLY_SALARY

    Table 2: tblActual_Pay
    Field1 – PAY_PERSON_ID (Same value as EMPLID - just different field name in the pay table)
    Field2 – PAY_PERIOD_BEGIN_DATE
    Field3 – PAY_PERIOD_END_DATE
    Field4 – PAY_AMOUNT

    What I’m trying do is join these tables in a query and do a lookup to find what the employee’s monthly salary was for each given pay period. i.e

    DESIRED QUERY RESULT:
    EMPLID
    PAY_PERIOD_BEGIN_DATE
    PAY_PERIOD_END_DATE
    PAY_AMT
    MONTHLY_SALARY

    Will a DLOOKUP achieve what I’m attempting to do here? These tables are large and I know DLOOKUP could potentially suck the life out of the system. Any other thoughts on how I should go about this? Thanks for any insight.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a Crosstab query from tHrJobs on EmpID,Date.
    you will get Emp,DAte1,date2,date3

    using this xtab query , make a temp table, tEmpDateList, to fill with these dates, but only 2 dates at a time

    tEmpDateList
    Emp, startDate, EndDate

    using the xtab query , make an append query into tEmpDateLIst with ONLY 2 dates at a time
    emp, date1, date2
    then
    emp, date2,date3
    etc , until all data is in this. NOW you can run a join on tEmpDateList and tActualPay using the start,End dates.

  3. #3
    rlesage1645 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    2
    Thanks. I'll see what I can do with this.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  2. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  3. Replies: 1
    Last Post: 11-16-2014, 09:10 AM
  4. How create a table for a date range report
    By jegupta in forum Programming
    Replies: 12
    Last Post: 01-30-2014, 12:33 PM
  5. Replies: 1
    Last Post: 05-25-2010, 02:58 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