Link to home
Start Free TrialLog in
Avatar of fitaliano
fitalianoFlag for United States of America

asked on

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America 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
SOLUTION
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
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)
patrick,
you have to use UNION ALL
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 :)
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
Avatar of fitaliano

ASKER

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!
>>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.
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.
>>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.
You don't have to, the union query will take the Field names of the first select query as the Column Names
I am impressed by the response speed. I love this website!

 matthewspatrick was particularly thorough, thanks!
Glad to help :)