Return max date of multiple columns (1 Viewer)

[jimtimber]

Hi there,

How do you return the most recent date of multiple columns.

I have a table (tbl_courses) that has a list of training courses. We want to know when a client completed the course most recently.

The problem is, for one course there has been up to 4/5 different variations of the course with different names over the years. E.g. "Drug awareness" has also been known as "Drug Aware" "Illegal Substances" and "Stoppers". I want to pull through the most recent date for all of the above.

We have a field in the Courses table that links the courses into groups (e.g. All drug aware courses come under "23"). Not sure if that helps?

Is there a way to do this? The Tbl_Courses is linked to Tbl_Clients via a ClientID.

I've managed to do it in SQL using GREATEST() but that isn't an option in Access.

Thanks

 

[CJ_London]

Is there a way to do this?

There is always a way but you need to provide more information if you want a fuller answer.

can you provide some example data plus the required outcome- the way you are describing it sounds like you have a table with a column for each course?

 

[jimtimber]

Thank you for your reply. We want to produce a spreadsheet of users in the first column, with the different courses in the following columns.

E.g., this would be the desired output (see attachment).



CLIENTID comes from Tbl_Client.clientID
Client Name from Tbl_Client.Name
The dates are stored in Tbl_enrolment.attendeddate. Tbl_enrolment and tbl_Client are linked via CLIENTID.
All course titles (Drug aware, MH Aware, etc.) comes from Tbl_Courses.coursetitle. Each course has a unique ID number (tbl_Courses.courseID e.g."345") and, as my 1st post, those grouped together have a course group (tbl_courses.Group e.g. "02").

Is anything else required? I can't paste any SQL as I haven't written the query yet as not usre how to find MAX date for the different course titles that form 1 group.

Thanks again,

J

 

[CJ_London]

OK - I can see the desired output, but still not clear on the input data. Can you provide details of Tbl_enrolment - field names and some example data.

Also, just to be clear, do you want 'null' (as opposed to being left blank) to appear when there are no dates - this will have an impact on how you get the result.

I would expect the query to be a crosstab but need to see tbl_enrolment to confirm

 

[jimtimber]

Please see the attached for sample of tbl_enrolment.

I've highlighted in green what is causing a problem. These 3 courses all have different CourseIDs but they are one and the same, just with different names. The client has attended the course 3 times over the years but I just want to pull through the most recent date (i.e. 26/01/2014).

As for null values, there are quite a lot of clients that have not sat certain courses. I'd like it to say "not attended" if possible?

Thank you for your help.

 

[CJ_London]

OK, then you will need something like this

TRANSFORM Max(Nz([Date Attended],"Not Atttended")) AS Expr1
SELECT tbl_Client.ClientID
FROM tbl_Client LEFT JOIN tbl_enrolment ON tbl_Client.ClientID = tbl_enrolment.ClientID
GROUP BY tbl_Client.ClientID
ORDER BY tbl_Client.ClientID
PIVOT tbl_enrolment.CourseGroup;

 

[jimtimber]

Hi CJ,

Cheers for this, i'll give it a go in the next day or two and see if it works(waiting for a data transfer at present!)

Thanks again, really appreciated.

J

 

[monkeyman77]

Is it possible to do this with out using code?

 

[CJ_London]

only by using pen and paper - if you are going to hijack a 7 year old thread at least be clear about what it is you want to do