asked on

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.

Rey Obrero (Capricorn1)

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;

Chuck Lowe

ASKER

@Rey.

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

This may be confusing and redundant to users.

Thanks.

Rey Obrero (Capricorn1)

in the query design, just uncheck the Show checkbox for the column [PlanSubTypeCD]

Rey Obrero (Capricorn1)

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

Chuck Lowe

ASKER

Duh!
Sorry brain fart.
Thanks again!

Chuck Lowe

ASKER

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.

Rey Obrero (Capricorn1)

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chuck Lowe

ASKER

Thanks