Select field plus qualifier help request (1 Viewer)

[fenfool]

I'm hoping that writing this out will help me figure out how to do this. I'm setting up a database in Access 2010. One field is a list of outside companies that assist on a particular project (the main data item is a project...project number, name, date, budget, then company). I can do this fine...there are 120 companies, I have their names in a table, then have a drop-down linked to that table.

The problem is that now I need to add a percentage with each company...there can be up to 5 companies involved...their percentage could be 0%-100%, no decimals (0% means a company was invloved, but not financially). About half of the projects have no company involvement. The rest between 1 and 5. Before the percent issue came up, I had a single multi-select field linking to the Company table. But having to assign a percentage...I have created 5 separate Company fields, which each have a drop-down allowing a single selection from the Company field...next to each is a percentage field:

Project 101 - Company 1 Comp1% Company 2 Comp2% Company 3 Comp3% Company 4 Comp4% Company 5 Comp5%

It will work, but feels really, really clunky (and will take a bunch of time to enter in the initial data). Is there a better way to do this? I was thinking of a data array of some sort, but I'm not really sure how to implement it. And suggestions?

 

[plog]

You need a new table, I'd call it CompanyInvolvement. It would have 3 fields, 1 would link to your Projects table, the other would link to your Companies table and the third would hold the involvement percentage.

 

[llkhoutx]

You don't say how you are entering data. I would create a single record project form with a related continuous CoParticipation subform. The form might contain an unbound combo box for filtering projects. Both such forms should have navigation buttons visible.

I presume that a compnay can participate in more that 1 project. I'd separate company and participation into separate related tables and use a junction table to relate project to company. This avoids duplicious company records.

In my humble opinion, I think that your table design is inadequate for what you want to do.

 

[fenfool]

I think that's exactly where I wanted to go, my mind just wasn't taking me there. Thanks!

 

[fenfool]

Yeah, I think the junction table will do it.

Once the database is set up, it will be updated with 1-2 projects a month, and there's a 50/50 chance there will be no company involvement with what gets updated. I think a form with a selection of project number, one for the company, then a box for percentage to update the junction table will work for this. A company can indeed work on more than one project, so there is a potential need to sort on company...so I think Plog's suggestion will work well for this.