Creating a relationship that selects the proper record in a parent table based on date and the selection made from a different parent table.
I'm having trouble creating the proper relationship. The following are the tables and fields involved when we're running into trouble:
Classes - Class Name
Rates - Effective date, Rate 1, Rate 2, etc.....
Members - Member Name
Membership Records (this contains records of members being assigned to classes and if it's easier, we could assign rates to them as well)- Effective Date
Remittances - From Date, To Date, Hours
In the remittance table, we'd like the user to pick the member and then based on the dates of the remittance and the member selected, have the proper Membership Record populate which would be determined by the effective date in the Membership Records for the related member that was selected. Below is a simple example of the relationship.
Also, our hope is to be able to import the data into the remittance table once the relationship is set-up.
Classes - Class Name
Rates - Effective date, Rate 1, Rate 2, etc.....
Members - Member Name
Membership Records (this contains records of members being assigned to classes and if it's easier, we could assign rates to them as well)- Effective Date
Remittances - From Date, To Date, Hours
In the remittance table, we'd like the user to pick the member and then based on the dates of the remittance and the member selected, have the proper Membership Record populate which would be determined by the effective date in the Membership Records for the related member that was selected. Below is a simple example of the relationship.
Also, our hope is to be able to import the data into the remittance table once the relationship is set-up.
