Using many-to-many junction tables

[twogunz]

I would like to create a database of organizations in my area that provide particular services. I had initially created an Excel spreadsheet, but decided that I'll probably need an Access database, so I converted the format.

I have one table that has the name of the organization, contact information, hours, and other info. In the original Excel spreadsheet, I used strings in two other columns for "languages" and "areas served". Some organizations offer services in more than one language and some operate in more than one geographical area. I've read posts, some books, and info on other sites and it looks like I'll need to use two many-to-many junction tables (one for each of the two remaining columns described above).

So, what I have done so far, is this:

Table: "Resources"
ResourcesID (AutoNumber)
Organization/Program (Memo)
Address (Memo)
...

Table: "Areas"
AreasID (AutoNumber)
Description (Text)

Table: "Languages"
LanguagesID (AutoNumber)
Description (Text)

JUNCTION Tables:

Table: Resources_Areas
Resources_AreasID (AutoNumber, PK)
ResourcesID (Number)
AreasID (Number)

Table: Resources_Languages
Resources_LanguagesID (AutoNumber, PK)
ResourcesID (Number)
LanguagesID (Number)



I have several questions, and I hope that I'm asking the right ones.

First, my junction tables, when I initially set them up have no information in them when I just open them (when I don't use the Design view) -- they just show the column headings. Is this right?

Also, when setting up the relationships, do I need to check the "Enforce Referential Integrity" box? And if so, it seems like I need to change the "Indexed" property for the PK's of all three of the main tables (not the junction tables) to "Yes (No Duplicates)" -- right?

My main problem is that I don't understand now how to assign multiple languages or multiple areas served to each record in the Resources table. Originally, as I explained above, I had these as strings in a particular cell in Excel (like, "English, Spanish, French"). Will I be deleting those two columns from the Resources table?

I can provide what I've got so far if that would help... and I would greatly appreciate any assistance (explanation, link to good tutorial, example databases, etc.). Thank you so much,

Matt

 

[Len Boorman]

Many to Many relationships are always decomposed into two 1 to Many relationships

Take the primary key from the participating tables and put these into the linking table. There them become the primary key in the new table (Composite key). They are also the Foreign keys to which the particupating tables link

Example

Table 1 Organisation (PK Org ID)
Table 2 Language (PK Language ID)

Many to Many

New table Table 3 Org_Language
PK Org ID and Language ID

Link Table 1 PK to Table 3 OrgID (1 to Many)
Link Table 2 PK to Table 3 Language ID (1 to Many)

This then enables you to link 1 or more languages to a specific Organisation.

Same principle with Areas

Also gives you opportunity to indicate say Language/Organisation info, could be preferred or competancy level

Enforce referential integrity

You should also adopt a name convention

I use tbl_ for tables, qry_ for queries etc

Do not have spaces in filed names. I use and underscore, others just have no spaces. Whatever you do is fine but have a convention

len

 

[twogunz]

Thank you for your help!

I guess my question is really more of a "what next?"

After I've established those relationships (created junction tables with foreign keys), what do I do?

I still have my "Resources" table with a column for Languages where each cell has a string... how do I change that so that I can have multiple selections (English, Spanish, etc.) for any individual record?

Sorry if this doesn't make sense,

Matt

 

[twogunz]

Thank you so much! I will try that...