459,341 Members | 1,700 Online
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

# Access 2003: Union Query Problem

 P: 19 I'm very new to creating union queries. I understand the purpose but not how to properly implement it for my purpose. So far I've created to perfectly fine crosstab queries. One shows input on a date ordered, the other shows the out put on a date completed. I am trying to combine these two charts into one chart. The code I've got so far will only show one date. I am looking to have 2 (4 if you count the break down into the two material types) line graphs. Here is the code for this so far. Input: Expand|Select|Wrap|Line Numbers TRANSFORM Sum(tblOrders.QtyOrdered) AS SumOfQtyOrdered SELECT tblOrders.DateOrdered, Sum(tblOrders.QtyOrdered) AS [Total Of QtyOrdered] FROM tblOrders GROUP BY tblOrders.DateOrdered PIVOT tblOrders.MaterialType; Output: Expand|Select|Wrap|Line Numbers TRANSFORM Sum(tblOrders.QtyCompleted) AS SumOfQtyCompleted SELECT tblOrders.DateCompleted, Sum(tblOrders.QtyCompleted) AS [Total Of QtyCompleted] FROM tblOrders GROUP BY tblOrders.DateCompleted PIVOT tblOrders.MaterialType;   Union: Expand|Select|Wrap|Line Numbers SELECT * FROM [qryTPInput_Crosstab] UNION SELECT * FROM [qryTPOutput_Crosstab]   As always, Thanks in Advance! Aug 9 '10 #1
14 Replies

 Expert 5K+ P: 8,699 Convert your Input and Output Crosstab Queries into Make Table Queries. Create a UNION Query based on these 2 Tables. Aug 9 '10 #2

 P: 19 I tried that and it still only returns the DateOrdered and the sum of qtyOrdered. I am still missing the DateCompleted and the QtyCompleted Expand|Select|Wrap|Line Numbers SELECT * FROM [tblTPInput] UNION SELECT * FROM [tblTPOutput]; Aug 9 '10 #3

 P: 19 Still needing help towards a solution Aug 11 '10 #4

 Expert 5K+ P: 8,699 Can you Upload the Database, not really sure what is goining on here, or I am missing something very obvious. Aug 11 '10 #5

P: 19
Thank you for looking into this!

Unzip and rename to .mdb

The two crosstabs I have are:

qryTPInput_crosstab

qryTPOutput_crosstab

I have since removed the non working union query and changed the crosstabs back from the original idea of make table queries.
Attached Files
 TPRS.zip (3.60 MB, 117 views)
Aug 11 '10 #6

 Expert Mod 15k+ P: 31,768 I doubt this can work (reliably at least). One of the main concepts of a CrossTab query is the number of columns is determined by the data rather than the design, whereas a precondition of UNIONing two queries is that they both have the same number, and type, of fields. Does that sound like a situation that can work together to you guys? Aug 11 '10 #7

 Expert 5K+ P: 8,699 @bknabl - You are fortunate in that you have one of the most highly qualified Experts, and also Moderator, who is very adept in this specific area, and who subscribed to this Post. I am referring to NeoPa. @NeoPa - Both Crosstabs output the same number of Fields whose Data Types are also exactly the same. I honestly do not see why a MAKE TABLE/UNION Query would not work under these exact conditions, but it does not seem to. @bknabl - I guess that we could create 2 Recordsets based on the Crosstabs, then populate a Results Table. This would surely guarantee that the resulting Values are correct, but I'll hold off on this for now and wait and see what NeoPa or other Members have to say on the matter. Aug 12 '10 #8

 Expert Mod 15k+ P: 31,768 I've pretty well shot my bolt on this one ADezii. I have only used CrossTabs a few times in my whole career I'm afraid. I suspect though, if you're falling over issues with this, then Access is not refusing at the point where it checks the output of the CrossTabs, but rather earlier on in the parsing where it simply says - "This is a CrossTab. No way am I gonna get caught with my pants down on this one! I'm not even touching it." I must say that would probably be my approach if I were designing a database system to fit where Access does. I checked in help, and though it's not very definitive on the subject, it does say the allowed sources are any of : Table QueryDef SELECT statement While the CrossTab can be stored in a QueryDef, so can other types of query (such as UPDATE or INSERT). I would read that to mean only QueryDefs consistent with the other SQL statements listed. In that case only SELECT is listed. TRANSFORM is not, so I would read that to be saying UNION doesn't support including CrossTabs. I may be misreading it of course, as it's not too explicit, but that would be my reading of it. Aug 12 '10 #9

 Expert 5K+ P: 8,699 Thanks for your insight into this matter, NeoPa. It is always valued and appreciated. @bknabl - I'm going to attempt a Code-based approach on this dilemma and see what happens. Stay tuned to this station (LOL). Aug 12 '10 #10

