How do you UNION several tables that all have a date field grouping them by date?

Question

I have a few tables in an Access db: Expenses, Payments, Third Party Costs, and Hours. They have different field structures, but they share a date field, aptly named Expenses.[Expense Date], Payments.[Transaction Date], [Third Party Cost].[Cost/Income Date] and Hours.[Date Worked]. They also share a field named Project, which helps correlate an expense/payment/etc to its respective project. Tables look as follows:

Expenses
ID  Project         Employee        Expense Date  Description  Expense Code   Amount \\
1   Test Project 1  Test Employee 1  7/17/2019     Dinner       Meal           50     \\
2   Test Project 2  Test Employee 2  7/17/2019     Taxi        Transportation  20     \\    

Payments
ID & Project         Invoiced Amount  Transaction Date  Payment Amount  Invoice No.  Payment Doc No. \\
1   Test Project 1    100               7/17/2019                            InvNo1                       \\
2   Test Project 2                      7/21/2019            100                       PayDoc1      


Third Party Costs
ID  Project         Employee         TPC  TPI  Cost/Income Date  Cost Type       \\
1   Test Project 3  Test Employee 3  100  0    7/16/2019         Catering        \\
2   Test Project 1  Test Employee 1  100  200  7/19/2019         Location Rental 


Hours
ID  Project         Employee         Date Worked  Hours Worked \\
1   Test Project 1  Test Employee 1  7/16/2019    2            \\
2   Test Project 2  Test Employee 2  7/19/2019    1            \\
3   Test Project 1  Test Employee 2  7/21/2019   & 1   

What I am trying to do, but failing, is creating a UNION query that will bring all the information from all these tables together, ordering it by date, with an expense, a payment, a third party cost, or a worked hour being on different lines even if they share the same date.

A bonus would be being able to group them by date and project, so I could for example see exact activity for a certain project on a specific date.

So the desired result would be something like this:

Results query
ID & Date      & Project        & Expense Amount & Expense Code & Invoiced Amount & Payment Amount & TPC & TPI & Cost Type       & Employee        & Hours Worked \\
1  & 7/16/2019 & Test Project 1 &                &              &                 &                &     &     &                 & Test Employee 1 & 2            \\
2  & 7/16/2019 & Test Project 3 &                &              &                 &                & 100 &     & Catering        & Test Employee 3 &              \\
3  & 7/17/2019 & Test Project 1 & 50             & Meal         &                 &                &     &     &                 & Test Employee 1 &              \\
4  & 7/17/2019 & Test Project 1 &                &              & 100             &                &     &     &                 &                 &              \\
5  & 7/17/2019 & Test Project 2 & 20             & Taxi         &                 &                &     &     &                 & Test Employee 2 &              \\
6  & 7/19/2019 & Test Project 1 &                &              &                 &                & 100 & 200 & Location Rental & Test Employee 2 &              \\
7  & 7/19/2019 & Test Project 2 &                &              &                 &                &     &     &                 & Test Employee 2 & 1            \\
8  & 7/21/2019 & Test Project 1 &                &              &                 &                &     &     &                 & Test Employee 2 & 1            \\
9  & 7/21/2019 & Test Project 2 &                &              &                 & 100            &     &     &                 &                 & 
\\   

Basically it would select certain fields from every table and group them by date.

So far I've only managed to confuse myself more by reading documentation on UNION queries, attempting to write that as code, not getting the expected result, rereading, reattempting, confusing myself some more, and so on, in a circle.

Can anyone please suggest a reasonable way for what I'm trying to do? I'm assuming it's not the most complex of tasks, but I'm just getting the hang of SQL.

Answer 1

Unions in SQL are used to vertically join data, for example if you have two tables:

A:

| Name | Date |
| Bob  | 1234 | 
| Fred | 4321 |

B:

| Name | Date |
| Mark | 7890 | 
| Test | 6543 |

If you union these two tables you will end up with:

| Name | Date |
| Bob  | 1234 | 
| Fred | 4321 |
| Mark | 7890 | 
| Test | 6543 |

If you are looking to do this then you can only union on columns with exactly the same name and structure, so if you look at my example I have two tables with both name and date fields, these are good candidates to be unioned.

If you are wanting to pull through the other columns in the tables you would need to look into a join rather than a union.

I hope this helped.

Answer 2

Generally, for union queries, column types and column numbers must match between underlying SELECT statements. Additionally, UNION will return de-duped records across all concatenated records while UNION ALL will return all concatenated records including duplicates.

Specifically, for your needs, consider adding a scalar string with UNION ALL if combining multiple tables to differentiate line items across data sources.

SELECT 'Expenses' AS [Line Item], e.[Expense Date] AS [Date], e.[Project], e.[ExpenseColumn] As Metric
FROM Expenses e

UNION ALL

SELECT 'Payments', p.[Transaction Date], p.[Project], p.[PaymentColumn]
FROM Payments p 

