automatic updates for Null records

[Gavroche00]

Hi all,

I have 100+ students that can come at any time during the week and turn in a report. I then enter their mark in my Access DB. However, some of the students don't show at all. I can find out who they are by querying from a master list with 'null' in the report field, but I would like something speedier.

Can I get Access VBA to compare the weekly list with the master list and create records for the 'null' students such that every student would have an entry every week?

If so, what are the first steps one goes through? or can you refer me to other threads, I couldn't find them.

Thanks

David

 

[willir]

Perhaps...[tt]
Select StudentID From tblStudent
Where StudentID Not In
(Select StudentID From tblMarks
Where DueDate >= YourParameterDate)[/tt]

Richard

 

[HenryAnthony]

First a bit of clarification. Please explain what you mean by speedier. With 100 students, is the query too slow or is it inconvenient to run it? Also, describe what the Master and Weekly lists (tables?) are. You should have only one table of students and a table of report grades and dates related to the students table. It would then be a simple matter to query by any date range.

Best regards,

Henry

 

[Gavroche00]

Henry,

Sorry for not being clear enough. I have a student table, and I have a grade table. The grade table has 3 fields:
studentID, WeekNo, and Grade.

What I would like is simple but I don't know how to start it. I wish for every student to have a grade. The ones that never turned in a grade for a specific week get an entry generated with 'No Grade' written in the Grade field for that specific week No.

As of now, I must enter all my grades, then queery for the students which are null, and then enter manually a 'No Grade' for them. I am trying to automate the process.

Thank you for your help/

David

 

[HenryAnthony]

David,

Could it be as simple as entering "No Grade" in the default value for the grade field? However, "No Grade" = Null so I do not see the difference.

What is the final result you are trying to achieve? Are you trying to identify students that did not hand in reports or calculate a cumulative grade? Is there only one grade per week or could there be many? Would it make more sense to use date rather than week number since there are many built in date functions to work with? If you stick with an individual week number, I think a separate week number table would be a better design. You could then relate the student ID to the week ID to the grade ID.

Henry

 

[Axoliien]

With regards to optimization, and since it was suggested in this post, you should avoid using NOT in queries, the NOT keyword causes the database compare every row, so try changing your logic to get rid of any NOT in your query if at all possible.

 

[Axoliien]

With your schema, you would need to do an OUTER JOIN to find students who do not have records and include them in your report. As Henry noted, the better option, since every student will have a grade for every assignment, is to add columns to your table.

It sounds like you don't want NULL values, and that you are using a text field and inputting an A to E or NO GRADE. As Henry said, setting a default of NO GRADE is your best choice so that everyone starts out with nothing. The way you have your table set up though, not everyone has to be in the grade table for every week. Your database schema is optimized for a design where you records of grades are optional. Anytime you want to have records of grades as required, you should just add another column with a default value.


A second note and clarification, the NULL value has NO value, so any record with a NULL will not factor into any aggregate functions you may run. Finding class averages, student averages, etc will not give you the correct output with NULL values in fields. As an example, let's say you have 10 grades and a student has 4 NULL values and 6 perfect scores. You use a SQL statement to find the student average by getting the COUNT of the number of items and then use that to find the average. You will only get 6 items and 6 grades, so the student will have a 100 percent. There are SQL functions to alleviate these errors.