sharpapproach
asked on
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_LaborDistr ibutions.A mount) AS SumOfAmount
SELECT dbo_Maint_TypeCode_1.TypeC odeName AS [Pay Type]
FROM (dbo_Payroll_LaborDistribu tions INNER JOIN dbo_Maint_TypeCode ON dbo_Payroll_LaborDistribut ions.Payro llLineType ID = dbo_Maint_TypeCode.TypeCod eID) INNER JOIN dbo_Maint_TypeCode AS dbo_Maint_TypeCode_1 ON dbo_Payroll_LaborDistribut ions.Payro llEarningD eductionID = dbo_Maint_TypeCode_1.TypeC odeID
WHERE (((dbo_Payroll_LaborDistri butions.Pa yrollLineT ypeID)=457 6 Or (dbo_Payroll_LaborDistribu tions.Payr ollLineTyp eID)=4582) AND ((dbo_Payroll_LaborDistrib utions.Emp loyeeID)=1 000))
GROUP BY dbo_Payroll_LaborDistribut ions.Payro llLineType ID, dbo_Maint_TypeCode_1.TypeC odeName, dbo_Payroll_LaborDistribut ions.Emplo yeePayroll ID
ORDER BY dbo_Maint_TypeCode_1.TypeC odeName
PIVOT dbo_Maint_TypeCode.TypeCod eName;
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
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_LaborDistr
SELECT dbo_Maint_TypeCode_1.TypeC
FROM (dbo_Payroll_LaborDistribu
WHERE (((dbo_Payroll_LaborDistri
GROUP BY dbo_Payroll_LaborDistribut
ORDER BY dbo_Maint_TypeCode_1.TypeC
PIVOT dbo_Maint_TypeCode.TypeCod
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
upload a copy of the db..
ASKER
Perfect thank you!!!!!
glad to help.