Union Select- Newbie

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rebecca - 26 Jul 2006 22:31 GMT

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?

Reply to this Message

John Vinson - 27 Jul 2006 00:10 GMT

>Hi-
>First please be patient-- I'm somewhat new to Access, and am having trouble
[quoted text clipped - 5 lines]
>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]

Reply to this Message

Rebecca - 27 Jul 2006 00:48 GMT

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!

> >Hi-
> >First please be patient-- I'm somewhat new to Access, and am having trouble
[quoted text clipped - 26 lines]
>
> John W. Vinson[MVP]

Reply to this Message

Rebecca - 27 Jul 2006 01:14 GMT

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!

> >Hi-
> >First please be patient-- I'm somewhat new to Access, and am having trouble
[quoted text clipped - 26 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 27 Jul 2006 03:17 GMT

>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]

Reply to this Message

Wolfgang Kais - 27 Jul 2006 00:33 GMT

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-
[quoted text clipped - 6 lines]
> 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.

Signature

Regards,
Wolfgang

Reply to this Message

Rebecca - 27 Jul 2006 00:49 GMT

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

> Hello Rebecca.
>
[quoted text clipped - 16 lines]
> 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.

Reply to this Message