 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008Union Query
Can anyone show or tell me the basic steps for creating a union query? I'm trying to combine two queries by the PN field. Thanks. First of all, do you really want a UNION query? A union query "stacks" the records from source one onto records from source two. So, if you have records a,b, and c in source one and records d,e,f in source two, you end up with six records a b c d e f If that is what you want, then assuming your two queries have the same number of fields and each set of fields (field 1 from query 1 and field 1 from query 2) is of the same data type, you can make a union query fairly easily. -- Open one of the queries and switch to SQL view. Copy the SQL -- Make a new query with no table and no fields -- Select Query: SQL specific : Union from the menu -- Paste the query and remove the trailing semi-colon -- Type the following at the end of the text UNION ALL -- Open the other query in SQL view and copy the SQL statement -- Paste that after Union All If you want to eliminate duplicate records in the Union query, then change UNION ALL to UNION. Oh I forgot to mention, if the source queries contain an ORDER BY (Sort) remove the ORDER BY clause. If you want to impose a sort order you will need to add it to the very end of the UNION query. Use the field names from the first query to specify the sort. John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Can anyone show or tell me the basic steps for creating a union query? I'm > trying to combine two queries by the PN field. > > Thanks. Not sure then. I want to combine the records. I want to say take the 50 records from query one and add them to the 50 records in query two. That would give me a total of 100 records. Both queries have the same number of fields. Actually, I'm combining four queries. If not union then what would you recommend? Thanks. > First of all, do you really want a UNION query? A union query "stacks" the > records from source one onto records from source two. [quoted text clipped - 38 lines] > > > > Thanks. Then you do want a union query. You should be able to follow the instructions I gave and just add the other two tables So you would end up with something like: SELECT A, B, C, D FROM TableA UNION ALL SELECT A, C, Y, Z FROM TableB UNION ALL SELECT A, x, y, z FROM TableC UNION ALL SELECT A, B, C, D FROM TableD Try it, UNION queries don't destroy any data. John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Not sure then. I want to combine the records. > [quoted text clipped - 9 lines] >>> >>> Thanks. That worked! Another quick question. How would I format say field a and b to currency? Thanks. > Then you do want a union query. > [quoted text clipped - 35 lines] > >>> > >>> Thanks. Sorting is done at the end of the union, using the field names in the first query. SELECT A, B, C, D FROM TableA UNION ALL SELECT A, C, Y, Z FROM TableB UNION ALL SELECT A, x, y, z FROM TableC UNION ALL SELECT A, B, C, D FROM TableD ORDER BY C, D Formatting can get tricky. I would normally apply formatting using a control's format property on a form or report. If you format in the Union query you would have to use the format function AND that would change the value type into a string for the column. So, if you formatted column A as currency using the format function and attempted to sort by that column, you would get a text sort (1,10,100,12,2,3,5) instead of a numeric sort (1,2,3,5,10, 12,100). SELECT Format(TableA.A,"Currency") as A, b, c, d FROM TableA Union All ... John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > That worked! Another quick question. How would I format say field a and b > to currency? [quoted text clipped - 40 lines] >>>>> >>>>> Thanks. Perfect! Thanks for your help. > Sorting is done at the end of the union, using the field names in the first query. > [quoted text clipped - 71 lines] > >>>>> > >>>>> Thanks. And also sort (asending) > Then you do want a union query. > [quoted text clipped - 35 lines] > >>> > >>> Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.