Query A Table For The "latest workdate".

[jbesr1230]

Hello,
I am looking for a way to query a table for the "latest workdate".
The table fields would be: Project, Task (T1,T2,T3,etc) and
workdate. Within each project different tasks are worked upon at
different dates and each task may have many workdates associated with
it. I do not use access much and I am a novice with it. Much more
familiar with excel. I tried the expression builder using the maximum
function but could not get the results I need.

Thanks
JB

 

[John Spencer]

Is the table like the following
Project
TaskType - conatins a value T1, T2, etc
TaskDate - Contains the date relevant to the taskType.

Or do you have multiple task fields T1, T2, etc. with the workdate?
Project
Task1 - contains date of Task1
Task2 - Contains date of task 2

Max will work with the first table, but not with the second table structure.

You can use a normalizing union query to get the data into the correct form
and then use that query to get the latest (Max) workdate for each project.

qFixData:
SELECT Project, "Task 1" as TaskName, Task1 as TaskDate
FROM YourTable
UNION ALL
SELECT Project, "Task 2", Task2
FROM YourTable
UNION ALL
SELECT Project, "Task 3", Task3
FROM YourTable

Using the above saved query:
SELECT Project, Max(TaskDate) as LastActionDate
FROM qFixData
GROUP BY Project

 

[jbesr1230]

Thanks for your help. The data is in the correct format and I did
muddle my way through using MAX. However, I have run into another
problem. I have a 4th field column - Employee. It is the last
employee to work on the task that I am after. Right now in the query
grid I have
....Col 1 Project
....Col 2 Task
....Col 3 Workdate--using Max in the Total row
....Col 4 Employee name.

What I am getting is the last date each different employee worked on
Task 1, for instance. That is get multiple employees working on Task
1. What I want is only the latest employee.
Can you help with this?

Thanks

 

[John Spencer]

How about posting actual field and table names? Perhaps, the following will
work for you.

SELECT Project, Task, WorkDate, Employee
FROM YourTable
WHERE WorkDate =
(SELECT Max(WorkDate)
FROM YourTable as Temp
WHERE Temp.Project = YourTable.Project)