Query best practice or solution - question (1 Viewer)

[DWC3ZZZ]

Hello everyone. Thanking you in advance for taking the time to read (and with any luck) point me in a reasonable right direction.
This is my first post in the forum so here goes..

I have inherited a Access database for training of personnel. The bulk of the data is in a single table.
[emp ID], [Module], [dateCompleted], [TrainingSessionNo], and a few other irrelevant fields. The table spans over a dozen+ years. The personnel may have taken the same course a couple of times in 1 year or missed the training for a couple of years.

216	01.01	10-Aug-2020
216	08.01	15-Sep-2021
201	05.01	24-Nov-2024
212	01.01	07-Jan-2023
216	05.01	09-May-2019
178	03.04	28-Nov-2023
178	04.02	28-Nov-2023
178	06.02	04-Feb-2022
178	03.04	13-Dec-2023
216	01.01	23-Apr-2024

I think that will give you the general idea. What I have been asked to do is produce a report with the last date taken for any and all courses for each of the personnel within the last 2 years.

	01.01	03.04	04.02	05.01	06.02	08.01
201				24-Nov-2023
212	7-Jan-2024
178		13-Dec-2023	28-Nov-2023
216	23-Apr-2024

The data within the last two years is easy. I will query only the data (today's date - 2 years).

My first thought was to create a bunch of queries for each of the courses. ie. 01.01, 03.04 etc.. there are not really a great deal of them about and pull them into 1 query.
I am assuming there is a much simpler way to do this without creating a bunch of separate queries and linking them based employee ID? A cross tab query sparks my interest but I have not used them before.

Again, any and all suggestions will be greatly appreciated!

 

[Minty]

Yes a cross tab can probably do what you want.

Pull the fields you want into the query designer. (As per your list above)
Change the query type to crosstab

In the total selection select Group By for the emp_Id and Module
In the total selection for the DateCompleted select Max
In the crosstab selector select Row Heading for the empid, Column Heading for the module and Value for the date.

See what it looks like.

 

[Pat Hartman]

When you create the crosstab, you will choose the Max() option for the date.

 

[LarryE]

The bulk of the data is in a single table

Just my opinion, but before any queries can be developed, you should normalize your data into a relational format. From what you have said so far, it appears you will need:

1. An Employee table to hold employee data
2. A Module table to hold Module names and other data related to each Module
3. A TrainingSession table to hold data related to each training class or session within each Module
4. A TrainingDate table to hold the various training session dates
5. A TrainingCompleted table to hold each employees training completion result.

Each Employee may have training in multiple Modules
Each Module may have multiple Training Classes or Sessions
Each Class or Session may have multiple Dates they are taught
Each Training Session Date has one result for each Module for each Session for each Employee (Training was Completed Yes/No)

I don't know what a "Module" is, but I am using it as a category for training classes.

 

[plog]

You've got 2 things to accomplish--a logical one (finding the MAX date) and formatting (making the thing easy for others to consume).

I would accomplish the logical one in Access and the formatting one in Excel. This is the query you need in Access to find the MAX date:

SELECT [emp ID], Module, MAX(dateCompleted) AS LastCompleted
FROM YourTableNameHere
GROUP BY [emp ID], Module
ORDER BY [emp ID], Module

Run that and then copy that into a new Excel file on a tab called 'Data'. Then make a new called 'Report'. On the Report tab you make a pivot table (which is essentially a cross tab query but better). You can have the Modules as column/row headings and then employees as row/column headings or vice versa. You can then allow users to filter data, you can add totals, etc.

 

[arnelgp]

there are 3 queries i made (numbered according as to the rank i made them).
3_qryFinal will be your query (note access replaces your dot (.) with underscore (_) on the module number).

 

[GaP42]

Seems to me that, in line with @LarryE's comment on db structure, there is the question of can you identify those current employees that have not taken any training courses in the last two years. That question can be raised with whoever wants the report, and may illustrate the limitations of the current database that can be used leverage improvements.

 

[azhar2006]

You can create a query based on your table. Add the course date. Then create a report based on this query. Create a form with a start date and end date field through which the report for each trainee appears from the beginning of the year until today.

SELECT tblWorkshop.ID, tblWorkshop.Department, tblWorkshop.DeviceType, tblWorkshop.HardwareType, tblWorkshop.RepairDate, tblWorkshop.Notes
FROM tblWorkshop
WHERE (((tblWorkshop.RepairDate)>=[forms]![frmRpt]![txtDateFrom] And (tblWorkshop.RepairDate)<=[forms]![frmRpt]![txtDateTo]))
ORDER BY tblWorkshop.RepairDate;

 On Error GoTo Err_cmdReport_Click

    Dim stDocName As String

    stDocName = "RptWorkshop"
    
    If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then
        MsgBox "Please ensure that a report date range is entered into the form", vbInformation, "AZHAR"
        Exit Sub
    Else
        DoCmd.OpenReport stDocName, acPreview
    End If
Exit_cmdReport_Click:
    Exit Sub

Err_cmdReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdReport_Click