How to create spending vs. budgeted query and/or report (maybe an full outer join question?)

[BFauska]

Full disclosure, I can offer the DB for people to take a look at but there are some terrible messes (in the worlds of naming conventions and table design best practices) in there that I simply don't have time to address at this point.

I'm working on a payroll database that started as a way to enter timesheet data and then evolved to include a budget section to compare and assess our spending.

I can use my "HourDetails" table as the source for a query that tells me what I've spent in any given week in each of several accounts and departments and I can use my "TblScenicAndCrewFlow" table to see how much I've budgeted to each of those many accounts and departments in any given week.

I want to create a query (ultimately a report but I think I need to start with a Query) that will show these numbers side by side. My challenge seems to be that I'm seeking a Full Outer Join and can't quite wrap my head around the design or implementation of it.

In order to get two groups of data that have matching fields I've made two queries with the following parts:
QryFlow:
-EndDate (these are pay period end dates)
-Show (number identifiers to define which show the work was on)
-AccountNumber (identifies the department and worker type)
-BudgetedPayroll (amount of money expected to spend that pay period)

and

QrySpent:
-EndDate (these are pay period end dates)
-Show (number identifiers to define which show the work was on)
-AccountNumber (identifies the department and worker type)
-SpentPayroll (amount of money spent that pay period)

For any given date there may or may not be budgets assigned to a show or account within that show and likewise there may or may not have been money spent for that show and/or account.
Here's a sample of the two queries that I'd like to create this new query (or report) from. There are additional fields that I didn't describe above or include below but once I know how to solve this as described above I can add the treatment to the additional fields.

EndDate	Show	AccountNumber	BudgetedPayroll
8/13/2017	000	6104-42-100	$4,577.35
8/13/2017	000	6104-44-100	$4,680.91
8/13/2017	101	6104-42-100	$6,866.03
8/27/2017	100	6203-49-300	$13,110.70
8/27/2017	100	6207-49-300	$4,142.40
8/27/2017	101	6104-42-100	$18,309.41
8/27/2017	101	6104-44-100	$3,510.68
8/27/2017	101	6108-42-100	$4,142.40
8/27/2017	760	6104-42-100	$9,154.70
8/27/2017	760	6104-44-100	$3,510.68
9/10/2017	101	6104-42-100	$14,876.39
9/10/2017	101	6104-44-100	$8,191.60
9/10/2017	101	6108-42-100	$2,071.20
9/10/2017	101	6123-44-100	$1,242.72
9/10/2017	760	6104-42-100	$14,876.39
9/10/2017	760	6104-44-100	$5,851.14
9/10/2017	760	6123-42-100	$4,970.88
9/10/2017	760	6203-49-300	$26,221.39
9/10/2017	760	6207-49-300	$16,569.60

EndDate	Show	AccountNumber	SpentPayroll
8/13/2017	101	6104-42-100	$181.23
8/13/2017	760	6104-42-100	$1,838.19
8/13/2017	000	6104-42-100	$4,142.40
8/13/2017	760	6104-44-100	$2,485.44
8/13/2017	100	6203-49-300	$414.24
8/27/2017	760	6104-42-100	$5,229.78
8/27/2017	000	6104-42-100	$854.37
8/27/2017	101	6104-42-100	$2,770.23
8/27/2017	000	6104-44-100	$776.70
8/27/2017	101	6104-44-100	$1,139.16
8/27/2017	760	6104-44-100	$1,294.50
8/27/2017	100	6203-49-300	$5,747.58
8/27/2017	100	6207-49-300	$1,957.62
9/10/2017	101	6104-42-100	$4,737.87
9/10/2017	760	6104-42-100	$3,857.61
9/10/2017	000	6104-42-100	$2,589.00
9/10/2017	000	6104-44-100	$2,485.44
9/10/2017	999	6104-44-100	$1,190.94
9/10/2017	760	6104-44-100	$1,372.17
9/10/2017	101	6104-44-100	$1,009.71
9/10/2017	760	6104-49-300	$1,242.72
9/10/2017	760	6123-49-300	$996.77
9/10/2017	100	6203-49-300	$1,139.16
9/10/2017	101	6203-49-300	$621.36
9/10/2017	760	6203-49-300	$14,509.89
9/10/2017	101	6207-49-300	$1,295.13
9/10/2017	760	6207-49-300	$4,135.96
9/10/2017	760	6223-49-300	$7,067.97
9/10/2017	100	6223-49-300	$77.67
9/10/2017	101	6227-49-300	$76.53
9/10/2017	760	6227-49-300	$721.59

