Hello All: I am trying to do a query to join two tables. One is our table of products we sell by a certain manfaucturer. We sell about half the items that Manufacturer makes. The other table is everything that manufacturer makes.

I am updating prices. I am pulling out Product ID from both tables and our price from our table and new prices from the manufactures table. The Query I did only shows matching fields. I want to remove old items from out table, and possibly add new items from the Manufacturer.

I hope this isnt too confusing. Would this be a many-to-many join?

Thanks

I think what you are looking for is the unmatched query. If I understand correctly, you want to see everything in your table that is not in your manufacturer's table (ie, they have stopped making it) and everything in your manufacturer's table that is not in your table (ie, you don't carry the item.)

You will need to make two queries using the Unmatched query wizard. If you go to Queries and click the new button, and select the "Find Unmatched Query Wizard" it should walk you through it.

I also want to see the items that match too. I am going to update the old prices with the new. I'll try the unmatched query though and see what that does. Thanks

To do that you'll right click on the join line and select option 2.

It's just a left join.

I tried that but it only shows the matching items in table 2. I want the query to show all items of both tables matched up. If there is no match in table 1 the field will be blank and vice versa for table 2. Sorry for the confusion.

It depends on which "direction" the join is. ie. if you join from the manufacturer table to your table or the other way around.

Example...if I have one table full of product ID's from the manufacturer and I want to know what ones are in there that aren't in mine, I'm going to join from the manufacturer table to my table and then right click and select option two. Then, I'll add the product ID from each table in my query and run it. So, you'll have a listing of all manufacturer ID's and there will be a blank in the other column when it doesn't find a match in your table.

This can be switched around to see which products are in your table that the manufacturer no longer offers.

That's how you do what you're asking so I hope that made sense.


I suppose I can post an example if you need it at this point. But if the above helped, then I won't.

Thanks, I'll try that each way then and see if that is what I'm looking for.