Not understanding M:M relationship

I have tblCategories, which was related 1:M to tblSubcategories. So, for
example:

- Category 1 had Subcategories A, B, and C
- Category 2 had Subcategories D, E, and F.

However, the tables have been updated, and now need to reflect a M:M
relationship:

- Category 1 has Subcategories A, B, and C
- Category 2 has Subcategories B, D and F
- Category 3 has Subcategories C, E and G.

I read a number of posts on M:M relationships and junction tables and
whatnot, but I'm just not getting it. Where do I record all the other
categories a subcategory relates to past the first one? I.e. the
Subcategories table looks like this:

SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1
2 Subcat 2 2
3 Subcat 3 3

but what I need is:


SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1, 2, 3
2 Subcat 2 2, 4, 6
3 Subcat 3 3, 5, 7

and I can't add more than one CategoryID to each field. I don't understand
how a junction table would fix that, either.

Help?

You need three tables:

Categories table
CategoryID (primary key)
CategoryName

Subcategories table
SubcategoryID (primary key)
SubcategoryName

Cat2SubCat table
CategoryID (composite primary key with SubcategoryID) - (foreign key to
Categories)
SubcategoryID (composite primary key with CategoryID) - (foreign key to
Subcategories)


The Cat2SubCat table is the junction table that gives you the M:M
relationship.
--

Ken Snell
MS ACCESS MVP


"MCB" wrote in message
news
I have tblCategories, which was related 1:M to tblSubcategories. So, for
example:

- Category 1 had Subcategories A, B, and C
- Category 2 had Subcategories D, E, and F.

However, the tables have been updated, and now need to reflect a M:M
relationship:

- Category 1 has Subcategories A, B, and C
- Category 2 has Subcategories B, D and F
- Category 3 has Subcategories C, E and G.

I read a number of posts on M:M relationships and junction tables and
whatnot, but I'm just not getting it. Where do I record all the other
categories a subcategory relates to past the first one? I.e. the
Subcategories table looks like this:

SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1
2 Subcat 2 2
3 Subcat 3 3

but what I need is:


SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1, 2, 3
2 Subcat 2 2, 4, 6
3 Subcat 3 3, 5, 7

and I can't add more than one CategoryID to each field. I don't understand
how a junction table would fix that, either.

Help?

A junction table solves it by creating a separate record for each combination
of category and subcategory. So the structures you need a

Categories
------------
CategoryID
CategoryName

SubCategories
-----------------
SubCategoryID
SubCategoryName

JunctionTable (call it what you wish)
------------------------------------------
ID AutoNumber
CategoryID Number (Foreign Key to Categories)
SubcategoryID Number (Foreign Key to Subcategories)

So your records will look like:

[ID], 1, A
[ID], 1, B
[ID], 1, C
[ID], 2, B
[ID], 2, D
[ID], 2, F
[ID], 3, C
[ID], 3, E
[ID], 3, G

Hope that solves it.
Sprinks

"MCB" wrote:

I have tblCategories, which was related 1:M to tblSubcategories. So, for
example:

- Category 1 had Subcategories A, B, and C
- Category 2 had Subcategories D, E, and F.

However, the tables have been updated, and now need to reflect a M:M
relationship:

- Category 1 has Subcategories A, B, and C
- Category 2 has Subcategories B, D and F
- Category 3 has Subcategories C, E and G.

I read a number of posts on M:M relationships and junction tables and
whatnot, but I'm just not getting it. Where do I record all the other
categories a subcategory relates to past the first one? I.e. the
Subcategories table looks like this:

SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1
2 Subcat 2 2
3 Subcat 3 3

but what I need is:


SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1, 2, 3
2 Subcat 2 2, 4, 6
3 Subcat 3 3, 5, 7

and I can't add more than one CategoryID to each field. I don't understand
how a junction table would fix that, either.

Help?

MCB wrote:
I don't understand
how a junction table would fix that

If you can read SQL code, you may find this interesting:

http://www.intelligententerprise.com/010101/celko.jhtml

And if you know how to get an ADO connection to your Jet database, you
can even try out the examples. You will need to use the same data type
throughout (e.g. VARCHAR(30) in place of INTEGER) and use the longer
CONSTRAINT syntax rather than the REFERENCES contraction e.g. the first
example relationship table would be more like:

CREATE TABLE Pairs (
boy_name VARCHAR(30) NOT NULL,
girl_name VARCHAR(30) NOT NULL,
CONSTRAINT fk__Pairs__Boys FOREIGN KEY (boy_name) REFERENCES Boys
(boy_name),
CONSTRAINT fk__Pairs__Girls FOREIGN KEY (girl_name) REFERENCES
Girls (girl_name)
);

Jamie.

--

Ahh, okay. I was under the impression that it was do-able without any kind of
duplication or replication. Makes sense now. Thanks!


"Sprinks" wrote:

A junction table solves it by creating a separate record for each combination
of category and subcategory. So the structures you need a

Categories
------------
CategoryID
CategoryName

SubCategories
-----------------
SubCategoryID
SubCategoryName

JunctionTable (call it what you wish)
------------------------------------------
ID AutoNumber
CategoryID Number (Foreign Key to Categories)
SubcategoryID Number (Foreign Key to Subcategories)

So your records will look like:

[ID], 1, A
[ID], 1, B
[ID], 1, C
[ID], 2, B
[ID], 2, D
[ID], 2, F
[ID], 3, C
[ID], 3, E
[ID], 3, G

Hope that solves it.
Sprinks

"MCB" wrote:

I have tblCategories, which was related 1:M to tblSubcategories. So, for
example:

- Category 1 had Subcategories A, B, and C
- Category 2 had Subcategories D, E, and F.

However, the tables have been updated, and now need to reflect a M:M
relationship:

- Category 1 has Subcategories A, B, and C
- Category 2 has Subcategories B, D and F
- Category 3 has Subcategories C, E and G.

I read a number of posts on M:M relationships and junction tables and
whatnot, but I'm just not getting it. Where do I record all the other
categories a subcategory relates to past the first one? I.e. the
Subcategories table looks like this:

SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1
2 Subcat 2 2
3 Subcat 3 3

but what I need is:


SubcategoryID (PK) SubcategoryName CategoryID (FK)
--------------------------------------------------------------
1 Subcat 1 1, 2, 3
2 Subcat 2 2, 4, 6
3 Subcat 3 3, 5, 7

and I can't add more than one CategoryID to each field. I don't understand
how a junction table would fix that, either.

Help?