Choose value based on a date range

Discussion created by lancelotlink on Jun 28, 2018
Latest reply on Dec 3, 2018 by kdu472

I'm trying to create an attendance database for billing State Subsidies. Rates change at lease annually, sometimes twice a year. I'd like to have one table to house all attendance records for the children and a second table to house the rates based on a start and end date. The relationship between the two databases would be a rate description like Infant-Based Care or Toddler-Based care and whether it was full time or part time care.

I would have a record that would list a rate with the related name, full time or part time, and the date range that this rate would be active.

The attendance side would have the related rate name, the single date, a field for FT or PT (full time or part time) in order to get the rate pulled from the other table.

This is of course a slimmed down description of all that I would be doing, but if I can get this to work, I'll be able to make the rest work.



Thanks in advance to anyone that can give me a nudge in the right direction,