[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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
0
fitaliano
Asked:
fitaliano
  • 6
  • 4
  • 3
  • +1
2 Solutions
 
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now