A little help with Master Child subform using 3 tables

[dbstrat]

Hi.

I have 3 tables that I need to display on a master-child form/subform. Think of it as Orders and Order Details.

The tables are

Orders
OrderDetails
VendorItems

The reason for the complexity is that my users can get the same item from multiple vendors. When they enter the order detail I want them to enter the vendor part number but the actual field they will update in OrderDetails will be ItemID which identifies each distinct part in the Item table.

Therefore, the order has a VendorID. I want to avoid storing the VendorID in the OrderDetail table as well, but maybe I need to relax that requirement and get over it. Anyway, the Order and OrderDetail form/subforms are linked by OrderID and then each line item in the detail is a unique OrderDetailID. To do my lookups for VendorItem (vendor's part number, which they will enter but the system unique ItemID will go in the OrderDetail table) and VendorDesc based on VendorID which is on the parent form.

Hopefully this makes sense. What is the best way to have a subform be able to get a field from the parent form that is not the link field?

Should I just bite the bullet and add VendorID to the detail table?

Thanks.

[FishVal]

Hi, there.

[VendorItems] is the table linking Items table and Vendors table in many-to-many relationship, am I right?
So why not to store [VendorItems] PK in [OrderDetails]?

[dbstrat]

Hi, there.

[VendorItems] is the table linking Items table and Vendors table in many-to-many relationship, am I right?
So why not to store [VendorItems] PK in [OrderDetails]?

You know, that sounds like a pretty good idea actually. I'm going to give that a shot, then for reporting I can always use the Items table in a join to find an item regardless of the originating vendor.

Thanks for showing me something simple that I couldn't see.