Creating a M-M relationship using a different M-M join table

[mergle]

Hello,
I haven't designed a database in many years and struggling with different scenarios for days with what seems should be easy to see, but not sure if I am able to. I now feel that I know enough to be dangerous.

The database is for a home exterior cladding subcontractor that performs work for homebuilders in various communities/subdivisions. I am just starting out on this and currently trying to create a purchase order process for buying various products/materials from different Vendors, which all have different prices for the same common materials they offer.

The way I see it, there is a M-M between tblVendor and tblMaterial (join table includes the MaterialCost for each VendorMaterialID item). Then there is a M-M between tblPurchaseOrder and what I think should be the tblVendorMaterial join table.

I will have a main form collect the BuilderID and CommunityID info to create a JobID. A subform will collect the VendorID, then a sub-subform will collect the various VendorMaterialID items with Quantity needed in a datasheet format.

I am wondering if it looks like I am on the right path with my table relationships, especially with using the tblVendorMaterial join table with tblPurchaseOrder to create the tblPOVendorMaterial join.

Thanks in advance for any suggestions.

 

[theDBguy]

Hi. I think you can remove the VendorID from tblPurchaseOrders, because you can derive that information from tblPOVendorMaterial.

 

[mergle]

OK, however I want the VendorMaterialID items to be a datasheet subform to show only vendor-specific material pricing for a given material item selected based on the VendorID that's selected on the purchase order main form. So, wouldnt I need the VendorID at the main form level to show these in the subform?

I have attached a form I have come up with at this point. The datasheet is the subform of the form with the Vendor combo box that I would like to have the material combo box only show vendor-specific materials. The image shows triplicate items in the material combo box as there are now three vendors in the vendors table. This is something else I am trying to work out, and why I am wondering if my relationships are wrong.

 

[theDBguy]

OK, however I want the VendorMaterialID items to be a datasheet subform to show only vendor-specific material pricing for a given material item selected based on the VendorID that's selected on the purchase order main form. So, wouldnt I need the VendorID at the main form level to show these in the subform?

I have attached a form I have come up with at this point. The datasheet is the subform of the form with the Vendor combo box that I would like to have the material combo box only show vendor-specific materials. The image shows triplicate items in the material combo box as there are now three vendors in the vendors table. This is something else I am trying to work out, and why I am wondering if my relationships are wrong.

View attachment 86622

Hi. If you want to filter the subform to a specific vendor, you can simply use an unbound combobox on the main form. There's no need to store that information twice.

 

[mergle]

OK, I will work to use the unbound combo box.
Other than that do you see any problem with using the tblVendorMaterial join table for the tblPOVendorMaterial join table?

 

[mergle]

Hi. If you want to filter the subform to a specific vendor, you can simply use an unbound combobox on the main form. There's no need to store that information twice.

I know it's probable that I am missing a basic principle somehwere, but after a lot of thought and trial it seems I would need the VendorID in tblPurchaseOrder as a FK.
As purchase orders are a 1-M with vendors, wouldn't I need the VendorID field stored in tblPurchaseOrder?

 

[theDBguy]

I know it's probable that I am missing a basic principle somehwere, but after a lot of thought and trial it seems I would need the VendorID in tblPurchaseOrder as a FK.
As purchase orders are a 1-M with vendors, wouldn't I need the VendorID field stored in tblPurchaseOrder?

Hi. If you think having it would make it easier for you, it's your database after all. To me, the vendor is accessible through the materials, so I would say it's not needed.

For example, if you use a datasheet subform, you can include the vendor in the subform with the materials information and use the built in filtering dropdown, and you won't need the unbound textbox on the main form.

 

[mergle]

For example, if you use a datasheet subform, you can include the vendor in the subform with the materials information and use the built in filtering dropdown, and you won't need the unbound textbox on the main form.

Wouldnt that require a VendorID to be selected for every record in the subform? Since a purchase order can only be associated with one vendor, seems your method would allow a different vendor on each material record.

I want the database to be designed and structured properly, so I am just trying to grasp your logic.

 

[theDBguy]

Wouldnt that require a VendorID to be selected for every record in the subform? Since a purchase order can only be associated with one vendor, seems your method would allow a different vendor on each material record.

