How do I concatenate two tables/queries to look like One Table?

[azstyx]

I have a query which needs to combine the reslts of two other queries so that the record set looks like one table with the combined unequal fields from both query results.
The two source queries summarize some totals and create exactly one record of summary total fields.
In my QBE grid, I have the two Queries posted and have all their fields listed down on the grid.
Example:
qryInventory
Creates one record with fields tot1, tot2
qryOnOrder
creates one record with fileds tot3, tot4.
Fields tot1, tot2, tot3 tot4 are all on the QBE grid.
This works OK as long as both queries return a record. In the case that , say qryOnOrder does not return a record and the recordset is null, the whole recordset results becomes null for the entire query.
Is there any simple way I can instruct the Query to build the result even if one of the two source queries return a null recordset?
THX!!
Keith

 

[scriverb]

You can do this with a UNION query. The easiest way to do this is to create a query to select the records from the first table/query and copy the SQL code. Include in this query the sorting needs.

Create a new query and when you are prompted for the tables selection windows, just close it. Don't select anything. Now from the Menu select Query/query specific/union query. You can now paste this code into the SQL window. Remove the semicolon. Retrieve the SQl code from the second table/query and now you can enter UNION after the first one and then paste in the SQL from the second query. Both of these selects must have the same fields in both. The first select must have the field set from the second one added with nulls, and empty strings, and zeroes(0) for the values. The same goes for the second select.

Example:
Select A.Name, A.Address, A.City, A.State, A.Zip, Null as ClassID, Null as Instructor, Null as ClassTime
FROM tableA as A
Order By A.Name
UNION
Select Null as Name, Null as Address, Null as City, Null as State, Null as Zip, B.ClassID, B.Instructor, B.ClassTime
FROM tableB as B;

The recordset from this UNION query will look like a single table with 8 fields. I hope this is instructive enough for you to create your query.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[azstyx]

Yes, I thought about that and when I tried it, it gave me multiple records as the result. Since both source queries result in one record only with total field, the result with a union query gave me 2 records. What I want to do is make the two queries (or they could very well be unrelated tables) behave as one virtual table:
example:
qryInventory
Creates one record with fields tot1, tot2
qryOnOrder
creates one record with fileds tot3, tot4.

New query combines the two source queries to look like a single table of one record with fileds:
tot1, tot2, tot3, tot4.
K

 

[azstyx]

ok, tried to play around with Union some more and got no where:
tblotal1 has one rec:
tot1=$10.00
tot2=$20.00

tbltotal2 has one rec:
tot3=$30.00
tot4=$40.0

Union Query:
SELECT tot1, tot2, Null as tot3, Null as tot4
FROM tbltotal1
UNION SELECT Null as tot1, Null as tot2, tot3, tot4
FROM tbltotal2;

This produced 2 records with 4 fields:
fields:tot1, tot2, tot3, tot4

Rec#1:
tot1= blank
tot2= blank
tot3= unregognizable character
tot4= unrecognizable character

Rec#2:
tot1=$10.00
tot2=$20.00
tot3=blank
tot4=blank

What where the Nulls suppose to do in the Query?
THX,K

 

[azstyx]

Here's the results of another UNION query:
SELECT tot1, tot2, "" as tot3, "" as tot4
FROM tbltotal1
UNION SELECT "" as tot1, "" as tot2, tot3, tot4
FROM tbltotal2;
This too gave me 2 records:
rec#1:
tot1=blank
tot2=blank
tot3=$30.00
tot4=$40.00
Rec#2:
tot1=$10.00
tot2=$20.00
tot3=blank
tot4=blank

What I really want is to hortizontally splice the two tables to get one table with one record like this:
rec#1:
tot1=$10.00
tot2=$20.00
tot3=$30.00
tot4=$40.00

THX,
K

 

[scriverb]

Okay, first of all are the two tables related in any way? Do they both have a common ID that you want to be used to put the records together? If that is the case then we do this a little differently.

Let's say that both queries are using tables that have a customerID as the commond field and you want all four fields to be on the same row with the CustomerID identifying what the values mean. Is this the situation?

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[SIG357]

If all you want to do is combine the fields of 2 talbles with 1 record each, you can just use a cross:

SELECT tot1, tot2, tot3, tot4 FROM tbltotal1, tbltotal2

scriverb brings up an important point if these tables are ever to contain more than one record, though. Unless there is an ID in each table that you can use to relate desired records in a Join, computing a cross product of the two tables will result in a mishmash of all the records of each table

 

[azstyx]

Hi!
Yes the SQL to give the Cartesian results:
SELECT tblTotal1.tot1, tblTotal1.tot2, tblTotal2.tot3, tblTotal2.tot4
FROM tblTotal1, tblTotal2;
works fine as long as there is at least one record in the second table (tblTotal2) i used.

