Count of Null records

[mousemat]

Hi

Been taking abreak from Access for a few years now and cant get my head round my problem.

I have been asked to manipulate data from a Training Recods database.

Basically, training consists of 26 modules, there are currently 180 people who need training. Each person has been assigned a unique number, once a module has been completed, the date it was completed is entered into the database. There are currently lots of modules which havent been completed, what I need is a way of pulling that data from the DB.

I need to know how many people need to complete each module. For example, Module 1 might need to be completed by 15 people, Module 2 might need to be completed by 27 people etc.

I have enclosed the DB with the table and the form that I am looking to populate with the data.

Anyone have any ideas, I just can't get my head round it!!!!

 

[elbweb]

what i did:

on the form, change the data source to the table.
for each text box, renamed them to something other then the names of the database fields (causes circular references)
added the following control source to each text boxed (chaned field for the current one it wanted):

=DCount("[ID]","CompletedTraining")-DCount("[ID]","CompletedTraining","[Complete2]> #1/1/1#")

all it does, is counts the number of records, and then subtracts the number of records that have a date higher then 1/1/1 for the wanted field

 

[Brianwarnock]

That looks more like spreadsheet than an ACCESS database, and if it were the a simple =countblank(b2:ab181) etc would give the number of people to complete a course and similarly on the rows the number of courses to be completed by a person could be calculated.

Brian

 

[mousemat]

Sorry for the delay in getting back to the forum.

Have tried your solution and it works a treat. Many thanks for that

 

[elbweb]

always good to hear that someone's gotten the right answer