Filter records [Challenging problem required in here.... Help]

[d190610]

I had a table in access with the following datas:

Name        Machine       Training_Type       Start_Date
alice          c              ojt               12/03/03
alice          c              rc                11/02/05
alice          c              rc                12/03/05
dennis         a              ojt               12/03/03
dennis         a              rc                11/02/05
dennis         a              rc                12/03/05
james          b              su                13/06/04
james          b              rc                11/02/05
james          b              rc                12/03/05

I'm able to sort them by Name> Machine> Date using following command:

Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & App.Path & filenametext(0) & "';Persist Security Info=False"

rs.CursorLocation = adUseClient

query = "select Trg_Records.Employee_Name, Trg_Records.Start_Date, Trg_Records.Machine, Trg_Records.Trg_Type from Trg_Records where Trg_Records.Year <= '" & now_year & "' order by [Employee_Name] asc, [Trg_Records.Machine] asc, [Trg_Records.Start_Date] asc;"

rs.Open query, Con, adOpenDynamic, adLockOptimistic

I would like to make query to only display Training Type "rc" with the latest date. (as shown below)

Name        Machine       Training_Type       Start_Date
alice          c              rc                12/03/05
dennis         a              rc                12/03/05
james          b              rc                12/03/05

Anyone know how can I do it? Thanks in advance.......

[RoyK]

Can't you just add a "where" clause?
Like "...where training = 'rc'

[Sabin_33]

I did something similiar to this a qhile ago
check it out here :

http://www.codeguru.com/forums/showthread.php?t=326988

....

If that doesn't help let us know!
Good Luck!

[rxbagain]

Here is the SQL statement

SELECT t.Name, t.Machine, t.Training_Type, t.Start_Date FROM (
   SELECT MAX(Trg_Records.Start_Date) AS Start_Date FROM Trg_Records
)  tFilter
INNER JOIN Trg_Records t ON t.Start_Date =  tFilter.Start_Date
WHERE t.Training_Type = 'rc'

You must be using the Date/Time data type in you Start_Date field to make it work correctly (I hope though you did it).

Hope it will help you

[d190610]

Hi guys, thanks for ur input.
I had made a mistake and would like re-illustrate the the issue again.

Table as follow:

Name          Machine        Training_Type                 Start_Date
Alice           a                 OJT                       12-02-03
Dennis          b                 OJT                       16-03-01
Dennis          b                 RC                        15-03-03
James           c                 SU                        17-03-99
James           c                 RC                        13-03-01
James           c                 RC                        15-03-03

The above are my company training records.
The training start from On-the-training(OJT) or Skill upgrading(SU) and need to do recertification(RC) after every 2 years.

Alice had not do the RC on machine a.
Dennis had completed the RC on machine b but the date is due this year.
James had also completed the RC on machine c but the date is also due this year.

Would like to check if:
1. the employee had do their RC on the machine they're operating
2. if completed RC, to check whether is due to date

Base on the table above, the result should be:

Name          Machine        Training_Type                 Start_Date
Alice           a                 OJT                       12-02-03
Dennis          b                 RC                        15-03-03
James           c                 RC                        15-03-03

Hope you guys can help me again. Thanks.

[rxbagain]

In here, we have to include the [Name] in filter query. We do this by grouping the name. Here's the modified SQL statement

SELECT t.[Name], t.Machine, t.Training_Type, t.Start_Date FROM (
   SELECT MAX(t.Start_Date) AS Start_Date, t.Name  FROM Trg_Records AS t GROUP BY t.Name
) AS tFilter 
INNER JOIN Trg_Records AS t ON t.Start_Date = tFilter.Start_Date AND t.Name =  tFilter.Name

I assumed that there the date would be enough to get what is the latest status. The "ojt" for example should have earlier date than the "rc" status.

If you want to include the [Machine] as filter, like if you want to list separate record if a user had 2 Machines, you can used this

SELECT t.[Name], t.Machine, t.Training_Type, t.Start_Date FROM (
   SELECT MAX(t.Start_Date) AS Start_Date, t.Name, t.Machine  FROM Trg_Records AS t GROUP BY t.Name, t.Machine
) AS tFilter 
INNER JOIN Trg_Records AS t ON t.Start_Date = tFilter.Start_Date AND t.Name =  tFilter.Name AND t.Machine = tFilter.Machine

Hope it wil help you

[trends]

HI,

Try this query.Is this the output what you require ?

select name,Machine,Training_Type,start_date
from emp_temp a
where start_date=(
select max(start_date)
from emp_temp
where name = a.name
)
order by name

[d190610]

In here, we have to include the [Name] in filter query. We do this by grouping the name. Here's the modified SQL statement

SELECT t.[Name], t.Machine, t.Training_Type, t.Start_Date FROM (
   SELECT MAX(t.Start_Date) AS Start_Date, t.Name  FROM Trg_Records AS t GROUP BY t.Name
) AS tFilter 
INNER JOIN Trg_Records AS t ON t.Start_Date = tFilter.Start_Date AND t.Name =  tFilter.Name

I assumed that there the date would be enough to get what is the latest status. The "ojt" for example should have earlier date than the "rc" status.

If you want to include the [Machine] as filter, like if you want to list separate record if a user had 2 Machines, you can used this

SELECT t.[Name], t.Machine, t.Training_Type, t.Start_Date FROM (
   SELECT MAX(t.Start_Date) AS Start_Date, t.Name, t.Machine  FROM Trg_Records AS t GROUP BY t.Name, t.Machine
) AS tFilter 
INNER JOIN Trg_Records AS t ON t.Start_Date = tFilter.Start_Date AND t.Name =  tFilter.Name AND t.Machine = tFilter.Machine

Hope it wil help you

Thanks for ur help. I tried ur method but it give the following error:
"Join expression not support"

Not sure what went wrong.

[d190610]

HI,

Try this query.Is this the output what you require ?

select name,Machine,Training_Type,start_date
from emp_temp a
where start_date=(
select max(start_date)
from emp_temp
where name = a.name
)
order by name

tis is not I want. An employee might learn a few machines. The output should display the latest date on training type on each machine for each employee. Anyway thanks for ur input.