CODEWITHSUNDEEP
databasems-access
Robert M
Robert M

Reputation: 71

Bring Two Database Queries Together

I'm using an Access database to track my personal annual & sick leave (AL/SL) balances as I accrue and use leave hours.

I have one query which determines what pay periods are eligible for earning leave and shows each period with the amount of leave earned. (Currently, 4 hours each for AL/SL for each period.)

--- qryEarned Leave ------------------------
Period      PP      Code    Earned
2/14/2015   03-1    AL      4
2/14/2015   03-1    SL      4
2/28/2015   04-1    AL      4
2/28/2015   04-1    SL      4
3/14/2015   05-1    AL      4
3/14/2015   05-1    SL      4
3/28/2015   06-1    AL      4
3/28/2015   06-1    SL      4
4/11/2015   07-1    AL      4
4/11/2015   07-1    SL      4
4/25/2015   08-1    AL      4
4/25/2015   08-1    SL      4
5/9/2015    09-1    AL      4
5/9/2015    09-1    SL      4
5/23/2015   10-1    AL      4
5/23/2015   10-1    SL      4
6/6/2015    11-1    AL      4
6/6/2015    11-1    SL      4
6/20/2015   12-1    AL      4
6/20/2015   12-1    SL      4

A second query pulls data showing when I used leave.

--- qryUsedLeave ---------------------------
Period      PP      Code    Total
3/21/2015   05-2    SL      10
5/16/2015   09-2    AL      8
5/23/2015   10-1    AL      24
6/20/2015   12-1    SL      8

What I want to do is combine both of these queries, such that the "Total" from used leave is listed after "Earned" leave for each period:

--- Desired Result -------------------------
Period      PP      Code    Earned  Used
2/14/2015   03-1    AL      4       -
2/14/2015   03-1    SL      4       -
2/28/2015   04-1    AL      4       -
2/28/2015   04-1    SL      4       -
3/14/2015   05-1    AL      4       -
3/14/2015   05-1    SL      4       -
3/21/2015   05-2    SL      -       10
3/28/2015   06-1    AL      4       -
3/28/2015   06-1    SL      4       -
4/11/2015   07-1    AL      4       -
4/11/2015   07-1    SL      4       -
4/25/2015   08-1    AL      4       -
4/25/2015   08-1    SL      4       -
5/9/2015    09-1    AL      4       -
5/9/2015    09-1    SL      4       -
5/16/2015   09-2    AL      -       8
5/23/2015   10-1    AL      4       24
5/23/2015   10-1    SL      4       -
6/6/2015    11-1    AL      4       -
6/6/2015    11-1    SL      4       -
6/20/2015   12-1    AL      4       -
6/20/2015   12-1    SL      4       8

What can I do to bring these queries together to produce the result I want? I'm sure this has to be a rather simple thing, but I just can't wrap my head around it. (Currently I just have a VBA module which inserts the first query into a table, then reads the second query to either update the row or insert a new one, and calculates starting/ending balances)

Upvotes: 1

Views: 26

Answers (1)

Andre
Andre

Reputation: 27644

First step is to create a LEFT JOIN query from qryEarned to qryUsed on PP.

This will give you all rows of qryEarned with the matching qryUsed data, but missing the extra qryUsed rows (e.g. 05-2).

If you cannot add these periods to the "Earned" data (that would be easiest), create a second LEFT JOIN query, this time the other way around, and a criteria qryEarned.PP IS NULL. The query assistant for inconsistent or non-matching data can create such a query for you.
This will give only the missing rows from qryUsed.

Finally use a UNION query to put both together.

Upvotes: 1

Related Questions