Testing Scores

[helpaccess]

I am a new user of Access and am having difficulty figuring out what type of function I would need to get the data I need. We have a database of scores, which are not numbers, for students on tests taken on different dates. We need to know:
1. Number of students with a higher score than their last score
2. Number of students with a lower score than their last score


3. Number of students with the same score as last score
4. Number of students with a score this month but no previous
5. Number of students with no score this month

The issue of scores not being numbers makes it difficult to compare what is higher since a regular > operator will not work. Any help would be much appreciated.

[June7]

Even if the scores are in a text field, it is still possible to treat them as numbers.

But if you still have problems, convert value to a number. Use Val or Clng.
Example: Val([field1]) > Val([field2])
Use in a query or textbox of form or report

[helpaccess]

Thank you! Another problem is the test scores are not separated into 2 fields because tests are given at all different times. So there is a date field and then a test field. The ID for the database ties in to the student's name in another database. One student will have several ID's for the several tests she has taken. In this case, how do I tell Access to compare an previous score to the most recent?

[June7]

The only requirement in your list that will be easy is no.5

Comparing data between records is complicated. One way is with VBA and recordsets. Without VBA might be doable with a series of queries and/or domain aggregate functions (DMax, DLookup). I have never tried to build something like this.

As an example, here is no.4. This assumes current month is February (2) and returns all students meeting the criteria, then use this query as basis for a Totals summary.
SELECT Student, Month([TestDate])=2 AS CurrMonTest, DLookUp("Month(TestDate)","Table1","Month(TestDate )<>2 And Student='" & [Student] & "'")<>2 AS PrevMonTest
FROM Table1
WHERE (((Month([TestDate])=2)=True) AND ((DLookUp("Month(TestDate)","Table1","Month(TestDa te)<>2 And Student='" & [Student] & "'")<>2) Is Null));

The other 3 could be even more difficult.

Might be able to PIVOT the data with a CROSSTAB query.
TRANSFORM First([Score]) AS FirstOfScore
SELECT [Student]
FROM Table1
GROUP BY [Student]
PIVOT Format([TestDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

All is further complicated if data is multi-year.

[helpaccess]

Thank you so much!