Multi-value lookup field (1 Viewer)

maximus76

New member
Local time
Tomorrow, 05:52
Joined
Oct 14, 2019
Messages
3
Apologies for asking a noob question.

I'm trying to create a record (business activity) that I want to link to multiple companies which are in my company table (tblcompany). I looked to add a multi value lookup field in the Activities table (tblActivities) to link the companies but found you cant import multi value field in via excel, did some further research and found lots of people saying to avoid multi value lookup fields.

Trying to work out how I can best do this without a multi -value field.

Each activity in tblActivities needs to be linked to multiple companies that are in the company table.

tblCompany (holds company information)
tblActivities (holds business activities)

I have a form which allows users to select a Company (via combobox) and the aim is to have a subform/subreport then load showing all the business Activities that company has been involved in.

Appreciate any help or advice.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:52
Joined
May 7, 2009
Messages
19,169
add ActivityID (long, FK to tblActivities) to your tblCompany.
your subform will be bound the tblCompany with
Link Master Fields to cboCompany and
LInk Child Fields to CompanyID.
ActivityID in the subform should be converted to combobox with
RowSource to tblActivities.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:52
Joined
Feb 19, 2013
Messages
16,553
for your example, you need three tables - companies and activities as you already have plus a joining table (with a multivalue field this is hidden).

The joining field need two fields, the FK to the companies table and the FK to the activities table. Combine the two fields as a PK or multifield index to prevent the addition of duplicates company/activity combos.

If you want to add activities to a company, have subform on your companies form based on the joining table with the companyFK field hidden and the activity control as a combobox to list activities
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:52
Joined
Feb 28, 2001
Messages
27,001
CJ's advice is good. If you want to find more about this idea, look up "JUNCTION TABLE" on this forum as a way to implement a many-to-many JOIN. You have many companies and many activities, and they are joined in a somewhat amorphous way. I would not doubt that the same companies could be involved in more than one activity. I.e. the "multiple association" links run both ways. Therefore, you need to use the junction table because that is how you do many-to-many in Access.
 

Users who are viewing this thread

Top Bottom