Confusing Expressions with Percentage and Dates (Access 2010)

[PJmes]

Hi. I am trying to take my data base full of personnel and training tracked by dates. I need to be able to make a query or something similar showing a percentage of personnel for a specific training with non expired dates. This is to show how many people have up to date specificly for a certain training in a percentage. How do I do this?

[AlexHedley]

Try it in steps.
Create a query that gets just the ones you need that haven't expired first.
Once you have that move onto the next stage.

[June7]

A report could be best approach for this. Can calculate total in group and/or report footer and use that value to calculate percentages of individual records and/or group sections.

[Dal Jeanis]

The answer depends entirely on what your table layouts and relationships are.

1) How is "non-expired" training defined?

2) Do you want percentage of all personnel, or percentage of personnel who ever had that training?

3) Does all your training expire after the same time (1 year) or does it vary by course?

Assuming that your tables were set up like this...

tblStaff
   StaffID       AutoKey
   StaffStatus   Text    (A  = Active)

tblTraining
   TrainingID    AutoKey
   TrainingName  Text
   TrainingExp   Number (number of days training expires after)

tblStaffTraining
   StaffTrainID  AutoKey
   StaffID       Number (FK to tblStaff)
   TrainingID    Number (FK to tblTraining)
   TrainingDate  Date

Here's the total number of Active staff:

   SELECT Count(StaffID) AS TotActiveStaff
   FROM tblStaff
   WHERE StaffStatus = "A";

which in certain places can also be written like

DCount([StaffID],[tblStaff],[StaffStatus]='A')

This query returns one record each active staff who has unexpired training of each kind. This step is needed because if a staff member has two unexpired trainings of the same kind, you don't want to double-count him:

   SELECT 
      tblTraining.TrainingID, 
      First(tblTraining.TrainingName) As TrainingName, 
      tblStaff.StaffID
   FROM ((tblStaff 
          INNER JOIN 
          tblStaffTraining
          ON tblStaff.StaffID = tblStaffTraining.StaffID)
        INNER JOIN 
        tblTraining  
        ON tblTraining.TrainingID = tblStaffTraining.TrainingID)    
   WHERE StaffStatus = "A"
     AND DateAdd("d", tblTraining.TrainingExp, tblStaffTraining.TrainingDate) < Date()
   GROUP BY 
      tblTraining.TrainingID, tblStaff.StaffID;

Here's the count and percent of the number of staff members for each training above:

   SELECT 
      Q1.TrainingID, 
      First(Q1.TrainingName) As TrainingName, 
      Count(Q1.StaffID) As NbrTrained,
      DCount([StaffID],[tblStaff],[StaffStatus]="A") As TotalStaff,
      Count(Q1.StaffID)/(DCount([StaffID],[tblStaff],[StaffStatus]="A")) As PctTrained
   FROM 
     (
      SELECT 
         tblTraining.TrainingID, 
         First(tblTraining.TrainingName) As TrainingName, 
         tblStaff.StaffID
      FROM ((tblStaff 
             INNER JOIN 
             tblStaffTraining
             ON tblStaff.StaffID = tblStaffTraining.StaffID)
           INNER JOIN 
           tblTraining  
           ON tblTraining.TrainingID = tblStaffTraining.TrainingID)    
      WHERE StaffStatus = "A"
        AND DateAdd("d", tblTraining.TrainingExp, tblStaffTraining.TrainingDate) < Date()
      GROUP BY 
         tblTraining.TrainingID, tblStaff.StaffID
      ) AS Q1
   GROUP BY Q1.TrainingID;

Now, of course all this is probably Greek to you, but if you'll answer the question at the top, we can walk you through getting your answer, in short steps pretty much like the above.

[PJmes]

Thanks for the information Dal. I do know a little bit on coding. Not enough to keep up though.
Answering the questions may help.

1) Non expired training is training that is from today and on. This would be calculated on a monthly basis.
2) The percentage would be of all personnel for that one training that has had the training and is recorded as not expired from todays date.
3) The training expires depending on the date of completion. Training is good for two years after completion but that is extra information. It would be various expiration each time I ran this, not fiscal nor annual training required.

This will not need to calculate everytime I bring up the database, but only when I need to run it Monthly.

As for AlexHedley, I can run a query and get that information but I still need to be able to query it into a percentage without using a calculator. This will be done for multiple trainings to get a unique table to send up to the top boss each month to show what kind of percentage we are in for cetain training.

[June7]

In what format to you want to send to boss - a spreadsheet or a report? What you want can be done with a report structure.

[PJmes]

a report stating a few columns, the first column is the training name, second column has the status color coded (100-90% green, 89-80% yellow, 79-70 percent red, 69% and below black), third column is the percent complete, the other columns can be populated myself because they have nothing to do with access.

[June7]

The color coding should be doable with textbox Conditional Formatting.

The percentage calculation involves doing a sum calc in a group and/or report footer textbox then referencing that textbox in group and/or detail section textbox to calc percent.

No complicated nested subquery, Access report does the 'heavy lifting' with design features.

Save report as PDF. Send to boss.

[PJmes]

do you knowthe basic formula to do that ? having trouble figuring out this formula

[June7]

Need to get the report Grouping & Sorting properly set up.

The sum in footer textbox would be like: =Sum([fieldname])

The percentage in Detail section would be like: = [fieldname] / [textboxname] * 100