multiple valid effective dates

[AccessWimp]

I have an Access XP database that stores personnel records, budgeted salaries/benefits, and other related information. Each position has an original record and any number of related records that include updates to the original. All of the records must remain in the database as an audit trail. Each record includes an effective start and end date and an entry date. A position's salary is displayed as entries in a grid by month.

I have already written the queries required to generate the base monthly salary using various employee types, ranges, steps, increment dates, etc. Now I need a query/module to 1) determine which records are effective during any part of the month; 2) if the effective dates are the same, to choose the data from the most recent update; 3) if effective dates overlap, to use one record for one part of the month (using a workday function I've already written) and a second record for the other part of the month; 4) last, there are two salary per month grids: a) one shows the salary by month using all of the most recent updates; b) the second only implements those updates that were entered prior to each month's close of business.

Any suggestions?

 

[cmmrfrds]

All effective records per person.
where (effectivedate <= beginMonth and enddate >= beginMonth)
or (effectivedate >= beginMonth and <= endMonth and enddate >= endMonth)

Last Record per person
where effectivedate <= endMonth and enddate >= beginMonth