I want the database to be designed and structured properly, so I am just trying to grasp your logic.

Hi. As far as I can see from the image of your table structure, adding the Vendor ID in the Purchase Order table would not prevent anyone from adding a material from a different vendor in the PO Materials table for that Purchase Order. You can give it a try and let us know what happens. Limiting a purchase order to a specific vendor doesn't mean you need Vendor ID in the Purchase Order table. You can do that through data entry validation. If you're using a form, that's where you would use an unbound Textbox to allow the user to select a vendor to limit the list of materials to add to the purchase order.

As I said earlier, you can add the Vendor ID to the table, but without that validation check, users can still add materials from other vendors to the purchase order. So, either way (with Vendor ID or not), you'll still need data entry validation, which to me means storing the Vendor ID is redundant.

Hope that makes sense...

 

[mergle]

Trying to make sense of it, so thank you for your patience. Also not sure how to perform the data validation you mention.

With my current design the materials combobox shows vendor-specific materials based on the vendor combobox selection. Once I add material items under one vendor, and then I change to another vendor, it does allow me to add material items from the newly selected vendor in the same PO...so you are correct in that.

A couple of issues:
1. When I make it as you suggest with the unbound combobox for vendor selection and then add some materials, and then change the vendor, the material combobox field on each row goes blank. When I reselect the material with the combobox the material item is shown again with the correct vendor price for each material.

2. When I close the form and reopen it, there is no vendor in the combobox shown (as it is unbound) and I lose the material description again. Once I select the original vendor the description shows again.

3. As I scroll through POs, the Vendor cbox shows the same "last selected" vendor for each record. How can I recall the actual vendor the material prices were based on? I know the VendorID is stored in the underlying material records. Do I somehow reference this from the vendor cbox to show the Vendor used?

 

[theDBguy]

Trying to make sense of it, so thank you for your patience. Also not sure how to perform the data validation you mention.

With my current design the materials combobox shows vendor-specific materials based on the vendor combobox selection. Once I add material items under one vendor, and then I change to another vendor, it does allow me to add material items from the newly selected vendor in the same PO...so you are correct in that.

A couple of issues:
1. When I make it as you suggest with the unbound combobox for vendor selection and then add some materials, and then change the vendor, the material combobox field on each row goes blank. When I reselect the material with the combobox the material item is shown again with the correct vendor price for each material.

2. When I close the form and reopen it, there is no vendor in the combobox shown (as it is unbound) and I lose the material description again. Once I select the original vendor the description shows again.

3. As I scroll through POs, the Vendor cbox shows the same "last selected" vendor for each record. How can I recall the actual vendor the material prices were based on? I know the VendorID is stored in the underlying material records. Do I somehow reference this from the vendor cbox to show the Vendor used?

Hi. I was going to address each question you had above. However, since I can't see your form's setup, I would only be guessing as to why you're experiencing those issues. Would it be easier/simpler to demonstrate what I am trying to explain if you could post a sample copy of your form/db with test data?

Okay, let me give it a shot anyway.

1. You said the material combobox field on each row goes blank. This sounds to me like you're using a cascading combobox. One solution to that is to use a separate textbox to display the underlying data. However, if you meant what you said earlier that each purchase order is strictly limited to one vendor, then this problem shouldn't happen anyway. In other words, the users wouldn't be allowed to change the vendor combobox, correct?
2. If you want to update the unbound combo with the current record's vendor info, then you can use the Current event.
3. I am assuming you're talking about the subform here. If so, again, this is probably due to your use of a Cascading Combobox (if I was correct in guessing you're using one). Otherwise, it would help to see your form setup or maybe you can explain more how you have set it up.

Hope that helps...

 

[mergle]

OK, attached is my sample db. Hopefully you can make sense of what I am trying to do.
Yes, I am trying to use the cascading type combobox for the material selection.
Thanks

 

[Pat Hartman]

I would leave VendorID in the purchase order table. A purchase order can be for only one vendor and you shouldn't have to drop down to a child table to figure this out. You need it immediately so you can use VendorID on the po table to filter the material selected.

I know it looks like a circular relationship but it really isn't.

 