UNION ALL

SELECT 'Third Party Cost', t.[Cost/Income Date], t.[Project], t.[CostColumn]
FROM  [Third Party Cost] t

UNION ALL

SELECT 'Worked Hour', h.[Date Worked], h.[Project], h.[HourColumn]
FROM Hours h

Should you need to order by specific columns, include union query as a subquery or its own saved query:

SELECT sub.[Project], sub.[Date], sub.[Line Item], sub.[Metric]
FROM (...my union query...) AS sub
ORDER BY sub.[Project], sub.[Date], sub.[Line Item]

Should you want to aggregate records, again include union query as a subquery or its own saved query:

SELECT qry.[Project], qry.[Date], qry.[Line Item], Count(*) As [Obs], SUM(qry.Metric) As Total
FROM [mySavedUnionQuery] qry
GROUP BY qry.[Project], qry.[Date], qry.[Line Item]
ORDER BY qry.[Project], qry.[Date], qry.[Line Item]

---

Full Outer Join

Possibly you need a full outer join of all tables which is not directly supported in MS Access but can be achieved by combining LEFT OUTER JOIN queries with UNION.

Expenses-centric (all rows matching or not to Expenses)

SELECT e.[Expense Date] AS [Date], e.[Project], e.[Expense Amount], e.[Expense Code], 
       p.[Invoiced Amount], p.[Payment Amount],  
       t.[TPC], t.[TPI], t.[Cost Type], 
       h.[Employee], e.[Hours Worked]
FROM ((([Expenses] e
LEFT JOIN [Payments] p 
    ON p.[Project] = e.[Project] AND p.[Transaction Date] = e.[Expense Date])
LEFT JOIN [Third Party Cost] t 
    ON t.[Project] = e.[Project] AND t.[Cost/Income Date] = e.[Expense Date])
LEFT JOIN [Hours] h
    ON h.[Project] = e.[Project] AND h.[Date Worked] = e.[Expense Date])

Payments-centric (all rows matching or not to Payments)

SELECT p.[Transaction Date] AS [Date], p.[Project], e.[Expense Amount], e.[Expense Code], 
       p.[Invoiced Amount], p.[Payment Amount],  
       t.[TPC], t.[TPI], t.[Cost Type], 
       h.[Employee], e.[Hours Worked]
FROM ((([Payments] p
LEFT JOIN [Expenses] e
    ON e.[Project] = p.[Project] AND e.[Expense Date] = p.[Transaction Date])
LEFT JOIN [Third Party Cost] t 
    ON t.[Project] = p.[Project] AND t.[Cost/Income Date] = p.[Transaction Date])
LEFT JOIN [Hours] h
    ON h.[Project] = p.[Project] AND h.[Date Worked] = p.[Transaction Date])

Third-Party Costs-centric (all rows matching or not to Third-Party Costs)

SELECT t.[Cost/Income Date] AS [Date], t.[Project], e.[Expense Amount], e.[Expense Code], 
       p.[Invoiced Amount], p.[Payment Amount],  
       t.[TPC], t.[TPI], t.[Cost Type], 
       h.[Employee], e.[Hours Worked]
FROM ((([Third Party Cost] t 
LEFT JOIN [Expenses] e
    ON e.[Project] = t.[Project] AND e.[Expense Date] = t.[Cost/Income Date])
LEFT JOIN [Payments] p 
    ON p.[Project] = t.[Project] AND p.[Transaction Date] = t.[Cost/Income Date])
LEFT JOIN [Hours] h
    ON h.[Project] = t.[Project] AND h.[Date Worked] = t.[Cost/Income Date])

Hours-centric (all rows matching or not to Hours)

SELECT h.[Date Worked] AS [Date], h.[Project], e.[Expense Amount], e.[Expense Code], 
       p.[Invoiced Amount], p.[Payment Amount],  
       t.[TPC], t.[TPI], t.[Cost Type], 
       h.[Employee], e.[Hours Worked]
FROM ((([Hours] h
LEFT JOIN [Expenses] e
    ON e.[Project] = h.[Project] AND e.[Expense Date] = h.[Date Worked])
LEFT JOIN [Payments] p 
    ON p.[Project] = h.[Project] AND p.[Transaction Date] = h.[Date Worked])
LEFT JOIN [Third Party Cost] t 
    ON t.[Project] = h.[Project] AND t.[Cost/Income Date] = h.[Date Worked])

Union Query (combine all centric queries above)

SELECT * FROM ExpensesQ

UNION     
SELECT * FROM PaymentsQ

UNION     
SELECT * FROM ThirdPartyCostsQ

UNION     
SELECT * FROM HoursQ

Answer 3

It is not a union query you are after.

Create a select query with the Projects table. Then add outer joins from this to each of the tables you mention, joining on field Project ID.

Now you can select the fields you need from the tables, and group by and sum as you wish.