Query/Table question

[Ben Nicholson]

I have a table that contains all attempts made by all
students at a particular course. Records are unique on
the student's id number and the term in which the class
was taken.

What I would like to do would be to create a new field in
the table (probably through the use of a make table
query) which identifies what attempt number this record
is for that student so that I can look at all 2nd
attempts, 3rd attempts, etc. easily.

Is there an easy way to create an identifier for record
that match on one field (ID) and can be put in to order
on another field (TERM_CODE)

Thanks in advance.

Ben Nicholson

 

[Nikos Yannacopoulos]

Ben,

There are two legs to your question:
1. How to populate the attempt number field for existing records, and
2. How to auto-populate it when creating new records

On the first one: a make-table query is certainly not the way; were you
thinking of an update query maybe? Even so, it is quite tricky to do it
(frankly, I don't think I know how to). Personally, I would create the new
field manually, and then populate it through a small, simple piece of VB
code (assuming the term identifier is in such a format that can be
meaningfully sorted). If you provide the table and fields names and some
sample data, I will be happy to provide some sample code.

On the second one: I suppose you are using a form to enter new data; in that
case, you would use the On Change event of the control holding the student
ID number to fire a macro or a line of code that sets the attempt number to:
=DMax("[ID]","TableName") +1

HTH,
Nikos