By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,700 Online
Bytes IT Community
+ Ask a Question
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
  1. TRANSFORM Sum(tblOrders.QtyOrdered) AS SumOfQtyOrdered
  2. SELECT tblOrders.DateOrdered, Sum(tblOrders.QtyOrdered) AS [Total Of QtyOrdered]
  3. FROM tblOrders
  4. GROUP BY tblOrders.DateOrdered
  5. PIVOT tblOrders.MaterialType;
Output:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(tblOrders.QtyCompleted) AS SumOfQtyCompleted
  2. SELECT tblOrders.DateCompleted, Sum(tblOrders.QtyCompleted) AS [Total Of QtyCompleted]
  3. FROM tblOrders
  4. GROUP BY tblOrders.DateCompleted
  5. PIVOT tblOrders.MaterialType;
  6.  
Union:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [qryTPInput_Crosstab]
  2. UNION SELECT * FROM [qryTPOutput_Crosstab]
  3.  
As always, Thanks in Advance!
Aug 9 '10 #1
Share this Question
Share on Google+
14 Replies


ADezii
Expert 5K+
P: 8,699
  1. Convert your Input and Output Crosstab Queries into Make Table Queries.
  2. 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
  1. SELECT * FROM [tblTPInput]
  2. UNION SELECT * FROM [tblTPOutput];
Aug 9 '10 #3

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

ADezii
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
File Type: zip TPRS.zip (3.60 MB, 117 views)
Aug 11 '10 #6

NeoPa
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

ADezii
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

NeoPa
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

ADezii
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

ADezii
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
    18.     rstResults.AddNew
    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
    35.     rstResults.AddNew
    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
    52. DoCmd.OpenTable "tblResults", acViewNormal, acReadOnly
  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
File Type: zip 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

ADezii
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.
  1. qryTPInput_Crosstab
    Expand|Select|Wrap|Line Numbers
    1. DateOrdered    SumOfQtyOrdered    ALUM    STEEL
    2. 8/10/2010            326           318      8
    3.  
  2. qryTPOutput_Crosstab
    Expand|Select|Wrap|Line Numbers
    1. DateCompleted    SumOfQtyCompleted    ALUM    STEEL
    2.                          0              0       0
    3. 8/10/2010               58             58    
    4. 8/11/2010               55             55
    5.  
  3. 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
  1. SELECT * FROM qryTPInput_Crosstab 
  2. UNION 
  3. SELECT * FROM  qryTPOutput_Crosstab;
returns 1 + 3 rows as per the two input queries:

Expand|Select|Wrap|Line Numbers
  1. DateOrdered  SumOfQtyOrdered  ALUM  STEEL
  2.                     0           0      0
  3. 10/08/2010                     58     58
  4. 10/08/2010        326         318      8
  5. 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

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.