Hello I have a query problem and

[psimon1]

Hello

I have a query problem and was looking for help. Employee status may be on a table 5 times for an employee and only twice for another. I need the status before termination (the second most recent one). I can't hard code this. In other words, I can't say give me #2 of x. Can you think of a way though logic to give me the next-to-most-recent?

Thanks.

 

[geaker]

If a date is assoiciated with the status you could sort by status and date, get the record count and grab the recordcount-1. Or get the last record and use the function 'previous record' to rewind.

Hope this helps,
Gerald

 

[LittleSmudge]

Open a RecordSet in VB code using

rst.Open "SELECT TOP 2 EmployeeStatus FROM tableName WHERE EmployeeId = xxxx ORDER BY DateOfRecord DESC"

Then take the second record from the recordset



'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[scriverb]

I am going to assume that there is a field called EmpID, Status, and DateOfStatus:

Select A.EmpID, Last(A.DateOfStatus) as LastStatusDate
FROM tblYourTable as A
Order by A.EmpID, A.DateOfStatus
GROUP BY A.ID;

Create a query with the above SQL and name it qryLastStatus. Now use the following SQL which uses the above query as input:

Select A.EmpID, A.Status, Last(A.DateOfStatus) as DateOfStatus
FROM tblYourTable as A LEFT JOIN qryLastStatus as B ON (A.EmpID = B.EmpID) and (A.DateOfStatus = B.LastStatusDate)
GROUP BY A.EmpID, A.Status
HAVING ((Not (A.EmpID) Is Null) AND (Not (A.LastStatusDate) Is Null));

I haved to leave for a while and have not been able to setup some tables to test this but give it a try. Just run the last query and check out the results. If there is a problem get back with me. You could send me a small database with the table in it to my email. See profile. Then I could test it out and make sure it gives you what you want.

Bob Scriver

 

[scriverb]

Okay, ignore my previous post. We need three queries. First two setup the final one which will give you your expected result:
1. Create a query with this SQL and name it qryLastStatus:
SELECT A.EmpID, Max(A.DateOfStatus) AS MaxStatusDate
FROM tblEmpStatus AS A
GROUP BY A.EmpID;

2. Create a query with this SQL and name it qryLastDate:
SELECT A.EmpID, Max(A.DateOfStatus) AS DateOfStatus
FROM tblEmpStatus AS A LEFT JOIN qryLastStatus AS B ON (A.DateOfStatus = B.MaxStatusDate) AND (A.EmpID = B.EmpID)
WHERE (((B.EmpID) Is Null) AND ((B.MaxStatusDate) Is Null))
GROUP BY A.EmpID;


3. Create a query with this SQL and name it qry2ndFromLastStatus:
SELECT A.EmpID, A.Status, A.DateOfStatus
FROM tblEmpStatus as A INNER JOIN qryLastDate as B ON (A.EmpID = B.EmpID) AND (A.DateOfStatus = B.DateOfStatus);

You will have to update the queries with the correct table name. I used tblEmpStatus. Also, the field names may have to be updated. Just be careful to get them all. You can test the first two queries seperately to make sure you have the names all changed correctly without syntax problems.

When done you only need to run the final query for your results. Sorry about the previous post. I did that one on the fly without any data for testing. Let me know if you need any additional help.

Bob Scriver