Not really sure if this is a Layout, Database Relations, or Search problem.

We have a single database of parts with their nomenclature, stock number (NSN), and various other part info. When we order parts we assign a job number (WON) and make a list of parts needed for that job.

The relavent part of the layout looks like this.

We have another database to keep records of each order with the job number as the key field and fields for each parts nomenclature and stock number.

We would like to be able to type in the part number (in browse mode) and have the nomenclature for that part automatically fill in. Is there a way to pull parts of multiple records from the parts database into a single record in the ordering database?

One way would be to have a relationship (diagrammed in the Relationships Graph) for each part number of the order table and a corresponding TO of the stock table; there would be a separate set of Table Occurrences between each part number in the order table and the part number in the stock table. Then each nomenclature would just be the field from the relevant TO.

There is probably a more elegant way to do this but I cannot think of any at the moment.

Create a relationship between your NSN number in your job order database and your parts database. Then do a auto lookup in the job order db nomenclature field against that relationship. The nomenclature field should populate the value from the parts db

hope this helps

Thanks, we had thought of the auto-lookup feature but never pushed it until now. At first we had a problem with making mulitple relations between the two tables but made separate occurences for each one and now it works great.

Thanks again.