By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,659 Members | 1,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,659 IT Pros & Developers. It's quick & easy.

Position Query, when start date blank

P: 7
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!
Attached Files
File Type: xlsx bytes.xlsx (8.1 KB, 258 views)
Jun 25 '12 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,365
It would help to see the query along with sample data and results.
Jun 25 '12 #2

P: 7
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.
Attached Files
File Type: xlsx bytes1.xlsx (8.5 KB, 301 views)
Jun 25 '12 #3

Rabbit
Expert Mod 10K+
P: 12,365
I still don't see the query code that you're currently using.
Jun 25 '12 #4

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. WHERE (((tblCEDAPJunction.PositionStop) Is Null)) OR (((tblCEDAPJunction.EmployeeID) Is Null));
  4.  
Jun 25 '12 #5

Rabbit
Expert Mod 10K+
P: 12,365
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.
Jun 25 '12 #6

P: 7
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.
Jun 25 '12 #7

Rabbit
Expert Mod 10K+
P: 12,365
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.
Jun 25 '12 #8

Rabbit
Expert Mod 10K+
P: 12,365
Or, now that I think about it, a simpler solution would be to move your WHERE condition into your JOIN.
Jun 25 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.