I have tried a few different ways of setting this up and I cannot figure it out. I have a small database with 3 tables:
tblTicketInfo
- TransID
- TicketNumber
- TicketType
- EntryDate
- EntryTime
- ExitDate
- ExitTime
- Duration
- Rate
tblTicketType
- TypeID
- TicketType
tblFeeTable
- IncrementID
- StartTime
- EndTime
- Rate
I then have a form where the user enters the EntryDate and EntryTime for the ticket. The ExitDate and ExitTime are auto-populated with the current date and time. I need this form to:
- Calculate the difference between the EntryDate+EntryTime and the ExitDate+ExitTime
- Store that value in the Duration field & display it on the form
- Update the Duration any time the EntryDate, EntryTime, ExitDate, or ExitTime is changed
- Use that Duration to reference the tblFeeTable; If the Duration is between a StartTime and EndTime, give me the Rate and display it on the form
- Store the Rate in tblTicketInfo
I've thought about creating another table to store durations and rates, using queries to do the calculations, using calculated fields, but I just can't get everything to update properly. Does anyone have any suggestions?
Thanks!