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.

Any suggestions?

Thanks for your help.
johnmadiganAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
You can use:

Select
  Transaction.*,
  Period.*
From
  Transaction,
  Period
Where
   Transaction.Date Between Period.StartDate And Period.EndDate

/gustav
0
All Courses

From novice to tech pro — start learning today.