Access Report - combining multiple columns into a \"dictionary\" key I have a da
ID: 3560643 • Letter: A
Question
Access Report - combining multiple columns into a "dictionary" key
I have a database containing information in multiple columns that I would like to combine for each Member ID. My table looks something like:
AAA111
I need to have a list of all of the Codes next to their Descriptions for each MemberID. The code needs to be listed only once, no matter how many times it occurs for the MemberID or if it occurs in more than one ProductNumber. I basically have a bunch of info at the top of my report, and I need this at the bottom as a key to explain what each code means.
So something Like:
Everything I've tried so far leaves blank spaces for each hidden duplicate. And if Code1 & Code2 are empty, Code3 is listed with two blank lines above it. And then I've had problems with extra blank spaces when there are multiple ProductNumbers for one MemberID. Agh I'm so frustrated!
I'm not very experienced with VBA, but I understand the basics (I think). Enough to copy and paste something into a module if someone could help me out :) Thanks!!
MemberID ProductNumber Code1 Code1Description Code2 Code2Description Code3 Code3Description AAA111 14-001 GW GreatWestAAA111
14-001 10 Discount AAA111 14-002 Ex Express S1 SMA1 10 Discount AAA111 14-002 GW GreatWestExplanation / Answer
%^Try a normalizing union query like
SELECT Code1 Code, Code1Description CodeDescription
FROM [Your Unnamed Table]
WHERE Code1 Is Not Null
UNION
SELECT Code2, Code2Description
FROM [Your Unnamed Table]
WHERE Code2 Is Not Null
UNION
SELECT Code3, Code3Description
FROM [Your Unnamed Table]
WHERE Code3 Is Not Null