Using VBA to alter a report

[Eric B]

Please help…

I am in the process of creating a database for tracking missing
schoolwork. I have two tables that I am trying to create a report that
lists students with missing work. The problem I am having is I only
want to list the missing work. I am able to remove students who do not
have any missing work. However, I would like to include just the
periods where the student has missing work (to make for a smaller report
and to make it easier to read.)

Example:

Student Class Teacher Assignment
Doe, John Am Hist Smith pg 43 1-5
Pre-Alg Jones pg 413: 2-22 (e)

If a student doesn’t have any missing work for a period, I don’t want it
listed. (The page layout for the report is landscape, so if it is
possible, I wouldn’t mind having the option to list 2 or 3 periods
(class, teacher, assignment) on the same line.)

Table 1: Student Info [St_id (key); St_Last_Name; St_First_Name]

Table 2: Missing Info [St_id (key); Missing Work; 1_Missing; 1_Class;
1_Teacher; 1_Assignmet; 2_Missing…. 8_Missing; 8_Class; 8_Teacher;
8_Assignment]

Description of table 2:
Missing_Work is a yes/no field, if any of the teachers send in
missing work for any period this sets to yes.
#_Missing is a yes/no field for if there is missing work for that period
#_Class holds the name of the class
#_Teacher holds the name of the teacher
#_Assignment holds the missing assignment

My thought is to use VBA and If/then routines… I could probably stumble
through the code to sort the records, but I am lost on how to code this
to place it in the report.

Each teacher will have a separate database where they keep track of
their individual students and they will update the master database. I
included a field for each period because that was the easiest way I
could think of for teachers to be able to modify missing work without
fear of overwriting or removing what another teacher had already added.

I am brand new to Access (this is my first project) and I would like to
consider myself advanced beginner/moderate with VBA (more of a hobby.)

Thanks again, and sorry for the novel.

 

[Duane Hookom]

It looks like your table structure is not normalized. Fields with 1, 2, 3,
....8 is a red flag. I would expect there would be a table of assignments and
then a table of student assignments. If your table structure was normalized,
you could fairly easily create a query that lists incomplete work. It
wouldn't take any VBA or fancy stuff, just a little SQL knowledge.

IMHO, I would not continue investing time into a solution where the table
structure is wrong.