AutoUpdate

[JavaDude32]

Okay, I have a Access database that has 3 main index fields (course_Id, module_Id, question_Id). This is for storing student exams and I came up with a few solutions although currently it is just stored as 1 flat table.

Data Format is as follows:
Question AnsA ... Lesson_Reference course_Id module_Id question_Id.

Questions are looked up using a counter for question_Id with a static value for course_Id and module_Id. Questions are editted, created and deleted through online html forms and sql query objects (Students & Exam Writters never use the Access database program directly). My question is can I design the question_Id field or something to where question_Id acts an AutoNumber but a little differently? Basically the behavior I want is this:

Add a question
Questions 1, 2 & 3 already exist for module 1 course 1.
So new question has question_Id 4

Delete question #2
Make question #3 question #2 now and question #4 question #3

I want to avoid running multiple update queries to set all the higher question_Ids down on a deletion, instead, I'd like to issue 1 INSERT query or DELETE query and have the Access database handle re-numbering. I noticed AutoNumber doesn't reset all values down on a deletion. Queries are issued using OBDC with the Access Database as a data source with a DSN. Can anyone help me figure this out please?

 

[psemianonymous]

You either use Autonumber, which as you discovered doesn't necessarily sequentially number ID's, or you write a function (in your server-side scripts) that gets the next number yourself and do your own manual-autonumbering.

And what I'd recommend anyway, is that if you need to store "Rank", you should figure out a way to dynamically generate the rank in SQL, and store the ID independently of the rank.

 

[JavaDude32]

Alright, well I have a server script currently skipping all null queries and tracking when the assessment ends. I really can't see a way beyond a repetition of queries to enable the assessment authors to not worry about keeping up with what question # they are currently writing. This works although it makes the question the writer intended to be #3 show up as #2 if there isn't a #2 in the database for that course & module. Thanks for the input.

 

[psemianonymous]

Hmm, I didn't even think of them filling in the question numbers out of order. If they're doing that, then have an option that "autocompacts" their question numbers down to a complete, ordered list. Otherwise make them insert the question number themselves. Is that sounding right? If not, it's all good anyways.


Pete

 

[JavaDude32]

Yeah, right now, they insert the question number themselves and that is what I'm wanting to avoid. I think this can be done with AutoNumber until you have to delete, which I could get around by doing an update for each record with matching parameters, knocking the index down, but my problem is that this seems really inelegant and inefficient.
I want to track the question number programically without much hassle so the author doesn't forget a question somewhere or worse, overwrite a question they already created.

 

[lespaul]

Why not an autonumber field: QuestionID

and a field entered by the user: QuestionNumber

That way the user can have whatever question number they want, but you don't have to worry about what it is since your key will be the ID?

Leslie

 

[JavaDude32]

Do you mind explaining that please? I'm not sure I get it.

 

[lespaul]

From what I've read your table already has an autonumber as it's primary key and you've had problems with using that autonumber as the question number. So, add a field to your table that allows the user to assign a question number BUT keep your autonumber primary key.

Data Format is as follows:
Question AnsA ... Lesson_Reference course_Id module_Id question_Id....UserDefinedQuestionNumber

That way you give the users control over how the questions are numbered, ability to change the order if they choose by just changing the question number. Just make sure that when you output the questions, order by UserDefinedQuestionNumber.

Make more sense?

Leslie

 

[JavaDude32]

Oh! Sorry for the vagueness, no the question_Id is not a primary key yet nor an Autonumber, I merely suggested the way I thought of which as I have said just seems inefficient to me. Question_Id rather refers to where the question will appear in the assessment|exam.

With the Autonumber set up, each assessment would have to stored in its own table I think and I'm not sure if I can set up an AutoNumber Field using SQL through OBDC, but even that still leaves the prior problem of deletion. Please correct me if there is an easier way of doing this.

I don't really want authors to be able to input what question number the current question will be (seems like a hassle). I think it should be done automatically and sequentially. There's an edit question option for changing whatever question #. That way it ensures authors don't skip question numbers or pick a question # for which a question already exists. Any ideas, hopefully?