Help Combining Two Queries 1

[pkahlo]

I have two queries that I am trying to combine:

[u]Qry1[/u]
Month ItemName Quantity
04-01 Item1    10
04-02 Item4    12

[u]Qry2[/u]
Month ItemName Quantity
04-01 Item1    75
04-02 Item2    4
04-02 Item3    8

However, this is the best I can get:

[u]Combined Query[/u]
Month ItemName Quantity1 Quantity2
4-01 Item1     10         78

Since Item2, Item3, and Item4 are not in both queries they are not in the end result. How can I combine these two queries and not lose values that are only in one query?

Thanks,
Patrick

 

[BNPMike]

Perhaps you could say how you are 'combining' two queries. I'm not sure we all understand what you are doing.

If you are selecting from two tables, you need to do a UNION before doing your SUMs.

 

[JonFer]

The first step in creating a FULL JOIN in Access is to create a union of the unique primary keys from the tables.

Select Distinct PrimaryKey1, PrimaryKey2 From Table1
Union
Select Distinct PrimaryKey1, PrimaryKey2 From Table2

The Union keyword (as opposed to Union All) removes duplicates so you get the unique set of primary keys. Outer join your queries to the union query and you get a full join. You can add a field indicating which table is missing a row by checking for Nulls from that table.

 

[pkahlo]

Let me try to explain this better. I'm not tring to do a union query. I'm basically trying to get the exact output I stated, but with all values and not just the ones that are in both queries. Currently I am doing this with two inner joins between both the Month and ItemName fields on the two queries. As you can see the original queries only have 3 fields and the result I would like ot have 4, using both Quantity fields from each query.

Thanks!
Patrick

 

[amorous]

Try this:

SELECT t1.Month,t1.ItemName,t1.Quantity AS Quantity1, t2.Quantity AS Quantity2 FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Month=t2.Month AND t1.ItemName=t2.ItemName

Hope that helps

VJ

 

[JonFer]

tsar - your SQL is an Inner Join and is what pkahlo has already done based on the post.

pkahlo - To get all records from both tables in Access like you want (i.e. a FULL JOIN) cannot be done in a single query. You need to do it in two steps and one of the steps is a union query. There are a couple of ways to do this but my preferred is the Union query with the distinct primary keys used in an Outer Join query.

 

[pkahlo]

Thanks JonFer! I had to re-read both of your posts a few times before I understood what you were saying! This worked perfectly! Would be nice if I didn't have to have so many queries, but it works!

Thanks,
Patrick