Expert 5K+
P: 8,699
I do believe that I have arrived at a solution to your dilemma, though nothing like what was initially envisioned.
1. Create a Table named tblResults consisting of the following Fields:
1. [Date] {DATE}
2. [Quantity] {LONG}
3. [ALUM] {LONG}
4. [STEEL] {LONG}
2. Copy-N-Paste the following Code to wherever you deem necessary, then Execute it.
Expand|Select|Wrap|Line Numbers
1. Dim MyDB As Database
2. Dim rstInput As DAO.Recordset
3. Dim rstOutput As DAO.Recordset
4. Dim rstResults As DAO.Recordset
5.
6. 'Clear the Results (tblResults) Table
7. CurrentDb.Execute "DELETE * FROM tblresults;", dbFailOnError
8.
9. 'Setup the 3 required Recordsets, namely: Input, Output, and Results
10. Set MyDB = CurrentDb
11. Set rstInput = MyDB.OpenRecordset("qryTPInput_Crosstab", dbOpenForwardOnly)
12. Set rstOutput = MyDB.OpenRecordset("qryTPOutput_Crosstab", dbOpenForwardOnly)
13. Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
14.
15. 'Append the Results of the Input Crosstab Query to tblResults
16. With rstInput
17.   Do While Not .EOF
19.       rstResults![Date] = .Fields(0)
20.       rstResults![Quantity] = .Fields(1)
21.       rstResults![ALUM] = .Fields(2)
22.       rstResults![STEEL] = .Fields(3)
23.     rstResults.Update
24.     .MoveNext
25.   Loop
26. End With
27.
28. 'Clean up the Input Recordset
29. rstInput.Close
30. Set rstInput = Nothing
31.
32. 'Append the Results of the Output Crosstab Query to tblResults
33. With rstOutput
34.   Do While Not .EOF
36.       rstResults![Date] = .Fields(0)
37.       rstResults![Quantity] = .Fields(1)
38.       rstResults![ALUM] = .Fields(2)
39.       rstResults![STEEL] = .Fields(3)
40.     rstResults.Update
41.     .MoveNext
42.   Loop
43. End With
44.
45. 'Clean up the Input and Results Recordsets
46. rstResults.Close
47. rstOutput.Close
48. Set rstResults = Nothing
49. Set rstOutput = Nothing
50.
51. 'Let's see the results
3. Forget everything that I just told you, and download the Attachment to get a better Picture of exactly what is going on.
4. Any questions, feel free to ask.
Attached Files
 Crosstab_UNION.zip (24.9 KB, 97 views)
Aug 12 '10 #11

 P: 19 Wow, you have no idea how much I appreciate all this hard work from you guys/gals. However the solution you provided me I believe is only adding the input to the output. I am not trying to do that. I am mearly trying to display both the input and the output on a single graph. Or perphaps I am doing something wrong? Aug 12 '10 #12

 Expert 5K+ P: 8,699 Sorry, but now I am really confused. Kindly Post the results of what the so called UNION of qryTPInput_Crosstab and qryTPOutput_Crosstab should be. I have a feeling that you are referring to a Summation rather than a UNION, but until I see the resultant data, I'll just wait and see. qryTPInput_Crosstab Expand|Select|Wrap|Line Numbers DateOrdered    SumOfQtyOrdered    ALUM    STEEL 8/10/2010            326           318      8   qryTPOutput_Crosstab Expand|Select|Wrap|Line Numbers DateCompleted    SumOfQtyCompleted    ALUM    STEEL                          0              0       0 8/10/2010               58             58     8/11/2010               55             55   Result DataSet? Aug 12 '10 #13

 Expert Mod 2.5K+ P: 2,545 Hi all. I too am confused. ADezii showed the results of running each crosstab separately in post 13 above. There are only two separate dates within the data supplied - 10 August and 11 August. One row of the output query returns a null date and 0 values, as ADezii's post shows. I find that unioning these two crosstab queries works just as expected. Executing Expand|Select|Wrap|Line Numbers SELECT * FROM qryTPInput_Crosstab  UNION  SELECT * FROM  qryTPOutput_Crosstab; returns 1 + 3 rows as per the two input queries: Expand|Select|Wrap|Line Numbers DateOrdered  SumOfQtyOrdered  ALUM  STEEL                     0           0      0 10/08/2010                     58     58 10/08/2010        326         318      8 11/08/2010                     55     55 I cannot see much that can be done with these rows at all, so it all seems to be a bit misguided as an approach. As for putting these in line charts (post #1) - line charts should be used to plot continuous functions or values which can be treated to all intents and purposes as continuous (total sales by month, for example). What have these values got to do with a continuous function? By the way, NeoPa was right to question unioning crosstabs as an approach; it can only work in the special case where the number of columns returned by the crosstabs is equal, and in most crosstab applications this is highly unlikely, except where all queries involved in the Union have exactly the same set of column properties applied. -Stewart Aug 12 '10 #14

 Expert Mod 15k+ P: 31,768 It's interesting that, from Stewart's post, it is possible to UNION CrossTabs, at least as long as they are used as subqueries. IE. You may not use UNION ==> TRANSPOSE, but you can use UNION ==> SELECT * FROM (TRANSPOSE...). This would be the logical equivalent of UNION ==> stored QueryDef of a CroosTab. Aug 12 '10 #15