Hi
i am trying to build a query which willaalow me to specify criteria of previous records
briefly- my db contains a table "grades" which has a fields as follows
test id
date
result
pupil id
class id

it records yhe marks of pupils of any class in a particular test. The tests belong to categries which are in another table categories and are related to the test table thus

category id
category
and

test id
testname
category id

I need to design a query which will enable me to specify criteria for the previous test for this class within any category

when I say previous I mean previous date. I worked for hours to create a query using the max of the date field and now it makes the recordset not updateable. I read somewhere that pat says max function do cause this! so
is there a better way to make the query?
or
if I must use max how can I override the recrdset not updateable problem
Many many thanks

Why would you want to return the last set of results and then try and add to it?

a long story but again briefly, sometimes not all the pupils of a particular class take that particular subject. I have devised a method that i enter -1 for that paticular test, then next time I am about to enter the marks for the next test in that category, it will only show me the records of those who in the PRevious test got >=0
Any other way would be appreciated
Thanx

You can either use DLookup on the query to display the information you want, or you can apply a filter to the form based on the criteria you specify, using the filter method should allow you to then enter additional records for the pupils concerned.

dlookup?
How can I tell it to look up and display only those values for which there is also an identical ID in a previous set of records?

Filter?
Again how can I filter if I don't have a field containing everyones previous mark

To sum up I run an append query to add the empty records for all of a particular class to the main db then I run a select to show those records but I only want to show those that got >=0 in the previous tes in this category. in the meantime many classes categories and tests have been entered into the giant db. Iwant to
a)find the previous test in the category for this class
b)see who got a negative mark for that test
c)select the records for this new test but not display the ones who got a negative mark last time
d)update all those who got a negative mark last time with a negative mark this time!
Thanks again for your time and interest

You've already built the query which returns the results for the last set of exam results, DLookup on the query via the pupil ID and on the test result will return the value to the form, you can use the returned values to filter the form.

In the table design, you can set the default of the test score to null. Access automatically sets the default for Numeric fields to 0 but you can change that by simply removing the 0. Then you would use Not Null as your criteria for selecting rows that have grades recorded and you would have no need to modify the records for which you do not have grades.

This method will require that you change any existing -1 values to null. But using nulls has the advantage that the aggregate functions such as Avg() ignore them. So if you have 4 scores entered and 1 is null, the sum is divided by 4 rather than 5 as would happen with your method.

Thanks for your replies
Rich "DLookup on the query via the pupil ID and on the test result will return the value to the form, you can use the returned values to filter the form" I don't quite understand how to pass the values of a dlookup to a form to filter using them. does that mean that I tell it "show all recordsthast the pupil id corresponds with one of the pupil id in the lookup" how do I do that??
Pat I need the null value for when a pupil does not take a test because he was absent Thats why I had to use -1 for pupils not in that group. I don't think it will affect the average because I can use criteria like >=0 to remove these records before any calculations -Is that not correct??
Thanks again
Happy YN

You must have some other way of identifying who was in the group. It can really cause data integrity problems later if you corrupt the meaning of fields by using them for purposes outside their scope. If you need another field, by all means add it. Don't just use some existing field.