Hi All,
I have two tables with the following information:
1. Rates
- Contract#
- WorkingClass
- RateofPay
- EffectiveDate
2. LaborDetail
- Contract#
- WorkingClass
- DateWorked
- HoursWorked
These are both Many-to-Many tables.
Somehow, I need to relate the two so that I can pull information on the labordetail table when the 'effectivedate' applies.
- Rateofpay pulled into the LaborDetail can only occur IF the 'DateWorked' is ">=" the 'effectivedate'
- The rate pulled also needs to correspond with the 'latest' effective date as the rates change with each 'new' effectivedate brought into the database
The problem is that there are several effective dates for each contract#+workingclass Unique ID (if that were build). So, forcing a relationship, ends up having a 'rateofpay' pulled into the LaborDetail table that ignores the rule of 'effectivedates'.
I'm stumped on this and could use all the help/guidance out there.
Bookmarks