Complex update between two tables

[aadebayo]

Hello

Please can somebody help me. I have two tables, tblPupil and tblFSM. I have created a new field in tblFSM called schoolYearID. The tblSchoolYear is a brand new table. The fields in tblFSM are fsmID, surname, forname, dateofbirth, schoolyearID. The fields in tblSchoolYear are schoolyearID, startDOB, endDOB.
What I want to do is

update the tblFSM.schoolYearId with the value of tblSchoolYear.schoolyearID where tblFSM.DOB is between tblSchoolYear.startDOB, tblSchoolYear.endDOB.

Example

Data for tblSchoolYear


schoolYearId startDOB endDOB

11 01-SEP-1993 31-AUG-1994
10 01-SEP-1994 31-AUG-1995
9 01-SEP-1995 31-AUG-1996

the schollYearId for the record below in tblFSM should be 9


FSMID Surname Forename dateofBirth schoolyearID

111 Smith John 25-OCT-1995 9


The link between the two tables is schoolYearId. However, the tblFSM table has no value for schoolYearID.

Please can someone tell me how to update the tblFSM schoolyearId either with a query or access form?

 

[ByteMyzer]

Try:

UPDATE tblFSM, tblSchoolYear
SET tblFSM.schoolyearID = tblSchoolYear.schoolYearId
WHERE (tblFSM.dateofbirth BETWEEN tblSchoolYear.startDOB
                              AND tblSchoolYear.endDOB);

 

[aadebayo]

Thanks ByteMyzer, but when I run this query (it is similar to the one I first tried), access requests a parameter value for tblSchoolYear.schoolYearId. Please see the attached image

 

[aadebayo]

Sorry, please ignore my previous post. Thanks very much it now works. Below is what I did.

UPDATE tblPUPIL, tblSchoolYear SET tblPUPIL.schoolyearID = tblSchoolYear.schoolYearId
WHERE (((tblPUPIL.DOB) between tblSchoolYear.StartDateOfBirth and tblSchoolYear.endDateOfBirth));

 

[ByteMyzer]

Sorry, please ignore my previous post. Thanks very much it now works. Below is what I did.

Don't blame me; you were the one who supplied the table/field names. You also stated that you wanted to update tblFSM, not tblPUPIL.

If you did not supply the correct names the first time, you can only fault yourself.

 

[aadebayo]

Thanks very much bytemyzer, I am very grateful for your help