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.
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]
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