Access 2003: Union Query Problem

[bknabl]

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:

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:

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:

SELECT * FROM [qryTPInput_Crosstab]
UNION SELECT * FROM [qryTPOutput_Crosstab]
 

As always, Thanks in Advance!

[ADezii]

1. Convert your Input and Output Crosstab Queries into Make Table Queries.
2. Create a UNION Query based on these 2 Tables.

[bknabl]

I tried that and it still only returns the DateOrdered and the sum of qtyOrdered. I am still missing the DateCompleted and the QtyCompleted

SELECT * FROM [tblTPInput]
UNION SELECT * FROM [tblTPOutput];

[bknabl]

Still needing help towards a solution

[ADezii]

Can you Upload the Database, not really sure what is goining on here, or I am missing something very obvious.

[bknabl]

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.

[NeoPa]

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?

[ADezii]

@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.

[NeoPa]

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.

[ADezii]

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).

[ADezii]

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.
   

   Dim MyDB As Database
   Dim rstInput As DAO.Recordset
   Dim rstOutput As DAO.Recordset
   Dim rstResults As DAO.Recordset
    
   'Clear the Results (tblResults) Table
   CurrentDb.Execute "DELETE * FROM tblresults;", dbFailOnError
    
   'Setup the 3 required Recordsets, namely: Input, Output, and Results
   Set MyDB = CurrentDb
   Set rstInput = MyDB.OpenRecordset("qryTPInput_Crosstab", dbOpenForwardOnly)
   Set rstOutput = MyDB.OpenRecordset("qryTPOutput_Crosstab", dbOpenForwardOnly)
   Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    
   'Append the Results of the Input Crosstab Query to tblResults
   With rstInput
     Do While Not .EOF
       rstResults.AddNew
         rstResults![Date] = .Fields(0)
         rstResults![Quantity] = .Fields(1)
         rstResults![ALUM] = .Fields(2)
         rstResults![STEEL] = .Fields(3)
       rstResults.Update
       .MoveNext
     Loop
   End With
    
   'Clean up the Input Recordset
   rstInput.Close
   Set rstInput = Nothing
    
   'Append the Results of the Output Crosstab Query to tblResults
   With rstOutput
     Do While Not .EOF
       rstResults.AddNew
         rstResults![Date] = .Fields(0)
         rstResults![Quantity] = .Fields(1)
         rstResults![ALUM] = .Fields(2)
         rstResults![STEEL] = .Fields(3)
       rstResults.Update
       .MoveNext
     Loop
   End With
    
   'Clean up the Input and Results Recordsets
   rstResults.Close
   rstOutput.Close
   Set rstResults = Nothing
   Set rstOutput = Nothing
    
   'Let's see the results
   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.

[bknabl]

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?

[ADezii]

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
   

   DateOrdered    SumOfQtyOrdered    ALUM    STEEL
   8/10/2010            326           318      8
    

2. qryTPOutput_Crosstab
   

   DateCompleted    SumOfQtyCompleted    ALUM    STEEL
                            0              0       0
   8/10/2010               58             58    
   8/11/2010               55             55
    

3. Result DataSet?

[Stewart Ross]

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

SELECT * FROM qryTPInput_Crosstab 
UNION 
SELECT * FROM  qryTPOutput_Crosstab;

returns 1 + 3 rows as per the two input queries:

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

[NeoPa]

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.