Unable to count

[Somecallmejosh]

I have a table set up for a Karate class that keeps track
of rank and the date the new rank was earned.
Additionally, there is a table that contains the names of
the various ranks, which are pulled in to the rank test
table.

I am trying to do a count on the number of students who
fall into each rank category. However, in the rank test
table, a history of each new rank advancement per student
is maintained.
For example...

________________ Form from Main Student Info Table
[StudentID]: 312
[LastName]oe
[FirstName]:John
________________ Subform from Rank Test Table
[TestID] [TestDate] [Rank] [Pass/Fail]
1 09/15/03 White Pass
2 09/15/04 Yellow Pass

How can I ID this student as being a Yellow belt, as
opposed to being both a Yellow and a White Belt? I tried
to set up a query for the max date, as it applies to the
rank test. I set this up as a parameter query, but when
I select/type White, this student still shows up, even
though he is NOW a Yellow belt.

Thanks in advance.

 

[Roger Carlson]

The easiest way is with 2 queries. One to find the last test each student
passed (use Max, not Last) and join that back to the Rank Test table to find
the Rank. Like this:

Assuming the following two tables:
Student Info:
StudentID FirstName Lastname
1 John Doe
2 Robert Roe
3 Jane Fawn


and Rank Test:
TestID StudentID TestDate Rank PassFail
1 1 1/1/2003 White Pass
2 1 1/1/2004 Yellow Pass
3 2 1/1/2003 White Fail
4 3 1/1/2003 White Pass
5 3 1/1/2004 Yellow Fail


Create the query: qsubMaxTest:
SELECT [Student Info].StudentID, [Student Info].FirstName, [Student
Info].Lastname, Max([Rank Test].TestDate) AS MaxOfTestDate
FROM [Student Info] INNER JOIN [Rank Test] ON [Student Info].StudentID =
[Rank Test].StudentID
WHERE ((([Rank Test].PassFail)="Pass"))
GROUP BY [Student Info].StudentID, [Student Info].FirstName, [Student
Info].Lastname;

which produces this:
StudentID FirstName Lastname MaxOfTestDate
1 John Doe 1/1/2004
3 Jane Fawn 1/1/2003


Now, using this query, Join it back to the RankTest table on BOTH StudentID
AND TestDate

SELECT qsubMaxTest.FirstName, qsubMaxTest.Lastname, [Rank Test].TestDate,
[Rank Test].Rank, [Rank Test].PassFail
FROM qsubMaxTest INNER JOIN [Rank Test] ON (qsubMaxTest.MaxOfTestDate =
[Rank Test].TestDate) AND (qsubMaxTest.StudentID = [Rank Test].StudentID);

which will produce:
FirstName Lastname TestDate Rank PassFail
John Doe 1/1/2004 Yellow Pass
Jane Fawn 1/1/2003 White Pass


which you can turn into an aggregate (totals) query like this:

SELECT [Rank Test].Rank, Count([Rank Test].PassFail) AS CountOfPassFail
FROM qsubMaxTest INNER JOIN [Rank Test] ON (qsubMaxTest.MaxOfTestDate =
[Rank Test].TestDate) AND (qsubMaxTest.StudentID = [Rank Test].StudentID)
GROUP BY qsubMaxTest.FirstName, qsubMaxTest.Lastname, [Rank Test].TestDate,
[Rank Test].Rank;

to give you:
Rank CountOfPassFail
White 1
Yellow 1


There is another method that allows you to do it all in one query with a
subquery, but that is not as easy in my opinion. However, on my website
(www.rogersaccesslibrary.com) is a small sample database called
"MaxQueryProblem.mdb" which illustrates the problem and both solutions.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a table set up for a Karate class that keeps track
of rank and the date the new rank was earned.
Additionally, there is a table that contains the names of
the various ranks, which are pulled in to the rank test
table.

I am trying to do a count on the number of students who
fall into each rank category. However, in the rank test
table, a history of each new rank advancement per student
is maintained.
For example...

________________ Form from Main Student Info Table
[StudentID]: 312
[LastName]oe
[FirstName]:John
________________ Subform from Rank Test Table
[TestID] [TestDate] [Rank] [Pass/Fail]
1 09/15/03 White Pass
2 09/15/04 Yellow Pass

