Query setup (Sum, Max, etc)

[scsuflyboy]

Topic title doesn't explain my issue, so here goes...



I have a form that a training coordinator uses. On this form are separate category entries (facility familiarization, equipment familiarization, communications, etc...there are 9 categories). Within each category the training coordinator enters the date of training, training description and number of hours of training. Each time training is done the training coordinator documents the training on this form. This is done many times throught the year.

Also on the form is a drop down to select the year (i.e., 2011, 2012, etc).

Here is what I need to do.

I want to create a report that will list ONLY the categories, the most recent date of training and the total number of hours for the year. The report would be based on a query of the table but I can't figure out how to set up the query. the table will have many entries but I want the user to enter the employee name, enter the year of training, then display a total of the hours for the year in each category and the most recent date of training for that category.

Any ideas or help would be greatly appreciated...Also please note that I am an EXTREMELY novice access user.

Thanks in advance.

[TheShabz]

Is this all on one table? If so,

SELECT Category, max(trainingDate) As MaxTrainingDate
FROM myTable
WHERE employeeName = Forms!FormName!controlName AND trainingYear = Forms!FormName!controlName;

You can bind that query to whatever you want (subform, Report, etc)

[scsuflyboy]

Can you explain a little more? Where do I put this information when I build the query...I didn't say this earlier but I am a novice access user (hesitate to even use the word "Programmer".

Here is a pic of my query if that helps...

[TheShabz]

The field names aren't very descriptive.
Tell me which fields holds the date of the training.

[scsuflyboy]

Airport Fam and Aircraft Fam are the fields that contain the date of training

[TheShabz]

Which one contains the date you want to check?

[scsuflyboy]

All of them...Also, these aren't all of the fields in the table. It is quite a large table. There are 13 or 14 categories in all.

I jsut provided a few to give you an idea of what it looked like and hopefully from an example I can figure out the rest.