Find what date range a date belongs to
I have a time card layout in EmployeeInformation table with a portal to time records. An employee can press an in or out button to update their daily time at work. The PayPeriods table is a list of start and end dates for pay periods with an associated pay date.
When an employee clocks in, a date field is populated with today's date. I would like for that date to be compared to the PayPeriods table to find which pay period it falls in with the end goal being the portal will default to the current pay period but an employee has the option of selecting records associated with previous pay periods without having to enter search dates. Something like a "previous" and "next" button above the portal to time records.
I'm not sure if the tables are related correctly for this to function. I can't seem to find a way to relate the date field in time records to the date range in pay periods. I would appreciate any advice.