Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Combine table data into one query resuly

Please see the attached file.  When you open the table you will see this data:

User generated image
I need to create a query that takes the information and uses the fields that are not "0" and combine them into a result that looks like this:

CD-2, GH-3, IJ-6, OP-2, ST4

I don't know where to begin.  Any help much appreciated.
Test.accdb
Avatar of irudyk
irudyk
Flag of Canada image
A bit crude, but you could do something like
SELECT Left([Data],Len([Data])-1) AS Result
FROM (SELECT IIf([AB]<>0,"AB-" & [AB] & ",","") & IIf([CD]<>0,"CD-" & [CD] & ",","") & IIf([EF]<>0,"EF-" & [EF] & ",","") & IIf([GH]<>0,"GH-" & [GH] & ",","") & IIf([IJ]<>0,"IJ-" & [IJ] & ",","") & IIf([KL]<>0,"KL-" & [KL] & ",","") & IIf([MN]<>0,"MN-" & [MN] & ",","") & IIf([OP]<>0,"OP-" & [OP] & ",","") & IIf([QR]<>0,"QR-" & [QR] & ",","") & IIf([ST]<>0,"ST-" & [ST] & ",","") AS Data
FROM tblRecords) AS Src

Open in new window

Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

I really don't know what you did.  Where did the Src come from?  I don't get it.
ASKER CERTIFIED SOLUTION
Avatar of irudyk
irudyk
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

Got it.  Thanks.