Results 1 to 2 of 2
  1. #1
    Russell is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2023
    Posts
    7

    Query to Show Date Ranges in Table 1 not Covered in Table 2


    I'm struggling to get this working, but I have the following 2 tables:

    tbl_Vacancy
    Fields: VacancyID, VacancyStart, Vacancy End

    tbl_SAForms
    Field: SAFID, SAFVacancy (Relates to tbl_Vacancy.VacancyID), SAFStart, SAFEnd

    Staff Vacancies represent in tbl_Vacancy will be filled in part or in full by entries in tbl_SAForms (Staff action Forms)

    I'm trying to get a query, which will report date ranges for a Vacancy listed in tbl_Vacancy are not covered by an entry in tbl_SAForms

    I have a query which will show me SAF's the relate to a vacancy, but I'd like to take it a step further and highlight the gaps.

    Any help?? Thanks in advance.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    I'm struggling to understand the concept.

    Surely you have a job role which is either populated with a person or not, and if not then it is vacant

    But in principle find all the records in vacancy where there is a related record in saforms with a start date greater that the vacancy start date and less than the vacancy end date - and then exclude those records from vacancy

    You can do this with a non standard join (which cannot be displayed in the QBE, has to be SQL. Something like this

    Code:
    SELECT Vacancy.*
    FROM Vacancy V LEFT JOIN SAForms S ON V.VacancyID=S.SAFVacancy AND S.SAFStart>=V.VacancyStart AND S.SAFStart<=V.VacancyEnd
    WHERE S.SAFID is Null
    You can make a start in the QBE - make the date joins as standard (jeft) joins. Then go to the SQL view and change the two date joins = to >= and <= respectively

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-19-2017, 06:10 AM
  2. Replies: 5
    Last Post: 12-12-2013, 12:14 PM
  3. Replies: 6
    Last Post: 09-01-2013, 08:17 PM
  4. Query based on value ranges in other table
    By amrut in forum Queries
    Replies: 14
    Last Post: 04-06-2013, 07:44 PM
  5. Replies: 2
    Last Post: 09-13-2011, 11:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums