Windows 10 
Access 2013 64bit
Hello, I'm a first time database developer, I've been working slowly and researching the answers to problems as they come up, but this one has me truly stumped.
Background: This is a database to track Requests for Proposal (RFPs). An RFP is like a sales lead, but it is a discreet event, with an issue date and a due date. A prospective client can (and usually does) issue RFPs to our organization more than once, and they can (and usually do) continue issuing RFPs even after becoming clients. My team supports more than a dozen different products that we might be pitching in a response. Depending on the prospect/client and product in scope, various employees will be involved in responding to the RFP.
Tables: I have the following tables: RFPs, Employees, Clients/Prospects, Products. There are junction tables between the RFP table and each of the other three tables (each are many-to-many relationships), so 3 junction tables in all. (There are also a handfull of small tables that I'm using to populate comboboxes for things like "RFP status")
Problem: I'm trying to design the form that the user will use to create a new RFP record and populate the various junction tables with the appropriate Clients/Prospects, Employees, and Products. The issue is with the employees. A single subform that allows the user to add all of the employees at once will not work well because the user would like to add three distinct types of employees: "writer", "financial advisor", and "sales".
Writer: Each RFP has one and only one writer.
Financial Advisor (FA): These are special salespeople that are sometimes involved with an RFP. They tend to work in teams, but an RFP can also have more than one FA team.
Sales: This includes regional wholesalers, internal support, product specialists, and pretty much anyone who is not an FA or a Writer.
For Writer, even though in a perfect world I would like them to be associated with RFPs in the same junction table as the Sales and FAs, I created a field in the RFP table that holds the foreign key of the writer (from the Employee table), it filters on the appropriate field so that the user only sees eligible writers in the combobox. This seems like something I can live with unless there's a user friendly way to do the form that will populate the writer into the RFP-Employee junction table.
For FAs and Sales, I tried to have individual subforms for each (so two on the form), and each subform filtering employees to show only eligible FAs or eligible Salespeople, but it did not work. If one of the subforms is populated, when you try to add records to the other one, it overwrites an existing record rather than adding it onto the end. I searched the properties of the subform to see if I could change this behavior in settings, but I couldn't find anything.
Workaround: One potential workaround I thought of is to just have two different junction tables. One to capture RFPs-Advisors, and one to capture RFPs-Sales. Both junction tables would sit between RFPs and Employees, but each one could have their own subform. My hesitation here is that all of the information theoretically belongs in the same junction table. Am I creating a problem down the road if I do this?
Appreciate any help here, maybe there's just a property of subforms that I'm not aware of, or there's some other simple fix, or maybe I'm just conceptualizing the problem wrong.
Reply With Quote
