I am creating an Excel workbook to keep track of my paychecks. I have a parent table called EarningsRates that has the following columns:
Earnings Code: Code to determine the description (e.g. os1 = Overtime Shift 1, rs1 = Regular Shift 1, pto = Paid Time Off)
Start Date: Date the new pay rate for a particular earnings code goes into effect (usually January 1 of each year)
End Date: Date the pay rate for a particular earnings code ends (usually December 31 of each year)
Rate: The actual pay rate per hour for a certain pay description in a particular date range.
I also have a child table called EarningsTransactions that stores the actual paycheck transactions:
Earnings Code: Same as EarningsRates table.
Pay Date: Date of the paycheck
Hours: Number of hours worked during the pay period.
Rate: Same as EarningsRates table. Fetches the Rate through a SUMIFS function using Pay Date field.
Amount: Calculated field. Hours column * Rate column.
What I want to do is add some data validation such that:
* You cannot enter in an earnings code and pay date into EarningsTransactions unless there is a corresponding code and date range in EarningsRates.
* You cannot delete a record in the parent table EarningsRates without first deleting the corresponding record(s) in EarningsTransactions
Is this possible?
Earnings Code: Code to determine the description (e.g. os1 = Overtime Shift 1, rs1 = Regular Shift 1, pto = Paid Time Off)
Start Date: Date the new pay rate for a particular earnings code goes into effect (usually January 1 of each year)
End Date: Date the pay rate for a particular earnings code ends (usually December 31 of each year)
Rate: The actual pay rate per hour for a certain pay description in a particular date range.
I also have a child table called EarningsTransactions that stores the actual paycheck transactions:
Earnings Code: Same as EarningsRates table.
Pay Date: Date of the paycheck
Hours: Number of hours worked during the pay period.
Rate: Same as EarningsRates table. Fetches the Rate through a SUMIFS function using Pay Date field.
Amount: Calculated field. Hours column * Rate column.
What I want to do is add some data validation such that:
* You cannot enter in an earnings code and pay date into EarningsTransactions unless there is a corresponding code and date range in EarningsRates.
* You cannot delete a record in the parent table EarningsRates without first deleting the corresponding record(s) in EarningsTransactions
Is this possible?
