Previous two years compared, query

[Mary]

I am wondering if there is a way to compare data from
August first of the previous year to the present date against
Everything before August first of the previous year.

Specifically, I would like the database to return:
Query 1: anyone who is newly retired since August 1
Query 2: anyone who has continued in the same job
Query 3: anyone who has started a new job title since August 1

This is something we do every year so if the database can be set up to not
have to change dates within the query this would be great.

Many thanks to anyone who has ideas.

 

[Klatuu]

Assuming you will be running the query for the year you want to pull the
data, you can use the Year function in your date calculation. You also don't
say what fields will be used or what the values will be, so for # 1, I will
assume there is a field named Retire_Date and for #2, 3 a field named
Hired_Date

#1. WHERE Retire_Date >= DateSerial(Year(Date), 8, 1)
#2. WHERE Hired_Date < DateSerial(Year(Date), 8, 1)
#3. WHERE Hired_Date >= DateSerial(Year(Date), 8, 1)