[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Crosstab Query

Posted on 2013-11-13
4
Medium Priority
?
289 Views
Last Modified: 2013-11-13
I cannot get this query to do what I want.

Here is the SQL statement.

Pic #1 is the Query Design.

Pic #2 is the Query Run.

I need it where the Deduction and Deduction (ER) are on the same line for the same Pay Type.

TRANSFORM Sum(dbo_Payroll_LaborDistributions.Amount) AS SumOfAmount
SELECT dbo_Maint_TypeCode_1.TypeCodeName AS [Pay Type]
FROM (dbo_Payroll_LaborDistributions INNER JOIN dbo_Maint_TypeCode ON dbo_Payroll_LaborDistributions.PayrollLineTypeID = dbo_Maint_TypeCode.TypeCodeID) INNER JOIN dbo_Maint_TypeCode AS dbo_Maint_TypeCode_1 ON dbo_Payroll_LaborDistributions.PayrollEarningDeductionID = dbo_Maint_TypeCode_1.TypeCodeID
WHERE (((dbo_Payroll_LaborDistributions.PayrollLineTypeID)=4576 Or (dbo_Payroll_LaborDistributions.PayrollLineTypeID)=4582) AND ((dbo_Payroll_LaborDistributions.EmployeeID)=1000))
GROUP BY dbo_Payroll_LaborDistributions.PayrollLineTypeID, dbo_Maint_TypeCode_1.TypeCodeName, dbo_Payroll_LaborDistributions.EmployeePayrollID
ORDER BY dbo_Maint_TypeCode_1.TypeCodeName
PIVOT dbo_Maint_TypeCode.TypeCodeName;


for example:

401K            $161.52        $94.22
Dental         $31.19          $12.88
Health         $163.07        $100.82
Parking       $6.92
Vision          $3.98            $2.83
Pic1.gif
pic2.gif
0
Comment
Question by:sharpapproach
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39645494
Problem is that you are grouping by [PayrollLineTypeID] and [EmployeePayrollID] but have not included those as Row Headers, that is why the lines are split.  If you remove those two columns, you should get what you are looking for.  Either that, or change the Crosstab row from blank to RowHeading for those items.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39645501
upload a copy of the db..
0
 

Author Closing Comment

by:sharpapproach
ID: 39646076
Perfect thank you!!!!!
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39646129
glad to help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question