Hello all! First of all, I'm new to Access and programming. I've made a few databases before, but nothing too complicated.

Right now, I'm trying to do something, but I'm not even sure it's possible.

The database tracks products and vendors. In the form to edit a product, I have a drop-down field where I can select all of the vendors that sell that particular product. However, I would also like a way to indicate whether the vendor is Preferred or Optional.

For example, let's say the product is cookies. The cookies are sold at CompanyA, CompanyB, but not CompanyC. And though both CompanyA and CompanyB sell the cookies, we prefer to buy from CompanyA.

So, the database would show that CompanyA is the preferred vendor, that CompanyB is an optional vendor, and that CompanyC is not an option.

Is this possible? I've tried to think of a way to combine different functions, but apparently I'm too new to MS Access to understand its capabilities.

Thanks!

​

Edit:

Okay, so I partially figured it out (thanks to u/wA5te6!), but now I have a whole new problem.

First, some background:

• Three tables are used in this situation: Product_Info, Vendor_Info, and Vendor_Status
  • Product_Info contains all of the product-specific information
  • Vendor_Info contains all of the vendor-specific information
  • Vendor_Status is the important one -- it's the many-to-many junction table that pulls in the primary keys from the first two, the name of the vendor, and also adds two yes/no fields (one for Preferred, one for Optional). It has two primary keys: ProdID (which links back to the Product_Info primary key) and VendID (which links back to the Vendor_Info primary key).
• Two forms are used: Product_Entry and Vendor_Status_Subform (which is a subform on the Product_Entry form)
• Two queries are used: Vendor_Status_Query_Parent and Vendor_Status_Query_Child
  • After doing some research, I figured that this is necessary because I want the subform to display ALL possible vendors, not just the ones that have already been identified as preferred or optional. The Child query pulls in all records from the Product_Info sheet, and the Parent query pulls in the relevant records from the Vendor_Info sheet.
  • This relationship seems to work -- the form shows all of the vendors regardless of whether they are preferred, optional, or neither

Now, this issue is that I can't change any of the checkboxes for vendors that have not already been identified as preferred or optional (aka the ones that do not currently have a corresponding record in the Vendor_Status table). If I try to check a box, an error pops up saying "You must enter a value in the "Vendor_Status_ProdID" field." This is the field that should link back to the original Product_Info primary key. (And note, after changing some things around, the same error occurs with the VendID field -- it's just that the ProdID error is the first one to trigger.)

So, (deep breath), it looks like the issue is that the query isn't associating the ProdID for the relevant vendor with the primary key for the relevant product (and presumably the same issue applies for VendID). How do I fix this?