How can I ID this student as being a Yellow belt, as
opposed to being both a Yellow and a White Belt? I tried
to set up a query for the max date, as it applies to the
rank test. I set this up as a parameter query, but when
I select/type White, this student still shows up, even
though he is NOW a Yellow belt.

Thanks in advance.

 

[Somecallmejosh]

You are the man....
Thank you very much.

-----Original Message-----
The easiest way is with 2 queries. One to find the last test each student
passed (use Max, not Last) and join that back to the Rank Test table to find
the Rank. Like this:

Assuming the following two tables:
Student Info:
StudentID FirstName Lastname
1 John Doe
2 Robert Roe
3 Jane Fawn


and Rank Test:
TestID StudentID TestDate Rank PassFail
1 1 1/1/2003 White Pass
2 1 1/1/2004 Yellow Pass
3 2 1/1/2003 White Fail
4 3 1/1/2003 White Pass
5 3 1/1/2004 Yellow Fail


Create the query: qsubMaxTest:
SELECT [Student Info].StudentID, [Student Info].FirstName, [Student
Info].Lastname, Max([Rank Test].TestDate) AS MaxOfTestDate
FROM [Student Info] INNER JOIN [Rank Test] ON [Student Info].StudentID =
[Rank Test].StudentID
WHERE ((([Rank Test].PassFail)="Pass"))
GROUP BY [Student Info].StudentID, [Student Info].FirstName, [Student
Info].Lastname;

which produces this:
StudentID FirstName Lastname MaxOfTestDate
1 John Doe 1/1/2004
3 Jane Fawn 1/1/2003


Now, using this query, Join it back to the RankTest table on BOTH StudentID
AND TestDate

SELECT qsubMaxTest.FirstName, qsubMaxTest.Lastname, [Rank Test].TestDate,
[Rank Test].Rank, [Rank Test].PassFail
FROM qsubMaxTest INNER JOIN [Rank Test] ON (qsubMaxTest.MaxOfTestDate =
[Rank Test].TestDate) AND (qsubMaxTest.StudentID = [Rank Test].StudentID);

which will produce:
FirstName Lastname TestDate Rank PassFail
John Doe 1/1/2004 Yellow Pass
Jane Fawn 1/1/2003 White Pass


which you can turn into an aggregate (totals) query like this:

SELECT [Rank Test].Rank, Count([Rank Test].PassFail) AS CountOfPassFail
FROM qsubMaxTest INNER JOIN [Rank Test] ON (qsubMaxTest.MaxOfTestDate =
[Rank Test].TestDate) AND (qsubMaxTest.StudentID = [Rank Test].StudentID)
GROUP BY qsubMaxTest.FirstName, qsubMaxTest.Lastname, [Rank Test].TestDate,
[Rank Test].Rank;

to give you:
Rank CountOfPassFail
White 1
Yellow 1


There is another method that allows you to do it all in one query with a
subquery, but that is not as easy in my opinion. However, on my website
(www.rogersaccesslibrary.com) is a small sample database called
"MaxQueryProblem.mdb" which illustrates the problem and both solutions.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L

I have a table set up for a Karate class that keeps track
of rank and the date the new rank was earned.
Additionally, there is a table that contains the names of
the various ranks, which are pulled in to the rank test
table.

I am trying to do a count on the number of students who
fall into each rank category. However, in the rank test
table, a history of each new rank advancement per student
is maintained.
For example...

________________ Form from Main Student Info Table
[StudentID]: 312
[LastName]oe
[FirstName]:John
________________ Subform from Rank Test Table
[TestID] [TestDate] [Rank] [Pass/Fail]
1 09/15/03 White Pass
2 09/15/04 Yellow Pass

How can I ID this student as being a Yellow belt, as
opposed to being both a Yellow and a White Belt? I tried
to set up a query for the max date, as it applies to the
rank test. I set this up as a parameter query, but when
I select/type White, this student still shows up, even
though he is NOW a Yellow belt.

Thanks in advance.

.