fitaliano
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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
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 :)
>>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;
db2.mdb
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!
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.
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.
ASKER
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_C I90 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_C I90 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_C I90 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.
--------------------------
SELECT [MG1] as [MG], [MR], [Employee_EIN], [Full Name], [CI90-Q1-L], [CI90-Q1-H], [CI90-Q1-E]
FROM [q_Rolling_Forecast_Supp_C
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_C
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_C
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_C I90 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_C I90 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_C I90 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.
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_C
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_C
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_C
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
ASKER
I am impressed by the response speed. I love this website!
matthewspatrick was particularly thorough, thanks!
matthewspatrick was particularly thorough, thanks!
Glad to help :)
select group1 as Groups, [name] as Names from Tablex
union all
select group2, [name] from Tablex
union all
select group3, [name] from Tablex