Solved

How can I get only distinct results for multiple unions that are built programatically?

Posted on 2009-05-13
10
282 Views
Last Modified: 2012-05-06
VBA, Access 2003

I have six queries that may potentially be built programatically into one query where they are unioned together.  The end result shows up to the user in a list box.  I am having problems with duplicate items showing up.  This is occuring because at times two or more of the queries are selecting the same rows.  Usually I expect a union of two tables only to include distinct results but that does not seem to be the case when there are more than two.

Anyone have any ideas on how to solve this?  I'm thinkig putting the results into a temporary table and then doing a SELECT with DISTINCT as the row source for my list box.  That sounds like it could be slow though and this application updates the searches on every key press so I don't want to add anything more than I have to.

Thanks,

Todd
0
Comment
Question by:Todd_Anderson
  • 5
  • 4
10 Comments
 
LVL 11

Accepted Solution

by:
Quetzal earned 250 total points
ID: 24380687
If you have a union query like SELECT ColA FROM tblA UNION SELECT ColA FROM tblB, then you can do SELECT DISTINCT X.ColA FROM (SELECT ColA FROM tblA UNION SELECT ColA FROM tblB) AS X
0
 
LVL 25

Expert Comment

by:reb73
ID: 24380705
Are you using UNION ALL by any chance? UNION ALL usually returns duplicates, but UNION shouldn't..
0
 

Author Comment

by:Todd_Anderson
ID: 24380716
I was thinking about something like this but it gets really complicated when you have six queries which are built up programatically and may or may not be included altogether.  Tryng to programatically build all the parenthesis correctly is a big mess.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Todd_Anderson
ID: 24380717
No, I am using just union.
0
 

Author Comment

by:Todd_Anderson
ID: 24380723
Quetzal,

I am seeing what you are saying better now.  I may be able to do this.  Let me try and I'll get right back to you.
0
 
LVL 11

Expert Comment

by:Quetzal
ID: 24380755
Here is my technique.  Create the Union query in Access, let's call it Query1.  Create the Select Distinct query using Query1 as the table, call it Query2.  In design mode for Query2, right-click Query1 table and select Properties, change name to X.  In design mode for Query2, switch to SQL View.  You will see SELECT ColA from Query1 AS X.  Open Query1, switch to SQL View and copy the SQL Query, except for the trailing semi-colon.  In the SQL View of Query2, replace Query1 with () and insert the copied SQL text from Query 1.  Verify that the query works.  Copy the SQL text to your program.

It is possible to build extremely complex queries in this fashion.
0
 
LVL 11

Expert Comment

by:Quetzal
ID: 24380760
Clarification. Insert the SQL text from Query1 between the left and right paren.
0
 

Author Comment

by:Todd_Anderson
ID: 24380826
Quetzal,

Works great!  I just surrounded my final query with

    SELECT DISTINCT * FROM (    my built up query here    )

Todd
0
 
LVL 11

Expert Comment

by:Quetzal
ID: 24380828
Here is a somewhat more complex example.  I am constructing a query on the fly based on information in a form that is modified by the user.  You will note that I am composing a fairly complex SQL statement in pieces.  In my SQL string constants, you will see %name% and that is how and where I am subsituting the various pieces that I am building on the fly.  I composed this code using the technique I described above.

I apologize in advance if I overwhelm you with this example.  I just wanted to show you how complex a statement you can create with this method.
SQLConstruct.txt
0
 

Author Comment

by:Todd_Anderson
ID: 24380911
Quetzal,

Thanks for the example.  I looked it over breifly and I see several things that look interesting that I will work through.

Thanks for the help,

Todd
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
I suddenly cannot write to C drive 20 71
Access query expression 6 19
IIF help, YN field 7 21
Cascading Combo boxes between 2 sub navigation forms 1 9
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

815 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now