totals by month, year

[danwand]

Hi,

I have a training database which holds info regarding 4 separate training activities: intro training, visit training, learning time and additional topics. Each table contains the following fields: ??ID (so ITID, VTID, LTID, ATID), TopicName, ProcedureRefered.

I also have a staff details table and a corresponding staff??Training table for each of the above mentioned training activities (so tblSINTrain, tblSVTrain, tblSLTTrain, tblSATTrain)

Each StaffTraining table contains the following fields: StaffID, TrainingID, TopicDate(short date), TimeTaken(Integer value), Done (yes/no).

What i want is to be able to do is produce a form or report that breaks down the total time (sum of TimeTaken) for each of these training activities for a selected employee for each month in a given year for all topics marked as Done = 'yes'.

So the form/report whould have the following controls/fields:

Select Employee:combobox - easy, can do.

Select Year: combobox (based on a query of all staff training activities to find distinct year part of all Date fields for selected employee) - not sure how to do this!


Intro totals Visit Totals LT Totals AT Totals
Jan
Feb
March
April
May
etc...

Now do i need to do a separate query for each table and then again to find each month total? or can all the stafftraining tables be joined?

Not really sure where to start, any help would be appreciated. Please write for any confirmation of description needed.

Thanks Dan

 

[Crowley16]

use a query to select all relevant records, and then split the results into the relevant month...

select *, month(topicDate) from staff training where staffid = [employee combo box] and Year(topicDate) = [date combo box]

then use a repot bound to this query, and set the month as a group, and show the totals for each group...

--------------------
Procrastinate Now!