Query to return multiple values

[rdowney79]

I have a Part Number that is a foreign key between the 'iSup_table' (Supplier_item) and the 'Part_variance' table (Component_part_no_finish_code). Based on the Part Number, I need to return 'Line_num'. The issue is that a Part Number can have more then one 'Line_num' in the iSup_table. I need a query that will return multiple 'Line_num' in the 'iSup_table' for each Part Number in the 'Part_variance' table, if applicable. I then need to update the 'Line_num' field in the 'Part_variance' table.

Part_variance table
------------------------------
Component_part_no_finish_code


iSup_table
----------------
Supplier_item
Line_num

The below query is returning the same 'Line_num' for each Part Number, when there is more than one 'Line_num' for each 'Part_num'. I hope this makes a little more sense?

UPDATE iSupTable RIGHT JOIN [Part_variance] 
ON iSupTable.[Supplier_item] = [Part_variance].[Component_part_no_finish_code] 
SET [Part_variance].[iSupp_line_no] = [Line_num];

Thanks in advance

Ray

 

[jdraw]

Can you try to describe your issue again --in real simple terms? Pretend you're talking to people who have no idea of your business or set up.
What do you mean by Part # is shared between two tables?

For Access you should restrict your field and object names to alphanumerics(A-Z0-9) and the underscore character "_". No embedded spaces and no special characters. This will save you hours of frustration with syntax.

Here is a tutorial that will help you with table design and relationships.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[rdowney79]

Tried to clean it up for clarification purposes. Hope that helps.

Thanks,
Ray

 

[rdowney79]

Issue solved. I went to a previous query in the DB and was able to get my desired results.

Thanks!