Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to do a report that will ask for the amound of years, for example 3
years and it will give me everyone in the db that had not had an award in the
last three years, type 5 and give all without an award for the last 5 years.
I know i will need a querry but I do not the formula on how to do such list.
Thank you.


Teofilo J. Herrera
514MXS?CCQ (afrc)
 
I need to do a report that will ask for the amound of years, for example 3
years and it will give me everyone in the db that had not had an award in the
last three years, type 5 and give all without an award for the last 5 years.
I know i will need a querry but I do not the formula on how to do such list.
Thank you.
Click to expand...

Well, I don't know the structure of your tables; but assuming that you
have a table of Members with MemberID as the primary key, related one
to many to a table of Awards, you can use a criterion

SELECT <whatever fields you want>
FROM Members
WHERE NOT EXISTS
(SELECT MemberID FROM Awards
WHERE AwardDate > DateAdd("yyyy", -[How many years?], Date())
AND Awards.MemberID = Members.MemberID)

Change the table and fieldnames to suit and copy this into the SQL
window of a new query.

John W. Vinson[MVP]
 
Back
Top