However, if the 2nd table does not conatin any records, thats where the problem comes in. In that case the Cartesian join gives a total query result of an empty recordset.
I was wondering if there was any way around this.
In reality, I have a form the user uses to see a list of summary data that is collected form two source queries. it was just brought to my attention that the form was not loading any data, which should be one record only with all the fields from the two source queries.
I investigated and found that the first query returned its usual one record and the second query returned an empty record set, thus the Cartesian join resulted in a total empty record set. The 2nd query is working properly as there was no summary data for it to compute in this case.

So, does anone know of any way to force the Cartesian join to produce a record even if the 2nd table/query has no records?


THX!!!
K

 

[azstyx]

Hi scriverb,
Unfortunately, I dont have a common field between the two qeries, otherwise I would create an outer join.
In reality here are the 3 queries....i didnt post them before because I was trying to sikmply the problem and use an example from a "ply" database and duplicate the issue, which i did.
But here are the real things:
inventory-totals query: (produced its usual one record ok)
SELECT Sum([Products].[In Stock]) AS [SumOfIn Stock], Sum([Products].[Committed]) AS SumOfCommitted, Sum([committed]*[Unit price]) AS Expr1, Sum([in stock]*[unit price]) AS Expr2
FROM Products
WHERE (((Products.obsolete)=False));

po-totals query: (produced an empty dataset)
SELECT Sum([PO-inside].[Part Quantity]) AS [SumOfPart Quantity], Sum([PO-inside].[Quantity Received]) AS [SumOfQuantity Received]
FROM Products INNER JOIN [PO-inside] ON Products.[Part Number] = [PO-inside].[Part Number]
GROUP BY Products.obsolete
HAVING (((Products.obsolete)=False));

form query: (this fails if there are no records in the po-totals query)
SELECT [PO totals].[SumOfPart Quantity], [Inventory-Totals].[SumOfIn Stock], [Inventory-Totals].SumOfCommitted, [Inventory-Totals].Expr1, [Inventory-Totals].Expr2, [PO totals].[SumOfQuantity Received], [sumofpart quantity]-[sumofquantity received] AS QOO
FROM [PO totals], [Inventory-Totals];

 

[scriverb]

YES, a Select query with a left join between the two record sources will give these results but you must have a common identifying field. What are these records the total of? Give me that field that is common in both and a simple Select with a left join will give you the recordset that you are looking for.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Sorry, didn't see the Select query example above:
SELECT Sum([PO-inside].[Part Quantity]) AS [SumOfPart Quantity], Sum([PO-inside].[Quantity Received]) AS [SumOfQuantity Received]
FROM Products LEFT JOIN [PO-inside] ON Products.[Part Number] = [PO-inside].[Part Number]
GROUP BY Products.obsolete
HAVING (((Products.obsolete)=False));


This query will give you one row per Part Number whether or not there is a matching record in the table PO_inside or not.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[azstyx]

Yes, the GROUP BY Products.obsolete
HAVING (((Products.obsolete)=False));
produces one record with the two summary totals. It just so happens in this database , since it is a new startup company they have no PO records yet, just inventory records so the form query failed.
What i tried to do was create an expression in the inventory-totals query such as join:"00" and it added the field to the resulting record ok.
I tried to do the same thing with the po-totals query but it would not let me create an expression like i did with inventory-totals query. If i could get the po-totals query to least write a record with nulls, that would be cool.
Any way to do that,,,, force it to create a record even if there are no records for it to sum?

Hince, i was wondering what my work arounds were in this case. I could create a subform that would just read the po-totals query, but i was hoping that there was a more easier and simplier way of getting around the issue.


THX!!
Keith

 

[scriverb]

I have modified all three queries. After updating the queries try running the last one.

inventory-totals query:
SELECT Products.[Part Number], Sum([Products].[In Stock]) AS [SumOfIn Stock], Sum([Products].[Committed]) AS SumOfCommitted, Sum([committed]*[Unit price]) AS Expr1, Sum([in stock]*[unit price]) AS Expr2
FROM Products
WHERE (((Products.obsolete)=False))
GROUP BY Products.[Part Number];

po-totals query:
SELECT First([PO-inside].[Part Number]), Sum([PO-inside].[Part Quantity]) AS [SumOfPart Quantity], Sum([PO-inside].[Quantity Received]) AS [SumOfQuantity Received]
FROM Products INNER JOIN [PO-inside] ON Products.[Part Number] = [PO-inside].[Part Number]
GROUP BY Products.obsolete
HAVING (((Products.obsolete)=False));

form query:
SELECT IT.[Part Number], POT.[SumOfPart Quantity], IT.[SumOfIn Stock], IT.SumOfCommitted, IT.Expr1, IT.Expr2, POT.[SumOfQuantity Received], POT.[sumofpart quantity]-POT.[sumofquantity received] AS QOO
FROM [Inventory-Totals] as IT LEFT JOIN [PO totals] AS POT ON IT.[Part Number] = POT.[Part Number];

Let me know how this works for you.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[azstyx]

Hi Bob,
The inventory-totals created a multi-record result.
I have decided to re-write all the original queries and build a table with one record, then I will have the summary queries update the corresponding summary field in the table with one record.
K