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

wlwebb
wlwebb used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
you have to a Union query.

here is a sample

select f1,f2, f3 from table where item <> "Payroll"
union all
select f1,f2, f3 from table where item = "Payroll"


post the sql of the queries that you  created

Author

Commented:
Just left the office. Will post when I get back
Top Expert 2010

Commented:
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.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
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

Author

Commented:
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

Top Expert 2016

Commented:
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

Author

Commented:
Cap1
It appears to be backwards.  It's summarizing everything except payroll.  Payroll is still in detail
Top Expert 2016
Commented:
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

Author

Commented:
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.

Author

Commented:
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.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27039556.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial