Best way to design for multiple values? (1 Viewer)

[mg55345]

Hi,

I am experiencing some confusion in setting up a many-to-many relational database, and I would greatly appreciate any assistance that anyone may have to offer.

Currently, I have two tables: one which is a last of organizations provided services, and the second table is a list of counties in the state. While many organizations only focus their services on one county, some offer services in multiple counties. The desired end product is a website in which users can search for available service organizations based on the county they have selected.

In Access, what is the best way to link each individual entry in the organizations table to the appropriate counties in which they offer services? For example, I will need to say Organization1 offers services in County1, County2, County43, and Organization 2 offers services in County2 and County62. When a user selects County2, it should return both Organization1 and Organization2… and so on.

What is a more efficient way to store this information than just having multiple columns?

Thank you in advance for your help.

 

[boblarson]

You basically have a junction table which pulls together the information, sort of like this:

tblOrganization
OrganizationID - autonumber (PK)
OrganizationName - Text
Other Org details...

tblCounties
CountyID - Autonumber (PK)
CountyName - Text

tblOrgCounty the Junction table
OrgCountyID - Autonumber (PK)
OrganizationID - Long Integer (FK)
CountyID - Long Integer (FK)

 

[mg55345]

Thanks, Bob, that is very helpful.

In doing so, however, what is the best way to create a form in Access so that novice users can add and delete counties for organizations without having to enter the jointer table to do this?

 

[boblarson]

You can have a form to associate them. In other words you can have a form that has a combo box, for example, that has each organization listed and then another combo box with each county listed and you would just go to a new record and select 1 record from each combo box and if the form is bound to the junction table and each combo box is bound to the applicable field, then you have your method right there.

Oh, and the county selection can actually have this form as a subform on the Organization form which has the Orgs listed.