Related Forms

[Mozencrath]

Hi All, I am totally stumped on this. My company does military sales, and the military buys its parts based on NSN numbers. An NSN number might have just one part number for it or it might have many different PN's from different manufacturers..I have two tables that are already populated with 10,000 records so I would not be adding any records, simply viewing. I exported the information from a paid service that provides this history... :



Table 1 "Products" has Fields:
National Stock Number ("NSN") (An NSN can have mutliple part numbers so each NSN might have multiple records)
Description
Weight
Part Number (A NSN has many different Part Numbers)

Table 2 "Contracts (Orders)" has fields
Contract No (A contract can have many NSN numbers therefore could have multiple records)
NSN
Total Price
Company Awarded the Contract

What I am trying to do is figure out how to do the relationships to allow a database to search by NSN to see all the contracts for that NSN or to search by Contract and see what NSN's are on that contract. For example, in the PRODUCTS table, if the NSN is 123-0987 and it has 3 part numbers, there will be 3 records for that NSN number. Any help or discussion would be greatly appreciated...

EDIT: I should note that not ALL NSN's have contracts which is why I need the info in two tables...

[ItsMe]

...figure out how to do the relationships to allow a database to search by NSN...

Is there any way you can JOIN your two tables? If not, you will have to search two different recordsets for each NSN search request.

[Mozencrath]

Is there any way you can JOIN your two tables? If not, you will have to search two different recordsets for each NSN search request.

Yes but how would I do that? Is there no way to create a third table like a Parts-Contracts details table?

[ItsMe]

If you build a query object using the Query Design option you can add your two tables. Then drag the field, I suspect NSN, from one table onto the other table to create the JOIN. You can add fields to the grid located towards the bottom of the window. You can view the data your new query retrieves using Datasheet View.

[Mozencrath]

If you build a query object using the Query Design option you can add your two tables. Then drag the field, I suspect NSN, from one table onto the other table to create the JOIN. You can add fields to the grid located towards the bottom of the window. You can view the data your new query retrieves using Datasheet View.

Im trying to stay away from queries and just figure out the proper relationships so I can use a search form