Solved Query to Find Vacant Positions Using Start and End Dates (1 Viewer)

[lwvogan]

I have a database with the following tables: tblPeople, tblSpaces, and tblMMPeople_Spaces, which is attached. The tblMMPeople_Spaces table is to record assignment of people to authorized space and records the start and end dates of the assignment, if known. If not known, the start or end date may be blank, but to avoid dealing with null dates, I am assuming that no start date = 1/1/1950 and no end date = 12/31/2050.

I am trying to create a query that will tell me which spaces do not currently have a person assigned which is determined by using the start date, the end date, and the date when the query is run.

I have tried and cannot figure out how to do this. I have been able to define the set of records for when some is assigned but the opposite is more difficult, and designing the query has stumped me for a while so I thought I would ask for help.

Here is what I came up with for when a position has someone assigned: when at least one record in tblMMPeople_Spaces has a personID, the SpaceID, and the following start and end date scenarios:
No start or end date.
No start date and an end date that is in the future.
A start date in the past and no end date.
A start date in the past and an end date that is in the future.

Since a Space may have multiple people assigned both at the same time (overlap of people) and during different time periods, the definition is not easy. To avoid dealing with null dates, I am assuming that no start date = 1/1/1950 and no end date = 12/31/2050.

Case 1. Set of records in tblMMPeople_Space with no SpaceID. These shouldn't exist but, if they did, this would mean that there are records in the table with no spaces attached (orphans?).

Case 2. Set of records in tblMMPeople_Spaces with a specific SpaceID but no PersonID. If all records in tblMMPeople_Spaces with the specific SpaceID are also in this record set, the Space is vacant. If some records in tblMMPeople_Spaces with the specific SpaceID are not in this record set, it means they have a SpaceID and a PersonID and are handled under 3, 4, 5, or 6 depending on the start and end dates.

Case 3. Set of records in tblMMPeople_Spaces with a specific SpaceID, any PersonID, a start date of today or in the past, and an end date in the past. If all records in tblMMPeople_Spaces with the specific SpaceID are also in this record set, the Space is vacant.

Case 4. Set of records in tblMMPeople_Spaces with a specific SpaceID, any PersonID, a start date of today or in the past, and an end date of today or in the future. If any record(s) in tblMMPeople_Spaces with the specific SpaceID are also in this record set, the Space is not vacant.

Case 5. Set of records in tblMMPeople_Spaces with a specific SpaceID, any PersonID, a start date in the future, and an end date in the past. If all record(s) in tblMMPeople_Spaces with the specific SpaceID are also in this record set, the Space is vacant. This one would only occur if there was a data entry error.

Case 6. Set of records in tblMMPeople_Spaces with a specific SpaceID, any PersonID, a start date in the future, and an end date of today or in the future. If all record(s) in tblMMPeople_Spaces with the specific SpaceID are also in this record set, the Space is vacant.

I would really appreciate any help making this query.

Thanks

 

[isladogs]

SELECT tblMMPeople_Spaces.intSpaceID_FK, tblSpaces.chrSpaceJobTitle, tblMMPeople_Spaces.intPersonID_FK, tblMMPeople_Spaces.dtmPersonSpaceBeginDate, tblMMPeople_Spaces.dtmPersonSpaceEndDate
FROM tblSpaces INNER JOIN tblMMPeople_Spaces ON tblSpaces.idsSpaceID_PK = tblMMPeople_Spaces.intSpaceID_FK
WHERE (((tblMMPeople_Spaces.intPersonID_FK) Is Null)) OR (((tblMMPeople_Spaces.dtmPersonSpaceBeginDate)>Date())) OR (((tblMMPeople_Spaces.dtmPersonSpaceEndDate)<Date()))
ORDER BY tblMMPeople_Spaces.intSpaceID_FK;

BTW I strongly recommend you create relationships between the three tables and impose referential integrity

 

[lwvogan]

Thank you for the tip on creating relationships and enforcing referential integrity. I had to add a record to tblSpaces before Access would allow me to do the relationship and enforce referential integrity between tblMMPeople_Spaces and tblSpaces.

The query you gave worked in that it pulled out the records from tblMMPeople_Spaces that are vacant but there is a problem because tblMMPeople_Spaces allows assignment of multiple people to a specific space.

