Recurring numeric sequence...

[M Davidson]

Hello all,

Here's my question:

Goal: Supervisor needs to be able to see missing daily trainee observation
reports in a numeric sequence. Example: "All reports have been entered from
for Trainee Smith except for Days 6 and 46, and Trainee Johnson's Day 41
report is missing.

Specifics:

The supervisor will create a 65 day training schedule for each Trainee (
01/01/04 = Day1, 01/03/04 =Day 2...). The Trainer will do a daily
evaluation of the Trainee. Each Trainee's Training Day #'s will fall on
different dates.

tblSupevisorSched
strTraineeID
datDay1 ---------Date for training day 1
datDay2 --------- Date for training day 2
<snip>
datDay65---------- End of Training, Day 65

tblDailyObservationReport
numDORSerial <-----this is the key field autogenerated,
strDORTraineeID <---- related to tblSupervisorSched!strTraineeID
datDORDate <----THIS IS THE COMPARISON FIELD.


How do I compare all of the datDORDate Dates to the Training schedule and
determine which observation reports have not been completed for each
recruit.

**OR**
Is there a way that when the trainer types in the date on the observation
report, that access will look up the coresponding "Training day #" for that
Trainee in the other table and and list it in the form, report or query?

I imagine this is a pretty simple situation, but it's got me whipped.

Thanks
Mike D.

 

[Marc]

tblSupevisorSched
strTraineeID
datDay1 ---------Date for training day 1
datDay2 --------- Date for training day 2
<snip>
datDay65---------- End of Training, Day 65

Part of your problem would be this table: It should be
tblSupervisorSched
strTraineeID - uniques off
datSequence - sequence number of day
datDay - date of training day

and then you have 65 records per trainee

tblDailyObservationReport
numDORSerial <-----this is the key field autogenerated,
strDORTraineeID <---- related to tblSupervisorSched!strTraineeID
datDORDate <----THIS IS THE COMPARISON FIELD.


How do I compare all of the datDORDate Dates to the Training schedule and
determine which observation reports have not been completed for each
recruit.

With the new new layout that is easier

**OR**
Is there a way that when the trainer types in the date on the observation
report, that access will look up the coresponding "Training day #" for that
Trainee in the other table and and list it in the form, report or query?

Go to queries - click on new query - select the option to find no matching
records, and add both tables

HTH
Marc

 

[John Vinson]

Hello all,

Here's my question:

Goal: Supervisor needs to be able to see missing daily trainee observation
reports in a numeric sequence. Example: "All reports have been entered from
for Trainee Smith except for Days 6 and 46, and Trainee Johnson's Day 41
report is missing.

Specifics:

The supervisor will create a 65 day training schedule for each Trainee (
01/01/04 = Day1, 01/03/04 =Day 2...). The Trainer will do a daily
evaluation of the Trainee. Each Trainee's Training Day #'s will fall on
different dates.

tblSupevisorSched
strTraineeID
datDay1 ---------Date for training day 1
datDay2 --------- Date for training day 2
<snip>
datDay65---------- End of Training, Day 65

Marc is exactly correct: your query is very difficult because your
table is not correctly normalized. "Fields are expensive, records are
cheap" - you should have as many RECORDS as you have training days,
*not* separate fields for each training day.

Restructure your table. This "spreadsheet" will be all but impossible
to search effectively.

 

[Bernie]

Mike,
Looks like you need to create a query, specifying the
criteria Is Null for the field where you log reports by
Days; this will show which days' reports are missing. You
can also sort them ascending. Do you have checkboxes, or
some similar method of recording each day's report as
submitted? Or, even if you actually enter the day number
in the field, as submitted, it will show which are null.
However, these all require that you have a separate record
for each day.
Bernie

-----Original Message-----
Hello all,

Here's my question:

Goal: Supervisor needs to be able to see missing daily trainee observation
reports in a numeric sequence. Example: "All reports have been entered from
for Trainee Smith except for Days 6 and 46, and Trainee Johnson's Day 41
report is missing.

Specifics:

The supervisor will create a 65 day training schedule for each Trainee (
01/01/04 = Day1, 01/03/04 =Day 2...). The Trainer will do a daily
evaluation of the Trainee. Each Trainee's Training Day #'s will fall on
different dates.

tblSupevisorSched
strTraineeID
datDay1 ---------Date for training day 1
datDay2 --------- Date for training day 2
<snip>
datDay65---------- End of Training, Day 65

tblDailyObservationReport
numDORSerial <-----this is the key field autogenerated,
strDORTraineeID <---- related to tblSupervisorSched! strTraineeID
datDORDate <----THIS IS THE COMPARISON FIELD.


How do I compare all of the datDORDate Dates to the Training schedule and
determine which observation reports have not been completed for each
recruit.

**OR**
Is there a way that when the trainer types in the date on the observation
report, that access will look up the

coresponding "Training day #" for that