Query to select a specific number

[woknick]

I have two tables Barcode and Product. I want to take a number from the Barcode table and use it to search for a value in the Product table. So where their values are equal. The Primary Key is Barcode number in the barcode table, and there is a foreign key Barcode number in the Product table.

BARCODE TABLE
Barcode Number
110

PRODUCT TABLE
Barcode Number
102
103
110

So I want to take the value 110 from the BARCODE TABLE and search for that same value in the Product Table.

I hope this makes sense. Thanks in advance

 

[Stanly222]

Relationship

Have you formed a relationship between the Primary Key "Barcode Number" in the Barcode table and the Foreign Key "Barcode Number" in the Product table?

 

[woknick]

Yes I have, there is a many to many relationship

 

[Stanly222]

Possible Solution

You may have a reason for the many-to-many that I don't realize, but I would like to suggest a one-to-many. With barcode being the primary key in the barcode table there shouldn't be any duplicates. A one-to-many gives you a few options.

After the tables fill with data you will notice (if you haven't already) the "+" signs on the records in the barcode table. Clicking on one of them should open a subwindow to show you all records in the product table with that specific barcode.

If you would like something more user friendly, I would suggest forms.

Create a form off of the barcode table with a text or combo box for the barcode field.

Then insert a subform on that form that uses the product table. Put any necessary fields on the subform from the product table.

When using the new form you can enter the barcode you are looking up on the main form. Then it will display all records from the product table with that barcode on the subform. All other records will be left out.

I hope that helps-
Scott