Putting Row Values into Columns

[joesmithf1]

Hi,

I have been trying to figure this out for a long time. Any tips/helps is much appreciated it.

I have a table with 3 columns(SSN,DeductionType,DeductionAmount). Let say I have 100 employees. There are 3 records per employee, because each have 3 different deductionType(TypeA,TypeB,TypeC).

I want to create a queryTable that will result with 1 record per individual, with TypeA,TypeB,and TypeC as column names and the DeductionAmount as their values. Make sense? Below is an example of what the new table should look like:

SSN--------------TypeA-------TypeB-------TypeC
022-58-5898------ $10.35-----$25------------$14
036-89-5487-------$5----------$45.69---------$47.33
...
..
.

Thank you VERY much!

By the way, if QueryTable is not the method to do this, please let me know how.
Joe

 

[edtab]

You're on the right track. In your query design, create
3 calculated fields, one for each deduction type, e.g.:

TypeA:Iif([DeductionType]="TypeA",[DeductionAmount],"")

Do this for the other two types and you should have these
3 additional columns that you can use for reporting purposes, etc.

Let me know if it works for you.

 

[joesmithf1]

Great! This works! HOWEVER, of course I still have 3 records per individual. To solve this, i am assuming I would have to do a "Group By" SSN. When I do so, I get an error saying:"Data type mismatch in criteria expression."

Here are my query codes:

SELECT Sum(IIf(Right([Dedtype_CD],1)="E",[overded_am],"")) AS [Employee Portion], Sum(IIf(Right([Dedtype_CD],1)="R",[overded_am],"")) AS [Employer Portion], Sum(IIf(Right([Dedtype_CD],1)="A",[overded_am],"")) AS [Admin Portion], DEDPARMS1.EMP_ID
FROM DEDPARMS1
GROUP BY DEDPARMS1.EMP_ID;

What is wrong this time? Sigh.....

Thank you.

Joe

 

[edtab]

Your code:
Sum(IIf(Right([Dedtype_CD],1)="E",[overded_am],""))

I think the problem lies in the "" - right after [overded_am].
You can not perform arithmetic on "". I suggest you replace
them with zero and see what happens.

Data type mismatch indicates a certain type of field is expected
but another has been encountered. This would be the case when
your 3 columns each contain "" and numbers together. Math would
not work here.

 

[Pat Hartman]

You can use a cross tab query which will be more flexible.