Call parameter query from another query - Access SQL

Question

I have a table in MS Access which holds staff details (tblStaff):

| Employee ID    |  Employee Name |  Manager ID  |   Manager Name  |
--------------------------------------------------------------------
|     205147     |  Bobby Fischer |     201154   |   Alan Turing   |
|     205442     |  Carl Sagan    |     204468   |   Nikola Tesla  |

I refresh this table with new data weekly and if any employees have changed manager, then they are archived in another table (tblArchiveStaff) along with the dates that that record was valid from and to.

| Employee ID | Employee Name | Manager ID| Manager Name | DateFrom| DateTo|
----------------------------------------------------------------------------
|     205442  |  Carl Sagan   | 235663    |  Marie Curie |03/01/16|01/06/17|

I have written a parameter query (qryDatedStaffList) that will select records from either of these tables based on which ones were valid for a given date (SelectedDate)

SELECT 
    tblUnion.[Employee ID], 
    tblUnion.[Manager Name], 
    tblUnion.DateFrom, 
    tblUnion.DateTo
FROM 
    (SELECT 
        ts.[Employee ID], 
        ts.[Manager Name], 
        DateAdd("d", 1, Nz(ta2.MaxDateTo, #01/02/16#)) AS DateFrom, 
        Date() AS DateTo
    FROM 
        tblStaff ts 
    LEFT JOIN 
       (SELECT 
        [Employee ID], 
        max(DateTo) AS MaxDateTo
    FROM 
        tblArchiveStaff
    GROUP BY 
       [Employee Number]) ta2 
        ON ts.[Employee Number] = ta2.[Employee Number]

    UNION ALL

    SELECT 
        ta.[Employee ID], 
        ta.[Manager Name], 
        ta.DateFrom, 
        ta.DateTo
    FROM 
        tblArchiveStaff ta) AS tblUnion
WHERE
    SelectedDate Between [DateFrom] And [DateTo];

The above query works fine.

I am now trying to write another query that will return any employees whose manager has remained the same but their second line manager (manager's manager) has changed. At this point I'm getting stuck.

I'm guessing that I would need to enter the DateTo value of each record in tblChanges as the SelectedDate parameter in the qryDatedStaffList above but have no idea if this is possible?

Worst case scenario, I'm sure I could achieve this with VBA but am trying to stick to a pure SQL solution for performance reasons. Could someone point me in the right direction?

(p.s I am not necessarily asking anyone to write the query for me, I just want to know if there is a way to pass a value from a column in one query as the parameter in another query)

When I create any kind of variance reports with historical data, I often use a temporary table with one row with dates from-to instead passing them as parameters. It helps to simplify even very complicated queries. You can try. — Sergey S.
– Sergey S., Commented Oct 10, 2017 at 14:02
Hi Sergey. Thanks for your reply. I'm not sure I completely understand what you mean. Do you mean run the query for a specific date and then insert the results into a temporary table to work with. And then repeat this process for every date? — Leroy
– Leroy, Commented Oct 10, 2017 at 14:55
You could use a TempVars instead of a parameter. You can set them either through VBA or through a macro, and refer to them in queries. — Erik A
– Erik A, Commented Oct 10, 2017 at 15:16
Interesting. I haven't come across TempVars before. Just looked them up and they appear to be like a global variable? Not completely sure how I would apply that for the purposes of my question? — Leroy
– Leroy, Commented Oct 10, 2017 at 15:33
TempVars are global variables that can be used in queries, macros and VBA. The syntax is TempVars!MyVar, e.g. in VBA TempVars!MyVar = 10, and then in your query WHERE ID = TempVars!MyVar — Erik A
– Erik A, Commented Oct 11, 2017 at 8:00

Leroy · Accepted Answer · 2022-02-17 15:48:08Z

1

I did manage to find a pure SQL solution to this. I started by removing the date condition on the qryDatedStaffList in the original post. This gave me a list of every change to the structure (rather than just those valid for a specific day).

I could then simply write another query that referred to it which joined the stafflist to itself and would show any records where a manager had changed area but the employee(s) reporting to them had not been updated:

SELECT *    
FROM 
    qryDatedStaffList AS a 
INNER JOIN 
    qryDatedStaffList AS b 
ON 
    a.[Employee ID] = b.[Manager ID] 
WHERE 
    a.dateto between b.datefrom and b.dateto-1

edited Feb 17, 2022 at 15:48

answered Oct 13, 2017 at 14:50

Leroy

6441 gold badge10 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Call parameter query from another query - Access SQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related