Full Outer Join Queries and Make Table/Append Query

[scorpio]

I am fairly new to Acces 2010. Here is my question.

I have two seperate tables hat I need to use to compare data. As you can see table A and table B have some of the same item numbers but they also have different item numbers that are not other table. Also some of the item numbers are duplicated in each table but that is okay because the cost of the item is different. Both tables contain item numbers for the products. I want all of Table A item numbers including the item numbers that are in table B. But I also want Table B item numbers except for the item numbers that are also in Table A. In the real raw data file some of the item number fields are blank but the other fields have values. How should I query these tables so that I achieve the correct results? Thank You


Table A
Item Num Costof Item Supplier Sales Tax Purchase Month
1234 $1.00 Walmart $2.00 Dec 2013
2222 $4.00 Walmart $1.00 Dec 2013
2222 $2.00 Walmart $1.00 Dec 2013
1276 $3.00 Sams club $1.50 Dec 2013
7898 $5.00 Texaco $5.00 Dec 2013
4567 $3.50 Food Lion $1.00 Dec 2013

Table B
Item Num Cost of Item Supplier SalesTax Purchase Month
1234 $1.00 Walmart $2.00 Jan 2014
2222 $4.00 Walmart $1.00 Jan 2014
1276 $3.00 Sams club $1.50 Jan 2014
7865 $6.00 Pizza Hut $2.00 Jan 2014
7654 $4.50 BP $2.00 Jan2014


7654 $3.50 BP $2.00 Jan2014
8754 $2.75 Johns $1.65 Jan 2014
4326 $3.25 Coke $3.00 Jan 2014

[June7]

A UNION query can generate a dataset of all unique item numbers. Do you need the cost, supplier, and date info as well? If you include all of that additional info, the item numbers will duplicate because the records will be unique.

If the item number field is blank, I don't see a way to relate the records. Those records are meaningless.

A Find Unmatched query can show which item numbers are in one table but not in the other.

I do hope this data is fake - SalesTax higher than the CostofItem?

[scorpio]

Thank You for the response

Yes the data is fake I was just trying to illustrate an example of my table.

Yes I will need item number, cost, supplier, sales tax and date.

The issue I had with the union query was it only included data from table A but not the data from table b that wasnt in table A.

My end result should include the following columns:
Item numbers from both table( a&B), Cost (table a) ,Cost (table b) ,Supplier(table a & b), Sales Tax(table a), Sales Tax (table b)


End Result
Item#table( a&B) Cost (tablea) Cost (table b) Supplier(tablea & b) Sales Tax(table a) Sales Tax (table b)
1234 $1.00 1.00 Walmart $2.00 3.00
1234 $1.00 1.00 Walmart $2.00 2.00
2222 $4.00 2.00 Walmart $1.00 1.00
2222 $2.00 3.00 Walmart $1.00 3.00
2222 $4.00 3.00 Walmart $1.00 4.00
1276 $3.00 3.00 Samsclub $1.50 2.00
1276 $3.00 3.00 Samsclub $1.50 2.00
7898 $5.00 5.00 Texaco $5.00 0.00
4567 $3.50 3.50 FoodLion $1.00 0.00
7865 $6.00 6.00 PizzaHut 0.00 $2.00
7654 $4.50 4.50 BP 0.00 $2.00
7654 $3.50 3.50 BP 0.00 $2.00
8754 $2.75 2.75 Johns 0.00 $1.65
4326 $3.25 3.25 Coke $0.00 $3.00

[June7]

Not understanding the result. For example, Item Number 2222: the values you show under Cost(Table b) are not in TableB.

A UNION query will pull records from both tables - and using the ALL qualifier will pull them even if they duplicate - but the data from each table will not be on the same row.

A UNION query is:

SELECT [Item Num], [Costof Item], Supplier, [Sales Tax], [Purchase Month] FROM TableA
UNION SELECT ALL [Item Num], [Costof Item], Supplier, [Sales Tax], [Purchase Month] FROM TableB;