Position Query, when start date blank

[lisaeileen]

Good Afternoon All.

I have a question - believe it might be something simple and I'm just not "seeing it."

I keep track of over 1K personnel - I want to run a query that gives me an alpha roster (all 1K by name etc). The roster looks good initially but those positions that are vacant (e.g. I had someone in there and they have since departed) do no show up on the roster. I'm sure the problem centers around the stop date on the junction table. I only want the roster to include those individuals who are currently sitting in a position (stop date is null) or the positions that don't have someone in them at all (start and stop date are null OR start and stop date are past dates) I just don't know how to do that.

I have two tables - a CEDAP position table and a CEDAP junction table that links the names up with the positions. I'm attaching an excel spreadsheet that has a quick snap shot of my two tables....the tables themselves are pretty simple. I must be doing something wrong!

[Rabbit]

It would help to see the query along with sample data and results.

[lisaeileen]

Sorry about that and thanks for the quick reply - here is an example. I included one line in red - it shows an individual who arrived and departed from a position - I need that position to show up on the roster as well but with a blank EMPLOYEEID. The roster should only show those individuals currently sitting in a positon and those positions that are currently vacant.

[Rabbit]

I still don't see the query code that you're currently using.

[lisaeileen]

Sorry, I confused again - Is this what you are looking for?

SELECT tblGarrison.Garrison, tblCEDAPPositions.GarrisonType, tblCEDAPPositions.GarrisonDutyTitle, tblCEDAPJunction.EmployeeID, tblCEDAPPositions.PositionLevel, tblCEDAPJunction.PositionStart, tblCEDAPJunction.PositionStop, tblCEDAPJunction.Remarks1, tblGarrison.Region1, tblDirectorate.Directorate, tblCEDAPJunction.ProjectedDeparture, tblCEDAPJunction.ProjectedDateofBackfill, tblGarrison.GarrisonLevel
FROM tblGarrison INNER JOIN ((tblDirectorate INNER JOIN tblCEDAPPositions ON tblDirectorate.DirectorateID = tblCEDAPPositions.DirectorateID) LEFT JOIN tblCEDAPJunction ON tblCEDAPPositions.CEDAPPositionID = tblCEDAPJunction.Position) ON tblGarrison.GarrisonID = tblCEDAPPositions.Garrison
WHERE (((tblCEDAPJunction.PositionStop) Is Null)) OR (((tblCEDAPJunction.EmployeeID) Is Null));

[Rabbit]

I see no historical records in your sample junction table, so why are you filtering on it? You only need to filter on it if you plan on holding history in there. And since there's no indication of that, there's no need for you to filter anything. If you take out the filter, it will return what you want.

[lisaeileen]

I am keeping historical data - this database is new - I'm just now starting to see people rotate in and out of the the positions.

[Rabbit]

What you'll want to do then is drop the where condition and use an aggregate query to get just the most recent records and then use that to join to the junction table to filter it down.

[Rabbit]

Or, now that I think about it, a simpler solution would be to move your WHERE condition into your JOIN.