Union Query as Lookup Source

I'm doing a "contact" ID and name combo box lookup to attach an ID to a "work step" record.  The contacts are all in a single contacts table, but the lookup source needs to draw from three separate "feed" queries on the contacts table (to knock down the number of contacts to paw through).   The lookup just grabs the ID, so there's no effect on the source data.  There is possible redundancy amoung the three feed queries, but they can probably be ignored.   I'm looking for a general approach...
>  big union query?
>  or, on "requery" event (prompted in different ways)
    >  build a temp table as recordsource using the feedqueries and an append query, or recordset logic in vba.

I could go either way but was hoping to not have to go through trial an error learning on which would work best or most easily.  May be an easy answer but only so many brain cells left...

Would either work?   Is one more reliable?

Any suggestions on a general approach for this would be appreciated.

Thanks!
LVL 2
codequestAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
A UNION query would work.
Also, UNION eliminates duplicates between queries, UNION ALL includes them.

SELECT id, name
FROM Table1
UNION
SELECT id, name
FROM Table2
UNION
SELECT id, name
FROM Table3
ORDER BY whatever

I think you would need to go the temp table route only if you had any special handling, or ordering that couldn't be accommodated within a single query.

Also, my normal naming convention for queries that feed combo boxes is to prefix it with 'qcbo_'
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Also, just so you know, if you're grabbing columns of the same thing but have different names, then you'll have to alias them so that the query returns a single name

SELECT id, name
FROM Table1
UNION
SELECT id, clent_name as name
FROM Table2
UNION
SELECT id, order_name as name
FROM Table3
ORDER BY whatever
0
 
codequestAuthor Commented:
Grazie!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.