Union Select- Newbie

[Guest]

Hi-
First please be patient-- I'm somewhat new to Access, and am having trouble
with a query. I have two tables (1. Certifications (Field- Certifications);
2. Skills (Field-Skills)). I currently have a form which allows the user to
look up certifications by pulling the list of certifications from the table.
Now I need to incorporate the skills table/field so that it allows users to
search both certifications and skills with the search button. When I add the
skills table to my querie it is not working. I think I may need a Union
Select query, but I am unsure. Is this doable? Can anyone help this newbie?

 

[John Vinson]

Hi-
First please be patient-- I'm somewhat new to Access, and am having trouble
with a query. I have two tables (1. Certifications (Field- Certifications);
2. Skills (Field-Skills)). I currently have a form which allows the user to
look up certifications by pulling the list of certifications from the table.
Now I need to incorporate the skills table/field so that it allows users to
search both certifications and skills with the search button. When I add the
skills table to my querie it is not working. I think I may need a Union
Select query, but I am unsure. Is this doable? Can anyone help this newbie?

Do these tables have only one field each? That's a bit unusual. HOw
are the tables used? What do you want to do with the results of the
search?

You can create a UNION query by creating a new query, not selecting
any table. Use View... to go into SQL view (the query will say
SELECT.

Edit it to

SELECT Certifications FROM Certifications
UNION ALL
SELECT Skills FROM Skills;

If there are 32 rows in certifications and 60 in Skills, you'll get a
query with 92 rows, all mixed up, with all of the skills and all of
the certifications. If you remove the ALL, you'll see (probably) fewer
rows, since any duplicate values will be removed.

John W. Vinson[MVP]

 

[Wolfgang Kais]

Hello Rebecca.

First please be patient-- I'm somewhat new to Access, and am having
trouble with a query. I have two tables (1. Certifications (Field-
Certifications); 2. Skills (Field-Skills)). I currently have a form
which allows the user to look up certifications by pulling the list
of certifications from the table.
Now I need to incorporate the skills table/field so that it allows
users to search both certifications and skills with the search
button. When I add the skills table to my querie it is not working.
I think I may need a Union Select query, but I am unsure.
Is this doable? Can anyone help this newbie?

Sure, it is doable.
Select Certifications From Certifications
Union
Select Skills From Skills

Maybe you also could have 2 separate sunforms in your form. Or maybe
you want to create 2 different forms opened when clicking the button.

 

[Guest]

Oops... SORRY... I meant I have two queries. The queries I utilized are for
a category drop down menu. Basically the search is for users to find staff
with the required certifications or needed skill sets so they can dispatch
them out to sites more quickly. Thanks for your help!

 

[Guest]

Thanks for your help! I'll give it a shot.

Hello Rebecca.



Sure, it is doable.
Select Certifications From Certifications
Union
Select Skills From Skills

Maybe you also could have 2 separate sunforms in your form. Or maybe
you want to create 2 different forms opened when clicking the button.

 

[Guest]

Hi again..

Remember when I said please be patient.... there was a reason for that....
Sorry!

I mispoke in an email I just sent. Anyway... I have two tables, and there
is several fields in each.... I only listed the pertinent fields to my query.
The query currently contains one field and I wanted to add another field
from another table. The query is used for a category drop down form I have
users search certifications and ultimately skill sets. Hopefully this is not
too confusing. Thanks for your help!

 

[John Vinson]

I mispoke in an email I just sent. Anyway... I have two tables, and there
is several fields in each.... I only listed the pertinent fields to my query.
The query currently contains one field and I wanted to add another field
from another table. The query is used for a category drop down form I have
users search certifications and ultimately skill sets. Hopefully this is not
too confusing. Thanks for your help!

What do you want the combo box to contain? One column, or two? (e.g.
do you need to return the ID of the person, or just the text of the
Skill)? You say "search certifications and ultimately skill sets".
This sounds like two consecutive searches, not just one.

Please explain a bit more about your table structure. Posting the SQL
view of the query might help.

John W. Vinson[MVP]