Access Query

I have the following table

Name | Group1 | Group 2 | Group 3 |
Paul   | Mktg      |  HR
Andy | Sales    | Mktg
Tony | HR        |                | Sales

I want to create a query with the following structure

Groups | Names

which should give me the following list

Groups | Names
Mktg      | Paul
Mktg      | Andy
HR         | Paul
HR         | Tony
Sales     | Andy
Sales     | Tony

Any idea on how to build it? It would be great if you could provide both the SQL and the Query structure in Access
fitalianoAsked:
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:
use a union query

select group1 as Groups, [name] as Names from Tablex
union all
select group2, [name] from Tablex
union all
select group3, [name] from Tablex
0
Patrick MatthewsCommented:
The immediate answer is to do a UNION query, which cannot be done in design view, but must be in SQL view:


SELECT Group1 AS Groups, [Name]
FROM SomeTable
WHERE Group1 Is Not Null
UNION
SELECT Group2 AS Groups, [Name]
FROM SomeTable
WHERE Group2 Is Not Null
UNION
SELECT Group3 AS Groups, [Name]
FROM SomeTable
WHERE Group3 Is Not Null


The *real* answer is, you have got to change your design, as that current table design is very, very suboptimal.
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
Rey Obrero (Capricorn1)Commented:
select group1 as Groups, [name] as Names from Tablex where group1 <> null
union all
select group2, [name] from Tablex where group2 <> null
union all
select group3, [name] from Tablex where group3 <> null
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Patrick MatthewsCommented:
BTW, a better design:

tblPeople
-----------------------------
PersonID (PK)
FName
LName
<others>

tblGroups
-----------------------------
GroupID (PK)
GroupName

tblGroupsPeople
-----------------------------
GroupPeopleID (PK)
PersonID (FK)                    <--- also create unique index on PersonID + GroupID
GroupID (FK)
0
Rey Obrero (Capricorn1)Commented:
patrick,
you have to use UNION ALL
0
Patrick MatthewsCommented:
Rey,

>>you have to use UNION ALL

With respect, that is not true at all.  I just put the sample data from the question into a database, ran my SQL statement, and got back:

Groups      Name
HR      Paul
HR      Tony
Mktg      Andy
Mktg      Paul
Sales      Andy
Sales      Tony

Same data as the Asker said s/he wanted, albeit in not exactly the same order :)
0
TracyVBA DeveloperCommented:
I agree with cap and patrick, the design should be changed.  But in any case, attached is an example of the union query on a table called data.
SELECT  Name as Names, Group1 as Groups
From Data
Where Group1 is not null
Union All
SELECT Name as Names, Group2 as Groups
From Data
Where Group2  is not null
UNION ALL SELECT Name as Names, Group3 as Groups
From Data
Where Group3 is not null
ORDER BY Names, Groups;

Open in new window

db2.mdb
0
fitalianoAuthor Commented:
All of your solutions work great. My Table is indeed much more complex. I basically need to "stack" everything by Groups, the rest of the fields does not need to be renamed (in the example: The field [Name] doesn't need to be renamed to [Names].

Is there a simple way to say SELECT Group1 as Gourps + "everything else" (I guess some sort of select *?).

In other words: I have 100 fields after Group1, 2, 3 I just dont' want to repeat Select as for all of them.

I hope is clear, thanks for your support!
0
Patrick MatthewsCommented:
>>My Table is indeed much more complex.

All the ore reason to reevaluate the design.  Looks to me like the table is highly denormalized, and that will make your life a lot harder than it needs to be.

In any event, if you want to return all columns, you can use *

    SELECT *
    FROM SomeTable

If you want to use some, but not all, columns, you must list them one by one.
0
fitalianoAuthor Commented:
Sorry maybe I wasn't clear. This is my Select statement after all your suggestions:
-----------------------------------------------------------------
SELECT [MG1] as [MG], [MR],  [Employee_EIN], [Full Name], [CI90-Q1-L], [CI90-Q1-H], [CI90-Q1-E]
FROM [q_Rolling_Forecast_Supp_CI90 by Single Role+Emp MG1]  

WHERE [MG1] <> ""

UNION ALL

SELECT [MG2], [MR], [Employee_EIN], [Full Name], [CI90-Q1-L], [CI90-Q1-H], [CI90-Q1-E]
FROM  [q_Rolling_Forecast_Supp_CI90 by Single Role+Emp MG1]

WHERE [MG2] <> ""

UNION ALL SELECT [MG3], [MR], [Employee_EIN], [Full Name], [CI90-Q1-L], [CI90-Q1-H], [CI90-Q1-E]
FROM  [q_Rolling_Forecast_Supp_CI90 by Single Role+Emp MG1]

WHERE [MG3] <> ""
ORDER BY [MG], [MR], [Full Name];
------------------------------------------------------

The only things that changes is MG.... Is there a way not re-write all the other fields? If not, it's not a big deal. You guys saved my day.
0
Patrick MatthewsCommented:
>>Is there a way not re-write all the other fields?

No, there is not.

BTW, are you sure that the MG# columns will have a zero-length string, and never a Null?  I like to take the "belt and suspenders" approach to stuff like that:


SELECT [MG1] as [MG], [MR],  [Employee_EIN], [Full Name], [CI90-Q1-L], [CI90-Q1-H], [CI90-Q1-E]
FROM [q_Rolling_Forecast_Supp_CI90 by Single Role+Emp MG1]  

WHERE Trim(Nz([MG1],"")) <> ""

UNION ALL

SELECT [MG2], [MR], [Employee_EIN], [Full Name], [CI90-Q1-L], [CI90-Q1-H], [CI90-Q1-E]
FROM  [q_Rolling_Forecast_Supp_CI90 by Single Role+Emp MG1]

WHERE Trim(Nz([MG2],"")) <> ""

UNION ALL SELECT [MG3], [MR], [Employee_EIN], [Full Name], [CI90-Q1-L], [CI90-Q1-H], [CI90-Q1-E]
FROM  [q_Rolling_Forecast_Supp_CI90 by Single Role+Emp MG1]

WHERE Trim(Nz([MG3],"")) <> ""
ORDER BY [MG], [MR], [Full Name];



That picks up zero length string, null, and all spaces.
0
Rey Obrero (Capricorn1)Commented:
You don't have to, the union query will take the Field names of the first select query as the Column Names
0
fitalianoAuthor Commented:
I am impressed by the response speed. I love this website!

 matthewspatrick was particularly thorough, thanks!
0
Patrick MatthewsCommented:
Glad to help :)
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.