query for transaction date in an accounting periods
I have a Period table that has the Accounting periods (start date & end date) along with a PeriodID for the period - 1 for Jan, 2 for Feb etc.
The periods are set up on a 4-4-5 week schedule so the dates do not correspond to beginning and end of each month.
I have a transaction table that has a transaction date filed. I would like to make a query that will look at the date in the transaction table and find which period it belongs in then put that periodID in a column in the results.