[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Crosstab Query

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
sharpapproach
Asked:
sharpapproach
  • 2
1 Solution
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
upload a copy of the db..
0
 
sharpapproachAuthor Commented:
Perfect thank you!!!!!
0
 
Dale FyeCommented:
glad to help.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now