access query create two new fields from one field

Consider the following Query and returned data.

SELECT lkp_VolSubFields.FldName AS [Section], lkp_VolSubFields.ImportFile AS [File], lkp_VolSubFields.PlanSubTypeCD as [Type]
FROM lkp_VolSubFields
WHERE  (((lkp_VolSubFields.ActiveInd)=True))
ORDER BY lkp_VolSubFields.FldName,lkp_VolSubFields.ImportFile;

Returns Data

Section                                                 File                                   Type

Roth Transfers                                     Other Provisions                K
Roth Transfers                                     Other Provisions                P
Years of Service                                  Other Provisions                K
Age of Service                                     Eligibility                             K
Age of Service                                     Eligibility                             P

I want the report to look like this:

Section                                                 File                                   401K       PS

Roth Transfers                                     Other Provisions                Y            Y
Years of Service                                  Other Provisions                Y
Age of Service                                     Eligibility                             Y            Y


ImportFile and FldName can be duplicate (but no more than 2 records).

I know I need to do the aggregate functions on some fields but I'm not sure how to create the temporary (variable) fields based on the values of "K" or "P" from the PlanSubTypeCd [Plan]. The values do not have to be "Y" or "N", they can be boolean.

Can this be done in an Access query?

Thanks.
Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this query, copy and paste

SELECT DISTINCT lkp_VolSubFields.FldName AS [Section], lkp_VolSubFields.ImportFile AS [File], lkp_VolSubFields.PlanSubTypeCD as [Type], iif([PlanSubTypeCD]="K","Y","") As [401K], iif([PlanSubTypeCD]="P","Y","") As [PS]
FROM lkp_VolSubFields
WHERE  (((lkp_VolSubFields.ActiveInd)=True))
ORDER BY lkp_VolSubFields.FldName,lkp_VolSubFields.ImportFile;
0
Chuck LoweAuthor Commented:
@Rey.

Thanks! That works great.
 Is there an easy way to not display the PlanSubTypeCD?

This may be confusing and redundant to users.

Thanks.
0
Rey Obrero (Capricorn1)Commented:
in the query design, just uncheck the Show checkbox for the column [PlanSubTypeCD]
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
SELECT DISTINCT lkp_VolSubFields.FldName AS [Section], lkp_VolSubFields.ImportFile AS [File], iif([PlanSubTypeCD]="K","Y","") As [401K], iif([PlanSubTypeCD]="P","Y","") As [PS]
 FROM lkp_VolSubFields
 WHERE  (((lkp_VolSubFields.ActiveInd)=True))
 ORDER BY lkp_VolSubFields.FldName,lkp_VolSubFields.ImportFile
0
Chuck LoweAuthor Commented:
Duh!
 Sorry brain fart.
Thanks again!
0
Chuck LoweAuthor Commented:
I spoke to soon. The 401k and PS are displaying on seperate fields. I tried the following with MAX and Group By but I still can not get it to work.

The output I was looking for was like this (as described in the original question)

Section                                                 File                                   401K       PS

Roth Transfers                                     Other Provisions               Y            Y
Years of Service                                  Other Provisions               Y
Age of Service                                     Eligibility                            Y            Y

Some Sections and File(s) will have both 401K and PS.
0
Rey Obrero (Capricorn1)Commented:
you can not do that with a straight query.

first create a query with this

SELECT lkp_VolSubFields.FldName AS [Section], lkp_VolSubFields.ImportFile AS File, IIf([PlanSubTypeCD]="K","401K","PS") AS Type
FROM lkp_VolSubFields;

then save as Q

then copy and paste this in a new SQL view of a query

TRANSFORM Count(Q.Type) AS CountOfType
SELECT Q.Section, Q.File
FROM Q
GROUP BY Q.Section, Q.File
PIVOT Q.Type;

btw, the "Y" will be replaced by 1
0
Rey Obrero (Capricorn1)Commented:
if you want a single query, use this, copy and paste


Transform Count(Q.[Type]) As CountOfType
SELECT Q.Section, Q.File
From
(
SELECT lkp_VolSubFields.FldName AS [Section], lkp_VolSubFields.ImportFile AS File, IIf([PlanSubTypeCD]="K","401K","PS") AS Type
FROM lkp_VolSubFields
) As Q
GROUP BY Q.Section, Q.File
PIVOT Q.Type;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.