Help with Archival query

[Zeunasc]

I have a table with the following columns: StudentName,
DatePointGiven, PointValue, Comments, Archived. I also have a table
with a single field: StudentName, that contains a listing of all the
employees.

Students can get either 0, .5 or 1 point based on their performance.
Currently I have one query that will give me a listing of every
student that received a point or half point for the last 90 days. I
have another query that uses a left join to tell me which student
haven't received any points in the past 90 days. (if there is a better
way to do this, please let me know) If a student hasn't received a
point in the past 90 days, they are forgiven 1 full point from the
past 12 months. To accomplish this, I have a form that lets you pick
a name and it runs a query of all points for the name for the past 12
months. You pick the oldest point date and put today's date in the
Archived field. This lets me use an Is Null in other queries to
ignore those point in calculations.

The problem is this. Once you go 90 days and have a point forgiven,
you have to go another 90 days before getting another point forgiven.
I don't know how to do this. When a point is forgiven, the day it is
forgiven is logged in to the Archived field, but I just can't make the
connection as to how to keep that person's name off the list for
another 90 days.

Any help is appreciated. If you need more info, let me know.

TIA,
Tim

 

[Smartin]

I have a table with the following columns: StudentName,
DatePointGiven, PointValue, Comments, Archived. I also have a table
with a single field: StudentName, that contains a listing of all the
employees.

Students can get either 0, .5 or 1 point based on their performance.
Currently I have one query that will give me a listing of every
student that received a point or half point for the last 90 days. I
have another query that uses a left join to tell me which student
haven't received any points in the past 90 days. (if there is a better
way to do this, please let me know) If a student hasn't received a
point in the past 90 days, they are forgiven 1 full point from the
past 12 months. To accomplish this, I have a form that lets you pick
a name and it runs a query of all points for the name for the past 12
months. You pick the oldest point date and put today's date in the
Archived field. This lets me use an Is Null in other queries to
ignore those point in calculations.

The problem is this. Once you go 90 days and have a point forgiven,
you have to go another 90 days before getting another point forgiven.
I don't know how to do this. When a point is forgiven, the day it is
forgiven is logged in to the Archived field, but I just can't make the
connection as to how to keep that person's name off the list for
another 90 days.

Any help is appreciated. If you need more info, let me know.

TIA,
Tim

Hi Tim,

To coin a phrase from Allen Browne, this sounds like "identifying what
is not there". IOW, you want to see students who are not in the forgiven
list in the last 90 days. This is in fact the first topic on one of his
pages on subqueries:

http://allenbrowne.com/subquery-01.html

Hope this gives you some ideas.