There are 11 spaces that have multiple corresponding records in tblMMPeople_Spaces (which is OK). SpaceIDs 3, 7, 18, 23, 48, 49, 52, 53, 56, 58, and 60 have at least one record in tblMMPeople_Spaces that is filled and at least one that is vacant. For those situation, the query needs to remove 3, 7, 18, 23, 48, 49, 52, 53, 56, 58, and 60 from the vacant results. I probably didn't explain that well in my initial post but that has been my difficulty and I can't seem to solve the puzzle.

Thanks.

 

[isladogs]

Hi
I thought you meant that but as it wasn't clear in the initial post, I went for the simplest option.
There are several ways of doing what you want.
This isn't the most concise way as it involves 3 queries but, by doing that, it should be clear how it works

1. First get a list of all SpaceID removing duplicates: qryAllSpaceID:

SELECT DISTINCT tblMMPeople_Spaces.intSpaceID_FK, tblSpaces.chrSpaceJobTitle
FROM tblSpaces INNER JOIN tblMMPeople_Spaces ON tblSpaces.idsSpaceID_PK = tblMMPeople_Spaces.intSpaceID_FK
ORDER BY tblMMPeople_Spaces.intSpaceID_FK;

2. Next identify all SpaceID that are currently filled: qryFilledSpaceID:

SELECT tblMMPeople_Spaces.intSpaceID_FK, tblSpaces.chrSpaceJobTitle, tblMMPeople_Spaces.intPersonID_FK, Nz([dtmPersonSpaceBeginDate],#1/1/1950#) AS StartDate, Nz([dtmPersonSpaceEndDate],#12/31/2050#) AS EndDate
FROM tblSpaces INNER JOIN tblMMPeople_Spaces ON tblSpaces.idsSpaceID_PK = tblMMPeople_Spaces.intSpaceID_FK
WHERE ((Not (tblMMPeople_Spaces.intPersonID_FK) Is Null) AND ((Nz([dtmPersonSpaceBeginDate],#1/1/1950#))<Date()) AND ((Nz([dtmPersonSpaceEndDate],#12/31/2050#))>Date()))
ORDER BY tblMMPeople_Spaces.intSpaceID_FK;

3. Finally run an unmatched query for all in qryAllSpaceID NOT in qryFilledSpaceID: qryVacantSpaceID:

SELECT qryAllSpaceID.intSpaceID_FK, qryAllSpaceID.chrSpaceJobTitle
FROM qryAllSpaceID LEFT JOIN qryFilledSpaceID ON qryAllSpaceID.[intSpaceID_FK] = qryFilledSpaceID.[intSpaceID_FK]
WHERE (((qryFilledSpaceID.intSpaceID_FK) Is Null))
ORDER BY qryAllSpaceID.intSpaceID_FK;

This gives a total of 17 records which hopefully covers what you actually want:

 

[lwvogan]

Thank you. That does exactly what I want. I hadn't thought of the need to do multiple queries to get to the solution to my problem. You have opened my eyes to a new way of thinking about queries. Again, thank you.

 

[arnelgp]

i have different result.
see query3.

 

[isladogs]

@lwvogan
You're welcome BUT...

@arnelgp has additional records as he used a left join between tblSpaces and tblMMPeopleSpaces so the end result includes SpaceID values where no record exists in tblMMPeopleSpaces. In other words Spaces that had never been filled. For example SpaceID=4.
I read your different cases in post #1 and instead used an inner join between the two tables as you didn't mention that possibility.

You need to look at the output from each approach and determine which solution fits your situation

 

[lwvogan]

Unfortunately the one @arnelgp sent didn't work the way I needed. There are 11 spaces that have had multiple people assigned over time. His Query 3 added five of these (the ones where the first assignment was current but the second was vacant) giving a total of 25 records in the result. The one @isladogs sent (PERSIS_CR_be.accdb) was close and I originally thought it worked. I went back and made a manual list of the assignments that were vacant (no currently assigned people) and found three spaces that had never had a person assigned. The only thing missing from your original qryAllSpaceID results were the three spaces that has never been filled (the spaces with no corresponding record in tblMMPeople_Spaces). I tinkered with qryAllSpaceID and changed it from an inner join to a left join. That change caused the three missing spaces to be included, brought the total results from 17 to 20, and the 20 were the correct vacant spaces.

I have attached the updated version with modified query. Thank you both for the help.

 

[isladogs]

Excellent. Glad you worked out the correct solution and thanks for posting it.
It may be useful to others in the future