I'm relatively new to this, so this may be convoluted, but I'm hoping someone can give me a hand on this. Trying to relate/join/union (not sure the best method here) two tables with select conditions.

Here is the layout, as I've got a few tables with sales-related data. 

"Sales Ledger" is actual transactions (where there are multiple lines per TX, some that show individual items, some that summarize everything, and some that just have payment info). Importantly, each sale has a unique "receipt number" and a datetime.

"Item Details" is like a product catalog with item cost, supplier, etc. (related to Sales Ledger via item SKU field)

"Payroll" is a table of pay dates, amounts, employee, employee status (hourly or salaried), and a column of "pay periods" (basically just positive numbers from 1 to 26). There is no unique ID column in this file and the same employee may have multiple amounts listed in different rows for the same day.

There is another table called "Pay Periods" which lists the "Pay Period" number (basically an ID) in a column next to columns for "Start Date," "End Date," and "Pay Date". After failing to get the larger picture to work though I just manually copied over the pay period numbers to the "Payroll" table to try and make it easier. But the SQL below uses this table, as I have a feeling it is needed long-term, as data is added. 

I'm trying to create a chart of total sales vs total payroll for a given month (or pay period, if that is the only way to make it work)...with the ability to user filter payroll amounts by employee type (so basically hide, or only show, salaried payroll amounts in the chart vs hourly).

1) The "Sales Ledger" select statement has a restriction WHERE "Sales Ledger"."Line Type" = 'Sale Line' so it doesn't double count sale amounts. Just an internal thing because the table contains lots of additional sale info.

2) I've built a query that works and the data returned in the simple table looks correct. However, no matter how I try, I can't get this data to Join/Union/Lookup/otherwise relate to payroll amount sums for the given Pay Period. That query is:

That seems to return a correct list of individual "Receipt Numbers" (i.e. sales), their datetime, total amount, and the correct pay period each sale occurred in. So I can chart total Sales by Pay Period.

But I can't tie this data into payroll data. So I need to sum all "Sales Ledger"."Total" in a given "Pay Periods"."Pay Period", then sum all "Payroll"."Amount" within the same "Pay Periods"."Pay Period".

I'm guessing there is some sort of UNION or JOIN statement needed in the above query?

Sorry for the long post, but any help is appreciated!