?
Solved

Union Query as Lookup Source

Posted on 2012-08-18
3
Medium Priority
?
468 Views
Last Modified: 2012-08-18
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!
0
Comment
Question by:codequest
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38308464
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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 2000 total points
ID: 38308466
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
 
LVL 2

Author Closing Comment

by:codequest
ID: 38308647
Grazie!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question