Link records between two tables based on range?

[Sheridan]

I'm trying to write an update query that will link records between two tables depending on whether an integer value derived from one table falls between the values of two integer values from the other table.

I have a Members table, and a Rates table. The Members table has DOB. The Rates table has a MIN_AGE and a MAX_AGE. For each record in the Members table, I want to calculate each member's age based on the current date at runtime, then match each Members record to a Rates record where the Member's age falls into the range between the Rates record's MIN_AGE and MAX_AGE records.

Here are a couple of failed attempts I made:

Attempt 1 using subquery

UPDATE Members
SET Members.RATE_FK = (SELECT ID FROM Rates WHERE (DATEDIFF('yyyy', DATE(), Members.DOB) BETWEEN Rates.MIN_AGE AND Rates.MAX_AGE))
WHERE Members.ACTIVE = 'Yes';

Access says "Operation must use an updateable query."

Attempt 2 using inner join

UPDATE Members
SET Members.RATE_FK = Rates.ID
FROM Members
INNER JOIN Rates ON DATEDIFF(year, DATE(), Members.DOB) BETWEEN Rates.MIN_AGE AND Rates.MAX_AGE
WHERE Members.ACTIVE = 'Yes';

Access says "Syntax error (missing operator)..."

I can't find much help online for this sort of problem. Maybe I'm approaching this all wrong?

 

[GaP42]

As a start: Datediff Does not recognise "year" as an input setting: datediff-function

As a side issue: you are assigning current members a membership rate for the current year, I think. What it implies, as you keep this info on the members table, is that you keep no history of payment of membership fees, and overwrite any current rate. Normally I would maintain a Financial-members join table that keeps such history of membership rates and payments by FinancialYr / Period. Records can be generated for new financial year in a batch process by the authorised user at the beginning of the financial year, and when new members are added.

 

[DHookom]

Your first sql statement could be used by replacing the subquery with DLookup(). Depending on the number of records, this might not be very efficient.

 

[Mark_]

Do you have a child table to members for their membership/renewal?
Were this me, I'd have a child for "Payments" that includes "RenewalDate". You could then run a process based on RenewalDate to insert payments due and have that do the look up based on their age at RenewalDate.

This give you a convenient way to show membership history as well as renewal costs. Also means costs can be adjusted on a per-member basis when needed.