Filter records [Challenging problem required in here.... Help]
I had a table in access with the following datas:
HTML Code:
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)
HTML Code:
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.......
Re: Challenging problem required in here.... Help
Can't you just add a "where" clause?
Like "...where training = 'rc'
Re: Challenging problem required in here.... Help
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!
Re: Challenging problem required in here.... Help
Here is the SQL statement
Code:
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
Re: Challenging problem required in here.... Help
Hi guys, thanks for ur input.
I had made a mistake and would like re-illustrate the the issue again.
Table as follow:
HTML Code:
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:
HTML Code:
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.
Re: Challenging problem required in here.... Help
In here, we have to include the [Name] in filter query. We do this by grouping the name. Here's the modified SQL statement
Code:
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
Code:
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
Re: Challenging problem required in here.... Help
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
Re: Challenging problem required in here.... Help
Quote:
Originally Posted by 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
Code:
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
Code:
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.
Re: Challenging problem required in here.... Help
Quote:
Originally Posted by 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
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.
