Solved

Crosstab Query

Posted on 2013-11-13
4
276 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
  • 2
4 Comments
 
LVL 47

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 119

Expert Comment

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

Author Closing Comment

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

Expert Comment

by:Dale Fye (Access MVP)
ID: 39646129
glad to help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

939 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now