Help creating outer join query

[jobrien4]

So I am creating a database (Access 2010) that shows all the parts my company produces for various customers. Each part we produce will have a unique combination of Customer Number (number the company assigns internally), Part number (number customer gives us), and revision number (number customer gives us). I set those three fields as a composite key for the PartTable.

Some (but not all) of these parts will have a special operation performed on it (let's call it Beveling). On the PartTable, I have a Yes/No checkbox for a Bevel field indicating whether the part has this operation performed. Then I set up a query to filter only parts with Bevel = Yes.

From this query, I set up BevelInfoTable. This is to display the details of beveling operations. Some parts may multiple beveling operations performed on it.

I tried to create a outer join query that displays fields from the PartTable and fields from the BevelInfoTable. However, it seems to use the Autonumber ID and places the beveling info with parts with the same Autonumber ID (instead of with the same Part #).

What am I doing wrong? Is there a simplier or more efficient way of setting this up?

 

[PNGBill]

Your tblBevelInfo should have it's own unique BevelJobID and the relationship would be One to Many ie One tblPartTable to Many tblBevelInfo.
A field in tblBevelInfo, PartRef, will hold the Unique Record from tblPartTable, Composit Key and allow duplicates.

Your query/sql will select all records from tblBevelInfo where the Composit Key matches.

Your sql may have to create a new field (for the sql only) to get three fields in tblPartTable to be concatenated and match the PartRef field in tblbevelInfo.

 

[PNGBill]

[Each part we produce will have a unique combination of Customer Number (number the company assigns internally), Part number (number customer gives us), and revision number (number customer gives us). I set those three fields as a composite key for the PartTable.
/QUOTE]
Consider having your own Unique Primay Key for the record and use the above composit as other filed values only.

This way you ensure No Duplicates and have one field to reference rather the three when identifying a unique item that may have been bevelled three times.

If the customer returned the same item it would have the same three field reference but for you it should be a new unique job and have a different number.