union query

[peljo]

Will you help me built a union query ? I have 2 queries, qryinput and qryoutput.Out of these query i have a third query called qryDiff substracting these
queries. However qryDiff does not show all the goods sold, only those goods that are substracted.Therefore i want to build an union query but somehow i cannot do it. Will you help ?

My first query, qryinput is :
SELECT [order details].ProductID, Sum([order details].Quantity) AS Sum1, orders.orderid
FROM (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
GROUP BY [order details].ProductID, orders.orderid;
My second query, qryoutput is :

SELECT [order details].ProductID, orders.orderid, [order details].Quantity AS Sum2
FROM ((orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN Customers ON orders.customerid = Customers.Customerid) INNER JOIN products ON [order details].ProductID = products.Productid;

My thirs query, qryDiff is :

SELECT qryProducts.ProductID, Sum(qryInput.Sum1) AS imported, Sum(qryOutput.Sum2) AS exported
FROM (qryProducts LEFT JOIN qryInput ON qryProducts.ProductID = qryInput.ProductID) LEFT JOIN qryOutput ON qryProducts.ProductID = qryOutput.ProductID
GROUP BY qryProducts.ProductID;

Now i want to convert the query qryDiff into an union query, but i cannot do it.I somehow managed to build a simple query showing all the products:
SELECT ProductID
FROM qryInput
UNION SELECT ProductID
FROM qryOutput;
This query shows the productsid indeed, but i cannot add the other items form the qryinput and qryoutput as the sum etc.
I also i cannot convert my query qryDiff into an uinion query, with fields for the imported and the exported sums and also for the product names.
Where is my error and now could i achieve my aim? My aim is that in the query qryDiff to show all the products, and not only the products sold.


I will be very grateful for any comments

 

[neileg]

I think you have misunderstood the purpose of a union query. Most queries in Access join 'horizontally' so that if you have 4 fields from one table and 5 fields from another you end up with 9 fields. A union query joins 'vertically' so that you need the same number of fields from each query so if you have 4 from one table and 4 from another you end up with 4 fields in the union.

If I am correct, then what you need to look at is the join in your query between the two sets of data. By default, Access applies an inner join (type 1 in Access terms). This retrieves data only when there are matching records on both sides of the join. That's why you are only getting result where there is an input and and an output.

You need to change the join by right clicking on the line and changing it to an outer join, either left or right depneding on how the tables are arranged. If you follow the guidance once you have opened the join type, you need to take the option that returns all of the input records and the output records that match.

I am assuming that you can't have an output record unless you have an input record.

 

[Guus2005]

please note that a union query performs a distinct. Same records on both ends of the union will be deleted(in the query result) so that one remains.
Use UNION ALL to show all the records. Since it won't have to perform a DISTINCT it is a bit faster also.

HTH

 

[ognjenovic]

Hi Peljo, I just saw your post, I have similar problem, want to have in one place product name ,input and output for that product, dates of input and output,name of suplier-buyer and total... So I would be grateful if you can tell me how did you salve your problem, did you use left or right join(i try with join but with no success) or union query???
Thank you

 

[apr pillai]

Few Tips on Union Queries: Union Query

 

[New2Access_and_VBA]

Hi,
I am creating a union query and faced an error "Query must have atleast one destination field" I am writing this code

SELECT FIELD6
FROM ImT2
UNION ALL
SELECT FIELD7,FIELD8,FIELD9,FIELD10,FIELD11,FIELD12,FIELD13,FIELD14,FIELD15,FIELD16
FROM ImT3

ImT2 & ImT3 are two tables. I want to union field6 with every field in ImT3 so that in the end I can use Max([field6]) to find the maximum value. actually i want to do this:


field6-----------field7---------field8----------field9---------max
5-----------------3-------------6--------------11-----------11
3-----------------8-------------2--------------4-------------8
1-----------------7-------------10-------------9------------10
9-----------------56------------23-------------3------------56


kindly help me!

 

[Guus2005]

Please read the union tips "apr pillai" provided and when you have a question please start your own thread.