Access - Query that will give details of all [Items] except one and will give Summary total for that excepted [Item]

Just left the office. Will post when I get back

Some variant of a union query could work.

It would be best if you could post a few rows of input, and then post the expected output given that sample.

Cap 1,

Below are the 2 queries I created.

PR Totals only query
SELECT DISTINCTROW qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt], Sum(qryQBGLDataExpenseDetailAll.Amount) AS SumOfAmount
FROM qryQBGLDataExpenseDetailAll
WHERE (((qryQBGLDataExpenseDetailAll.GLDate) Between [Forms]![frmJobSummary]![txtJobSummaryFormBeginningDate] And [Forms]![frmJobSummary]![txtJobSummaryFormEndingDate]))
GROUP BY qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt]
HAVING (((qryQBGLDataExpenseDetailAll.TransactType)="Paycheck") AND ((qryQBGLDataExpenseDetailAll.GLDataName)=[Forms]![frmJobSummary]![lstJobCostSummaryJobSelector]));

******************************
All other Expenses query
SELECT DISTINCTROW qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.[Source Name], qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.[Trans #], qryQBGLDataExpenseDetailAll.InvNum, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt], Sum(qryQBGLDataExpenseDetailAll.Amount) AS SumOfAmount
FROM qryQBGLDataExpenseDetailAll
GROUP BY qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.[Source Name], qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.[Trans #], qryQBGLDataExpenseDetailAll.InvNum, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt]
HAVING (((qryQBGLDataExpenseDetailAll.TransactType)<>"Paycheck") AND ((qryQBGLDataExpenseDetailAll.GLDataName)=[Forms]![frmJobSummary]![lstJobCostSummaryJobSelector]) AND ((qryQBGLDataExpenseDetailAll.GLDate) Between [Forms]![frmJobSummary]![txtJobSummaryFormBeginningDate] And [Forms]![frmJobSummary]![txtJobSummaryFormEndingDate]));


***************************
Query where I tried to get all info into one query
SELECT qryQBGLData_NonPayrollDetailByCustomer.TransactType AS qryQBGLData_NonPayrollDetailByCustomer_TransactType, qryQBGLData_NonPayrollDetailByCustomer.GLDataName AS qryQBGLData_NonPayrollDetailByCustomer_GLDataName, qryQBGLData_NonPayrollDetailByCustomer.[Source Name], qryQBGLData_NonPayrollDetailByCustomer.GLDate, qryQBGLData_NonPayrollDetailByCustomer.[Trans #], qryQBGLData_NonPayrollDetailByCustomer.InvNum, qryQBGLData_NonPayrollDetailByCustomer.Item AS qryQBGLData_NonPayrollDetailByCustomer_Item, qryQBGLData_NonPayrollDetailByCustomer.Job AS qryQBGLData_NonPayrollDetailByCustomer_Job, qryQBGLData_NonPayrollDetailByCustomer.JobItem AS qryQBGLData_NonPayrollDetailByCustomer_JobItem, qryQBGLData_NonPayrollDetailByCustomer.[FS Stmt] AS [qryQBGLData_NonPayrollDetailByCustomer_FS Stmt], qryQBGLData_NonPayrollDetailByCustomer.SumOfAmount AS qryQBGLData_NonPayrollDetailByCustomer_SumOfAmount, qryQBGLData_PayrollSummaryByCustomer.TransactType AS qryQBGLData_PayrollSummaryByCustomer_TransactType, qryQBGLData_PayrollSummaryByCustomer.GLDataName AS qryQBGLData_PayrollSummaryByCustomer_GLDataName, qryQBGLData_PayrollSummaryByCustomer.Item AS qryQBGLData_PayrollSummaryByCustomer_Item, qryQBGLData_PayrollSummaryByCustomer.Job AS qryQBGLData_PayrollSummaryByCustomer_Job, qryQBGLData_PayrollSummaryByCustomer.JobItem AS qryQBGLData_PayrollSummaryByCustomer_JobItem, qryQBGLData_PayrollSummaryByCustomer.[FS Stmt] AS [qryQBGLData_PayrollSummaryByCustomer_FS Stmt], qryQBGLData_PayrollSummaryByCustomer.SumOfAmount AS qryQBGLData_PayrollSummaryByCustomer_SumOfAmount
FROM (tblQBCustomer INNER JOIN qryQBGLData_PayrollSummaryByCustomer ON tblQBCustomer.[NAME] = qryQBGLData_PayrollSummaryByCustomer.[GLDataName]) INNER JOIN qryQBGLData_NonPayrollDetailByCustomer ON tblQBCustomer.[NAME] = qryQBGLData_NonPayrollDetailByCustomer.[GLDataName];

Select allOpen in new window

Capricorn1
I got the Union Query to work.  Somewhat.

Below is that Query.  

However, the Payroll portion is showing details and I only want it to show those in summary.

The fields that are causing the problem and show details for Payroll are:
qryQBGLDataExpenseDetailAll.[Source Name],
qryQBGLDataExpenseDetailAll.[Trans #],
qryQBGLDataExpenseDetailAll.InvNum

Those fields cause the details line by line to show instead of a summary.  Once I get this ironed out I will post a second question.  That question will be how to add a selection group where the user can show the summary of this payroll info by either paycheck a)date; b)week; c)month; d)year or e)Total for Job to date.

