Solved

Crosstab Query

Posted on 2013-11-13
4
286 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 (Access MVP) earned 500 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 (Access MVP)
ID: 39646129
glad to help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 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