Date Query

I have a table which records the dates students received their latest karate belt. The fields are BID (Belt ID), SNAME (Student Name), PRANK (Promotion Rank) and PROMD (Promotion Date). Using a query generously supplied by this group (SELECT Max ( [PROMD] ) FROM BELT AS X WHERE X.SNAME=BELT.SNAME), I can now query on the very last date of belt promotion for each student.
However, I need to calculate attendance from this last belt promotion to determine if the student is eligible to test again (karate rules state required hours of class participation between belts). I have another table which records attendance. The karate owner uses a book to record daily attendance and records only a cumulative number at the end of the month. The table for attendance has the following fields: AID (attendance ID), SNAME (Student Name), ATT (Number of attendances for the month) and MONTH (the month and year).
I am unsure how to use the queried last date and the date today to achieve a sum of the number of attendances from the last belt promotion to the date of the query. Any help would be MOST appreciated.
PPerry

You will need to force the Month and Year data to be a date at the end of the
month. This kind of depends on how month is stored in the database. Assuming
09/03 for September 2003. Then you need something like:

DateSerial(Right(Month,2)+2000,Left(Month,2)+1,0)

which will give you Sept 30, 2003. If date is stored as 09/2003 then you need
to change the expression to

DateSerial(Right(Month,4),Left(Month,2)+1,0)

If you store the date in month in some other manner then you will need to adjust
the date expression to return a valid date. Substitute the expression in the
following SQL statement. UNTESTED SQL statement follows.

SELECT A.AidID, A.SNAME, SUM(ATT) as TotalAttendance
FROM Attendance As A INNER JOIN BELT as B
ON A.SNAME = B.SNAME
WHERE TheDateExpression =
(SELECT Max([PROMD])
FROM BELT AS X
WHERE X.SNAME = A.SNAME)

PPerry wrote:

I have a table which records the dates students received their latest karate belt. The fields are BID (Belt ID), SNAME (Student Name), PRANK (Promotion Rank) and PROMD (Promotion Date). Using a query generously supplied by this group (SELECT Max ( [PROMD] ) FROM BELT AS X WHERE X.SNAME=BELT.SNAME), I can now query on the very last date of belt promotion for each student.
However, I need to calculate attendance from this last belt promotion to determine if the student is eligible to test again (karate rules state required hours of class participation between belts). I have another table which records attendance. The karate owner uses a book to record daily attendance and records only a cumulative number at the end of the month. The table for attendance has the following fields: AID (attendance ID), SNAME (Student Name), ATT (Number of attendances for the month) and MONTH (the month and year).
I am unsure how to use the queried last date and the date today to achieve a sum of the number of attendances from the last belt promotion to the date of the query. Any help would be MOST appreciated.
PPerry

HOLY COW! Where did you learn to do that?
I tried this. I am not clever enough to get it to work, John. The attendance dates are stored as follows:
a drop down list displays the last date of the month (I.E January 30, 2004, February 27, 2004, March 30, 2004). The karate teacher then records the total number of attendances for that month. (I.E May 30, 2004 10 attendances)
So... all I need to do is use the query that works (returns the last date of their belt promotion) and somehow have a new query (or modify the old query) to count the number of attendances FROM the last date of belt promotion TO DATE().

Right now, the query counts ALL attendances... it does not start adding at that one specific date of the last belt promotion.
Have I been clear? I hope you or some other clever person can help me. THANK YOU SO MUCH FOR YOUR HELP.
Pat

Well, I was trying to manipulate the MONTH value based on the fact that you said
had Month and Year. Now it turns out that MONTH stores the entire date.
GUESSING that Month is a date field, then you should be able to modify the
proposed query to. Also, I forgot to add the GROUP BY clause at the end.


SELECT A.AidID, A.SNAME, SUM(ATT) as TotalAttendance
FROM Attendance As A INNER JOIN BELT as B
ON A.SNAME = B.SNAME
WHERE A.[Month] =
(SELECT Max([PROMD])
FROM BELT AS X
WHERE X.SNAME = A.SNAME)
GROUP BY A.AidID, A.SNAME

You might need to add the following to the where clause if you want to limit it
to records before the current date, but unless someone is adding ATT data that
WILL happen, it should not be needed.

AND [MONTH] = DATE()

By the way, if you get error messages when doing something - it is helpful to
include the error message in your request for help. I almost missed the fact
that I had forgotten the GROUP BY clause.

By the way, MONTH is a reserved word. That's why I've included it in BRACKETS
above. Otherwise, Access may confuse your Field "Month" with the Function
"Month" - which returns the Month number of a date.


PPerry wrote:

HOLY COW! Where did you learn to do that?
I tried this. I am not clever enough to get it to work, John. The attendance dates are stored as follows:
a drop down list displays the last date of the month (I.E January 30, 2004, February 27, 2004, March 30, 2004). The karate teacher then records the total number of attendances for that month. (I.E May 30, 2004 10 attendances)
So... all I need to do is use the query that works (returns the last date of their belt promotion) and somehow have a new query (or modify the old query) to count the number of attendances FROM the last date of belt promotion TO DATE().

Right now, the query counts ALL attendances... it does not start adding at that one specific date of the last belt promotion.
Have I been clear? I hope you or some other clever person can help me. THANK YOU SO MUCH FOR YOUR HELP.
Pat

John,
Darn it. I have tried to make this work, but to no avail. If you are willing (and have an exceptionally kind heart) could you take a look at this again. I created a query from the ATTENDANCE table and included the SNAME(Student name), ATT (attendance number) and MONTH (The actual date from each end of the month i.e. 4/30/2004) Then, I used this expression: SELECT A.AidID, A.SNAME, SUM(ATT) as TotalAttendance FROM Attendance As A INNER JOIN BELT as B
ON A.SNAME = B.SNAME WHERE A.[Month] = (SELECT Max([PROMD]) FROM BELT AS X WHERE X.SNAME = A.SNAME)GROUP BY A.AidID, A.SNAME

It returned the following error: The syntax of the subquery in this expression is incorrect. Check te subquery's syntax and enclose the subquery in parentheses.

John, I could hit a subquery with my car and not recognize it! Any more help you can provide would be SO appreciated. I am so sorry I am not better versed in Access. Thanks,
Pat