Find last date added

[Corinne]

My database tracks the pupils who have SEN in school. I have to keep track
of which class the pupils have been in, so I have the main table with
Autonumber PK, first name, surname etc. and a second table whith FK, class
and the date the pupil was put in the class. My problem is that when I run a
query than needs to find all the pupils in year 5 for example it will return
all those who are currently in year 5, 6, 7, 8 etc. because obviously all
those pupils have previously been in year 5. So I modified the query to
return all those currently in this year. (SQL below)

SELECT [Whole School Table UPN].Surname, [Whole School Table UPN].[First
Name], [Year Tutor Group].[Tutor Group], [Year Tutor Group].Dateadded
FROM [Whole School Table UPN] INNER JOIN [Year Tutor Group] ON [Whole School
Table UPN].ID=[Year Tutor Group].[Pupil ID]
WHERE ((([Year Tutor Group].Dateadded)>#7/19/2004#));

My problem is that each year I have to change the date in all the queries to
the current year. Is there any way I can have the query do this
automatically without having to change all the queries every year. I tried
'group by MAX' but this still returns all those who have ever been in year
5.

Thank you for any help you can give on this problem

 

[Allen Browne]

Try:
WHERE [Year Tutor Group].Dateadded) >=
DateSerial(Year(DateAdd("m", -6, Date()), 7, 1))
That should choose the enrolments since the last July 1.

The other alternative would be to use a subquery to select the student's
most recent enrolment record.

 

[CReveley]

I have tried to copy and paste your sql into my query but I am getting an
error, "extra )". I have tried to remove some of the )but it doesn't want to
work.
I am afraid I don't understand how to do subqueries as you suggest.

Do you have any further help or suggestions.

Thank you

 

[John Spencer (MVP)]

I think that Mr.Browne had a slight error in his code. That said, I hope I got
them all correct in the following.

WHERE [Year Tutor Group].Dateadded) >=
DateSerial(Year(DateAdd("m",-6,Date())),7,1)

 

[CReveley]

Thanks for your help. I finally got it working, but you had an extra ) after
Dateadded. But this time when I tried to remove it it worked.
Sadly it seems as hard as I try to learn I am never going to get the hang of
this code. But I do keep trying. Thanks again for taking the time to help me.