I'm looking for some advice on how to set up some tables for an application.

The application is for managing the attendance of substitute teachers.

Here what I have at this point:
tblSubstitute:
SubID - AutoNum, PK
LastName - Text
FirstName - Text

tblPayRate:
PayRateID - AutoNum, PK
SubID - Number, FK
Amount - Number

tblSubEvents:
EventID - AutoNum, PK
SubID - Number, FK
EventDate - Date

Here's the problem - the rate of pay does not stay the same. i.e a sub will work at an introductory rate and after a certain number of subbing days will be raised to another value. Some subs are payed yet another rate.

If a sub works for payrate1 from September through November, then gets bumped to payrate2 for the rest of the school year. How do I get the end-of-the-year report ,on that sub, to show each event at the correct rate. I guess I could have the payrate entered as part of the event, into tblSubEvents, but that doesn't seem efficient.
Or I could put a date field in the tblPayRate table; then I have to do some sort of calculation on the date of the event and which rate to use.

Any ideas are appreciated.

That's certainly correct.

Yes. Use similar SQL contruction to UA Thread 1695721.