SELECT DISTINCTROW qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.[Source Name], qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.[Trans #], qryQBGLDataExpenseDetailAll.InvNum, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt], Sum(qryQBGLDataExpenseDetailAll.Amount) AS SumOfAmount
FROM qryQBGLDataExpenseDetailAll
GROUP BY qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.[Source Name], qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.[Trans #], qryQBGLDataExpenseDetailAll.InvNum, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt]
HAVING (((qryQBGLDataExpenseDetailAll.TransactType)="Paycheck") And ((qryQBGLDataExpenseDetailAll.GLDataName)=Forms!frmJobSummary!lstJobCostSummaryJobSelector) And ((qryQBGLDataExpenseDetailAll.GLDate) Between Forms!frmJobSummary!txtJobSummaryFormBeginningDate And Forms!frmJobSummary!txtJobSummaryFormEndingDate));
UNION ALL SELECT DISTINCTROW qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.[Source Name], qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.[Trans #], qryQBGLDataExpenseDetailAll.InvNum, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt], Sum(qryQBGLDataExpenseDetailAll.Amount) AS SumOfAmount
FROM qryQBGLDataExpenseDetailAll
GROUP BY qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.[Source Name], qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.[Trans #], qryQBGLDataExpenseDetailAll.InvNum, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt]
HAVING (((qryQBGLDataExpenseDetailAll.TransactType)<>"Paycheck") And ((qryQBGLDataExpenseDetailAll.GLDataName)=Forms!frmJobSummary!lstJobCostSummaryJobSelector) And ((qryQBGLDataExpenseDetailAll.GLDate) Between Forms!frmJobSummary!txtJobSummaryFormBeginningDate And Forms!frmJobSummary!txtJobSummaryFormEndingDate));

Select allOpen in new window

try this

SELECT DISTINCTROW qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.[Source Name], qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.[Trans #], qryQBGLDataExpenseDetailAll.InvNum, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt], Sum(qryQBGLDataExpenseDetailAll.Amount) AS SumOfAmount
FROM qryQBGLDataExpenseDetailAll
GROUP BY qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.[Source Name], qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.[Trans #], qryQBGLDataExpenseDetailAll.InvNum, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt]
HAVING (((qryQBGLDataExpenseDetailAll.TransactType)="Paycheck") And ((qryQBGLDataExpenseDetailAll.GLDataName)=Forms!frmJobSummary!lstJobCostSummaryJobSelector) And ((qryQBGLDataExpenseDetailAll.GLDate) Between Forms!frmJobSummary!txtJobSummaryFormBeginningDate And Forms!frmJobSummary!txtJobSummaryFormEndingDate));
UNION ALL SELECT DISTINCTROW qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, Null, qryQBGLDataExpenseDetailAll.GLDate, Null, Null, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt], Sum(qryQBGLDataExpenseDetailAll.Amount) AS SumOfAmount
FROM qryQBGLDataExpenseDetailAll
GROUP BY qryQBGLDataExpenseDetailAll.TransactType, qryQBGLDataExpenseDetailAll.GLDataName, qryQBGLDataExpenseDetailAll.GLDate, qryQBGLDataExpenseDetailAll.Item, qryQBGLDataExpenseDetailAll.Job, qryQBGLDataExpenseDetailAll.JobItem, qryQBGLDataExpenseDetailAll.[FS Stmt]
HAVING (((qryQBGLDataExpenseDetailAll.TransactType)<>"Paycheck") And ((qryQBGLDataExpenseDetailAll.GLDataName)=Forms!frmJobSummary!lstJobCostSummaryJobSelector) And ((qryQBGLDataExpenseDetailAll.GLDate) Between Forms!frmJobSummary!txtJobSummaryFormBeginningDate And Forms!frmJobSummary!txtJobSummaryFormEndingDate));

Select allOpen in new window

Cap1
It appears to be backwards.  It's summarizing everything except payroll.  Payroll is still in detail

I switched the =Paycheck in the first SELECT DISTINCTROW to <> 
and the <>Paycheck in the second SELECT DISTINCTROW to =

That did the trick.

I will now award the points for this question and start a new question for the Summary of Payroll by a date or Job to date selector and reference back to this thread.

Thank you so much Cap1 for your expertise.  It is invaluable.

As I indicated, I was going to post a followup question regarding adding a selection radio button for the user to decide how the summary payroll was to be shown.  Here is the link to that followup question.

https://www.experts-exchange.com/questions/27039556/Access-Union-query-that-needs-a-Selector-for-user-to-decide-if-Summary-Payroll-totals-shown-by-various-criteria.html