Earliest date in fields of the same record

[oni1983]

I have a query in which I am storing the details of trainees and their courses (including start and end dates). The trainee details and the course details belong to two different tables. Each trainee is entitled to a maximum of 10 courses.

I am now building a 'training history' of each trainee and I need to know when the trainee started training i.e. the earliest Start Date when the trainee started his first course.

I need to work this out via query (not a report) since I need to present the date in sheet format.

The query I have created consists of the details of the trainee and 10 columns, each showing the start date of that particular course. How can i compare the columns so that:

1. Either the dates are listed earliest first and latest at the end
2. The earliest date is shown and all the other dates are replaced with a blank cell.

 

[Joe4]

It is always best to show examples of what your data looks like, and what your want your expected results to look at.

It sounds like you will probably need to incorporate an Aggregate (Total) Query, where you include which fields you want to Group By, and then they field you want the earliest date on (you will use the MIN function here).

See here: https://support.office.com/en-us/ar...-a-query-b84cdfd8-07ba-49a7-b067-e1024ccfcca8
You would just choose to use the MIN function in place of the COUNT function in their example.

 

[oni1983]

This is what my data looks like:

Participant ID Number	Participant Name	Participant Surname	Course 1 Start Date	Course 2 Start Date	Course 3 Start Date	Course 4 Start Date	Course 5 Start Date	Course 6 Start Date	Course 7 Start Date	Course 8 Start Date	Course 9 Start Date	Course 10 Start Date
25632M	Aaaa	Bbbb
480356M	Samuel	Barbara	25/02/2017	02/03/2017	03/03/2017
510183M	Charmaine	Barbara	15/03/2017	02/03/2017	02/02/2017	26/03/2017			25/03/2017		02/03/2017

<caption> LMP - First Training Start Date </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

What I would like is to create a query in which the data shown is like the following (basically I am now being shown only the earliest date for each record):

Participant ID Number	Participant Name	Participant Surname	Course Earliest Date
25632M	Aaaa	Bbbb
480356M	Samuel	Barbara	25/02/2017
510183M	Charmaine	Barbara	02/02/2017

<caption> LMP - First Training Start Date </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

I have tried the Totals Query you suggested but unfortunately it is not working. The query is returning basically the same data without any filtering whatsoever.

 

[Joe4]

One big issue is that your data table is not normalized!
You should never have similar repeating data going across your table like that. It makes the data very difficult to work with (as you are currently experiencing).

You should have at least two tables with the following fields.

Participant_Table
Participant_ID (primary key)
Participant_Name
Participant_Surname

Participant_Course_Table
Participant_Course_ID (primary key - can use Autonumber if you like)
Participant_ID (from Participant_Table)
Course_Number
Course_Start_Date

Participant_Course_ID	Participant_ID	Course_Number	Course_Date
1	480356M	1	25/02/2017
2	480356M	2	02/03/2017
3	480356M	3	03/03/2017
4	510183M	1	15/03/2017
5	510183M	2	02/03/2017
6	510183M	3	02/02/2017
7	510183M	4	26/03/2017
8	510183M	7	25/03/2017
9	510183M	9	02/03/2017

<tbody>
</tbody>

So then the SQL code for the Aggregate Query would just look like:

SELECT Participant_Course_Table.Participant_ID, Min(Participant_Course_Table.Course_Date) AS MinOfCourse_Date
FROM Participant_Course_Table
GROUP BY Participant_Course_Table.Participant_ID;

And it would return data looking like:

Participant_ID	MinOfCourse_Date
480356M	25/02/2017
510183M	02/02/2017

<tbody>
</tbody>

To get it to return data in the manner that you like, you would save this Aggregate Query. and then do a Left Join from your participant table to this query, i.e.

SELECT Participant_Table.Participant_ID, Participant_Table.Participant_Name, Participant_Table.Participant_Surname, Min_Course_Date_Query.MinOfCourse_Date
FROM Participant_Table 
LEFT JOIN Min_Course_Date_Query 
ON Participant_Table.Participant_ID = Min_Course_Date_Query.Participant_ID;

and the results would look like:

Participant_ID	Participant_Name	Participant_Surname	MinOfCourse_Date
25632M	Aaaa	Bbbb
480356M	Samuel	Barbara	25/02/2017
510183M	Charmaine	Barbara	02/02/2017

<tbody>
</tbody>

 

[oni1983]

Wow, thanks for the detailed reply. I will change the way the data is presented in my table as you suggested and see how that works out.

Thanks again.

 

[oni1983]

It worked!!! Thanks for your help!!

But I have a small concern, I need to make sure that when the data is being inputted, the course number for the same participant is not the same i.e. there must not be two courses with course number 1 for the same participant but there can be more courses with course number 1 for several participant.

How can I do that?

 

[Joe4]

See: How to create a composite unique index (not as a primary key) in MS Access