Web Development

Ask something
Discussion Locked

Need Advanced MS Access Help

0 helpful
4 replies

by bristar

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to setup an HR database that will tell me when an annual review is due for each employee. Here's the trick: Some employees have left and returned, thus setting their review back to compensate for time lost. For example, If an employeeis coming up on one year, it would be offset by three months if they took a three-month leave of absence (LOA) earlier.

I currently have all employees and statistical data in one table, I have all employee actions (hire, terminations, re-instatements, LOA's, etc..) in another table linked by their employee number.

I also need to be able to see if they are even a current employee by checking for a termination without a re-instatement, or if they are employed, but not active because they are on LOA.

How can I look through the list of dates per employee number to see if they have a termination date, then check for a re-hire date, then calculate their time less the time they were gone, then check for multiple instances thereof?

4 replies

by john_wills

The following code detects whether a hotel room is currently occupied, essentially the same idea as an employee being occupied with the firm. It treats a null termination date as a maximum. You will have to put this in something at a higher level.
SELECT HotelID AS ViewHotelID,UnitID AS ViewUnitID,MaxMoveOutDate
FROM (SELECT HotelID,UnitID, Max(MoveOUtDate) AS MaxMoveOutDate
FROM Sojourn AS SojournDone
WHERE NOT EXISTS(SELECT * FROM Sojourn
WHERE UnitID=SojournDone.UnitID
AND HotelID=SojournDone.HotelID AND MoveOutDate is null
)
GROUP BY HotelID,UnitID
)UNION
(SELECT HotelID,UnitID,Null as MaxMoveOutDate
FROM Sojourn
WHERE MoveOutDate is null
);
I really think you will need an intermediate table with a total of sojourn times.

by bristar

Actually, the logic of getting it to work was the easy part. I already have an SQl statement for it, the problem is getting Access to read in the SQL as a Recordset so I can parse it properly. I keep getting a 3061 error. If I can open the recordset (queried for the specific user), then I can read in the dates one record at a time and gather proper date information from that using DateFill.

by bristar

Alright, I do know the logic, so let's modify the question...

Whenever I open a table as a recordset, then do a recordset.recordcount, it gives me the total number of records. Whenever I open a query or SQL statement as a recordset, it only returns a recordcount of 1. What's missing here?

This discussion is closed to new replies.