Here's a hacked together version of what I imagine the results looking like:

EndDate	Show	AccountNumber	BudgetedPayroll	SpentPayroll
8/13/2017	000	6104-42-100	$4,577.35	$4,142.40
8/13/2017	000	6104-44-100	$4,680.91
8/13/2017	101	6104-42-100	$6,866.03	$181.23
8/13/2017	760	6104-42-100		$1,838.19
8/13/2017	760	6104-44-100		$2,485.44
8/13/2017	100	6203-49-300		$414.24
8/27/2017	100	6203-49-300	$13,110.70	$5,747.58
8/27/2017	100	6207-49-300	$4,142.40	$1,957.62
8/27/2017	101	6104-42-100	$18,309.41	$2,770.23
8/27/2017	101	6104-44-100	$3,510.68	$1,139.16
8/27/2017	101	6108-42-100	$4,142.40
8/27/2017	760	6104-42-100	$9,154.70	$5,229.78
8/27/2017	000	6104-42-100		$854.37
8/27/2017	000	6104-44-100		$776.70
8/27/2017	760	6104-44-100	$3,510.68	$1,294.50
9/10/2017	000	6104-42-100		$2,589.00
9/10/2017	000	6104-44-100		$2,485.44
9/10/2017	100	6203-49-300		$1,139.16
9/10/2017	100	6223-49-300		$77.67
9/10/2017	101	6227-49-300		$76.53
9/10/2017	101	6207-49-300		$1,295.13
9/10/2017	101	6203-49-300		$621.36
9/10/2017	101	6104-42-100	$14,876.39	$4,737.87
9/10/2017	101	6104-44-100	$8,191.60	$1,009.71
9/10/2017	101	6108-42-100	$2,071.20
9/10/2017	101	6123-44-100	$1,242.72
9/10/2017	760	6104-42-100	$14,876.39	$3,857.61
9/10/2017	760	6104-44-100	$5,851.14	$1,372.17
9/10/2017	760	6104-49-300		$1,242.72
9/10/2017	760	6123-42-100	$4,970.88
9/10/2017	760	6123-49-300		$996.77
9/10/2017	760	6203-49-300	$26,221.39	$14,509.89
9/10/2017	760	6207-49-300	$16,569.60	$4,135.96
9/10/2017	760	6223-49-300		$7,067.97
9/10/2017	760	6227-49-300		$721.59
9/10/2017	999	6104-44-100		$1,190.94

I hope this all makes sense and I swear I've looked for answers. I think what's stumping me is that all the Full Outer Joins info I've found has used examples where the two data sources only have 1 column in common and 1 that may contain null values, my example has up to 3 columns of matching data and there may be instances where all 3 of those may be null in one source query or the other.

[aytee111]

Begin with a query to get a full list of all date/show/acct combinations:
SELECT date, show, acct FROM table1
UNION SELECT date, show, acct FROM table2;

Once you have this list, you can left join to the two queries you have created.

[BFauska]

Begin with a query to get a full list of all date/show/acct combinations:
SELECT date, show, acct FROM table1
UNION SELECT date, show, acct FROM table2;

Once you have this list, you can left join to the two queries you have created.

Thank you, such a simple description of the solution and it worked. None of the long posts and other forum responses I could find on the ways to fake an outer join made sense to me. That worked.