ok Access gurus what about this.

[trackmedic]

I would like to query a database that we keep our training records in.

the data kept in this DB is the name of department, the training and the date

I would like to query on the department and training that was not performed

for instance, our training records are 1-50. If someone did not do 48 and 49, I would like the query to key those departments

NEXT:

I am building a query by training. since some departments take the same training several times a year, I would like the query to show each department by the latest training. For instance if department 1 took training 49 10 times, all I want to see is department 1 and its latest date for training 49.

 

[Tiro Cupidus]

All of your training data is stored in a single table? Or is it set up in three tables? Like this:

tbl_Trainees
Trainee_ID
TraineeName
etc.

tbl_Training
Training_ID
TrainingName
TrainingNumber
Department
etc.

tbl_TraineesToTraining
Trainee_ID
Training_ID
TrainingDate

 

[trackmedic]

the material is in different tables that are pulled together. Basically I am looking for a way to find something that is not there. For instance, if training records 1-10 should have been completed by this date I would like t make a query that shows me employee x did not do training 3. I have a query that shows the person and all training. We have to go and physically look to see whats missing.

 

[khawar]

I have created a little sample in which i entered 10 Trainings and 10 Departments
which have to be trained so there are 10x10=100 trainings need to be performed
check the query TrainingStatus final it shows the data required by you
1- What are the trainings need to be performed
2- Which one are not performed (Highlighted in Yellow)
3- How many times each department has taken the specific training (If more than once highlited in pink)

I think thats all you Need

 

[khawar]

Sorry there was a wrong Join in my previous sample which I realized after uploading so download this ammended sample

Thanks

 

[trackmedic]

Thank you

I have used your example to build a query that does what your example can do. Can the query (per your example) be set just to show what training was not done instead of all the training with not done highlighted? As of now my report is over 400 pages long!

 

[khawar]

Its even easier to workout than previous one
Check Query TrainingsNotPerformed

 

[trackmedic]

Still does not work

The second example you sent worked great. I just want to get rid of the training records that have been done.

example, I have 115 teams that require training. I have 85 training modules.

I know that our vision team has missed only 3 trainings, yet, I still have 85 records showing on my report.

I would like the report to show only the name vision and the 3 records that are missing. i tried the null thing you sent and have 8000 records!

 

[khawar]

you must be using it wrong, on my sample data it works perfectly
Upload the db with your data I will check where the problem is

 

[jal]

Just wanted to say, Khawar, that you're disgustingly smart. Keep up the good work.

 

[khawar]

Thanks for compliments Jal