Inputing multiple values into a linked cell (cross referencing?)

[DotComDV]

Help! I have an Access database, getting ready for use with a website. I have 2 tables set out as follows:

STOCK
(Lots of columns, but the one i have linked is called CAT)

Categories
CatNo
CatName

I have created a one-to-many relationship between CAT in STOCK and CatNo in Categories so i can input a value into the CAT column in the STOCK table corresponding to the CatNo column in the Categories table. But... how do i put more than one value in the CAT column?

Eg. I have a book called 'Help Me'. It comes under the category Alcoholism, but it also falls under the category 'School Material'. I need that book to be listed under BOTH of those categories as this database is being used for a website and i need to be able to get information out of that database. How can i list that book under more than one category?

Any help is appreciated, Shane.

 

[LittleSmudge]

What you have, I think, is a classic MANY-to-MANY relationship between the two tables

Many record in STOCK can link to one particular CATEGORIES

whilst it is possible for a STOCK to link to more than one CATEGORY

The solution is to have a 'Link' table

Delete the STOCK.CAT column

Create a table
tblStockCat
StockRef Joint PrimeKey and also FK pointing to PK in STOCK
CategoriesRef Joint PrimeKey and also FK pointing to PK in CATEGORIES

Then to link the two tables you populate the tblStockCat with the Primary key from the Stock table into StockRef and the PrimaryKey from the Categories table into CategoriesRef

By making the two fields in tblStockCat a joint PrimeKey you ensure that you can never link any record in STOCK to the SAME record in CATEGORIES more than once.



'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[DotComDV]

Thanx for the speedy reply!

Everything make sense now, thanx a lot, but i have one question... what is a FK? You used it in this context:

"StockRef: Joint PrimeKey and also FK pointing to PK in STOCK"

I was a little confused by what was needed to be done here. Other than that minor issue, all is good. Thankyou!

 

[DotComDV]

Ok so i think i have got it all worked out now, just to check though, the new table should be filling up with Primary Keys in each column right?

Eg.

tblStockCat
StockRef - lots of book codes (PK's from STOCK) doubling up (because they belong to more than one category)

CatRef - lots of category numbers (PK's from CATEGORIES) doubling up (because lots of different books are using those keys)

This sound right? I presume it is because it should work no problems, but is this what u were intending to happen?

Cheers, Shane.

 

[LittleSmudge]

FK - Foreign Key

When you link two tables in a 1 to Many Relationship you link the Primary Key in the table at the ONE end to a field of the same data type in the table at the Many End. The field at the many end is refered to as the 'Foreign Key' because it contains 'Key' data values but they are 'key' for a foreign ( different ) table.


EXAMPLE

tblBooks

BookId    BookTitle
   1      "Teach YourSelf Chess"
   2      "Teach Yourself DIY"
   3      "DIY Equipment Review"

tblCategories

CatId     Category
   1      "Teach Yourself"
   2      "DIY"
   3      "Sports/Games"
   4      "Equipment"

tblBookCat

BookRef   CatRef
   1         1
   1         3
   2         1
   2         2
   3         2
   3         4

etc..


'ope-that-'elps,

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[DotComDV]

Thankyou so much, you have cleared everything up for me, i wouldnt have been able to figure that solution without ur help - Cheers!

Shane.