[theDBguy]

OK, attached is my sample db. Hopefully you can make sense of what I am trying to do.
Yes, I am trying to use the cascading type combobox for the material selection.
Thanks

Hi. I took a quick look at your subform's Record Source, and it's a bit more complicated than it needs to be. I would suggest to try and keep the Record Source for each to have a single data source, rather than a complex query, as much as possible.

But, I tell you what, since someone else recommends you keep the VendorID, I will go back to what I said earlier. If it will make your life less complicated to use the VendorID, then I say go ahead and keep the VendorID. Rather than me modify the db you posted and find out in the end that you may still not feel comfortable using it, it's better to follow your gut for now.

One thing I would ask though, after you keep the VendorID, please let us know if all the issues you were concerned about goes away (especially, if the user changes the VendorID, for some reason).

Cheers!

 

[Pat Hartman]

mergle, I would go back to having VendorID in the po table. Also, once there are material records, you should prevent the vendor from being changed. That just messes everything up.

 

[mergle]

I would leave VendorID in the purchase order table. A purchase order can be for only one vendor and you shouldn't have to drop down to a child table to figure this out. You need it immediately so you can use VendorID on the po table to filter the material selected.

OK, its good to know I wasn’t totally off base. I think its the tblPOVendorMaterial join table utilizing the join table from the VendorMaterial that makes it look circular? Thank you.

Hi. I took a quick look at your subform's Record Source, and it's a bit more complicated than it needs to be. I would suggest to try and keep the Record Source for each to have a single data source, rather than a complex query, as much as possible.

OK, I probably dont need all the fields I think I do, so I will look at again. I had previously tried to consolidate tables into a query but the result turned out to be unupdateable.

But, I tell you what, since someone else recommends you keep the VendorID, I will go back to what I said earlier. If it will make your life less complicated to use the VendorID, then I say go ahead and keep the VendorID. Rather than me modify the db you posted and find out in the end that you may still not feel comfortable using it, it's better to follow your gut for now.

I appreciate that. It has been a while since I've done this and for some reason makes more since to me the way I did it. I struggle enough with all of the db concepts.

One thing I would ask though, after you keep the VendorID, please let us know if all the issues you were concerned about goes away (especially, if the user changes the VendorID, for some reason).

I have attached the db with the VendorID now in the tblPurchaseOrder table. I am having issues with the material description dissapearing once a vendor has been changed.
The first PO when you open the form shows a missing material descipt on first row. The 4 rows were created under Vendor2 and then I changed to Vendor1 and all material fields went blank. I then added the row 1 material item again under Vendor1. When you change back to Vendor 2 the other 3 fields come back.

It would be good to be able to change the vendor on the fly and update all material records with the vendor-specific pricing. Could you point me in the right direction?

Thanks for all the help.

 

[mergle]

mergle, I would go back to having VendorID in the po table. Also, once there are material records, you should prevent the vendor from being changed. That just messes everything up.

Pat Hartman, I didnt see this before my last post. Locking up the Vendor cbox is what I thought about doing. Is making all material item record refresh with the correct pricing, once a Vendor is changed, too complicated?

 

[Pat Hartman]

I am having issues with the material description dissapearing once a vendor has been changed.

This is because you should NOT allow vendor to be changed once you have saved any material. Either a PO is going to a single vendor or it is going to several vendors and they are responsible for picking their stuff out of the list which of course doesn't make sense. Once you have at least one material record, do NOT allow vendor to change and you won't have a problem.

 

[Pat Hartman]

Typically, when creating PO's or Orders you save the price at the time you create the PO/order detail row. That fixes the price to the price that was valid at that point in time. In some cases, you might want the price to change on an unshipped order/po. if that is the case, then you need to add that logic to the form where you change the price. After the new price is changed, run an update query that searches for open PO/orders and update the price.

 

[Pat Hartman]

I made some changes.
1. prevent vendor from being changed after any material has been added
2. make required fields required.
3. store cost in the po record
4. add costDT to the po material table. You need to add code to set the date when the price changes and also add code if you want to update the price on any outstanding POs. But, you also need to add a flag to the PO so you know which have not actually been sent yet because you can't change the cost after the PO is sent.