asked on

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

Hi experts.

I am trying to query a table and have the query list details of all transactions if the [Item] is anything except Payroll and if the [Item] is Payroll list just the Summary total of those.

My table is tblGLData
the field name is Item

Is there a way on one query to do that or do I have to create two queries and then combine them? Attempted two and then combining but it didn't give me the desired result.

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

Just left the office. Will post when I get back

Patrick Matthews

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.

wlwebb

ASKER

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

Open in new window

wlwebb

ASKER

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));

Open in new window

Rey Obrero (Capricorn1)

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));

Open in new window

wlwebb

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

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.

wlwebb

ASKER

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