Need help with a table and then choosing the correct table Relationship

[Mike]

I'm at the beginner level.]

What I am Trying to Do: Create a table of keywords that
describe the subject matter of a document. [For example,
I have several hundred documents to place in the database
(via hyperlinks), and each can be described as being in
1, 2 or even 3 keywords - like "water supply" and
"groundwater". The goal is a Search Form for the staff
to look up documents within specified dates that contain
specified keywords.

My Problem: I can't figure out (I've spent the better
part of my day in the Access XP Help file, a couple of
Access websites, and a book on Office XP) how to set up
the table of keywords so I can reference them (whether it
be 1 or more keywords) with each document in another
table.

What I've Tried: List boxes, Combo Boxes; putting each
keyword in it's own field, then trying to link them via a
One-to-One and One-to-Many Relationship.

It's not coming out right, and I'm sure I'm missing
something easy.

I've gotten some off-line help on a previous posting on
this matter, but the SQL code given is way above my head
at this stage, and the sample data base of keywords sent
is one I can't figure out how to "attach" to the other
table to get what I need.

I'd appreciate any and all advice.

Thanks in advance,
Mike

 

[Tim Ferguson]

My Problem: I can't figure out (I've spent the better
part of my day in the Access XP Help file, a couple of
Access websites, and a book on Office XP) how to set up
the table of keywords so I can reference them (whether it
be 1 or more keywords) with each document in another
table.

This is a perfectly straightforward many-to-many relationship

Documents
=========
DocNumber Autonumber Primary Key
Author Text(4) // prob FK into People table
FilePath Text(64)
FileName Text(64)
CheckedOutBy Text(4) // FK into People table
etc...

Keywords
========
Short Text(8) Primary Key
Long Text(64) Unique Index // or not


IsCodedUnder
============
DocNumber Long Integer FK into Documents
KWText Text(8) FK into Keywords(Short)
MajorOrMinor Boolean

Primary Key (DocNumber, KWText)


Note that you may not want a long description for the Keywords; but the
relationship is still needed if you want to force all keywords to be
members of the set you have created.

As for the UI: there are many ways you can do this. As you have noticed,
you can do listboxes or subforms -- my favourite is a list box and two
command buttons, one for Add and one for Remove, but it does take a litle
bit of VBA to implement, so you might prefer the subform method that can be
done in pure Access.

Hope that helps


Tim F

 

[Bryan Christopher]

I'm at the beginner level.]

What I am Trying to Do: Create a table of keywords that
describe the subject matter of a document. [For example,
I have several hundred documents to place in the database
(via hyperlinks), and each can be described as being in
1, 2 or even 3 keywords - like "water supply" and
"groundwater". The goal is a Search Form for the staff
to look up documents within specified dates that contain
specified keywords.

My Problem: I can't figure out (I've spent the better
part of my day in the Access XP Help file, a couple of
Access websites, and a book on Office XP) how to set up
the table of keywords so I can reference them (whether it
be 1 or more keywords) with each document in another
table.

What I've Tried: List boxes, Combo Boxes; putting each
keyword in it's own field, then trying to link them via a
One-to-One and One-to-Many Relationship.

It's not coming out right, and I'm sure I'm missing
something easy.

I've gotten some off-line help on a previous posting on
this matter, but the SQL code given is way above my head
at this stage, and the sample data base of keywords sent
is one I can't figure out how to "attach" to the other
table to get what I need.

I'd appreciate any and all advice.

Thanks in advance,
Mike

Hi Mike,

What you want is a many to many relationship through the use of a
thrid table, a transaction table...at least that's what I call it.

The tables will look something like this:
PK=Primary key, FK=Foreign key, CK=Composite key

tblDocuments trxDocsKeywords tblKeywords
-DocID (PK) -DocID (FK, CK) -KeywordID (PK)
-DocLocation -KeywordID (FK, CK) -Keyword

The relationships will be set as one-to-many from tblDocuments to
trxDocsKeywords and as one-to-many from tblKeywords to
trxDocsKeywords, essentially creating the many-to-many relationship
you're seeking. Of course, actual table and field names will vary
depending on your technique.

Hope this was helpful...

Bryan

 

[Mike]

Thanks! I'll give it a try.

-----Original Message-----


This is a perfectly straightforward many-to-many relationship

Documents
=========
DocNumber Autonumber Primary Key
Author Text(4) // prob FK into People table
FilePath Text(64)
FileName Text(64)
CheckedOutBy Text(4) // FK into People table
etc...

Keywords
========
Short Text(8) Primary Key
Long Text(64) Unique Index // or not


IsCodedUnder
============
DocNumber Long Integer FK into Documents
KWText Text(8) FK into Keywords(Short)
MajorOrMinor Boolean

Primary Key (DocNumber, KWText)


Note that you may not want a long description for the Keywords; but the
relationship is still needed if you want to force all keywords to be
members of the set you have created.

As for the UI: there are many ways you can do this. As you have noticed,
you can do listboxes or subforms -- my favourite is a list box and two
command buttons, one for Add and one for Remove, but it does take a litle
bit of VBA to implement, so you might prefer the subform method that can be
done in pure Access.

Hope that helps


Tim F

.

 

[Mike]

Thanks! I'm on it now.

-----Original Message-----
"Mike" <[email protected]> wrote in message

I'm at the beginner level.]

What I am Trying to Do: Create a table of keywords that
describe the subject matter of a document. [For example,
I have several hundred documents to place in the database
(via hyperlinks), and each can be described as being in
1, 2 or even 3 keywords - like "water supply" and
"groundwater". The goal is a Search Form for the staff
to look up documents within specified dates that contain
specified keywords.

My Problem: I can't figure out (I've spent the better
part of my day in the Access XP Help file, a couple of
Access websites, and a book on Office XP) how to set up
the table of keywords so I can reference them (whether it
be 1 or more keywords) with each document in another
table.

What I've Tried: List boxes, Combo Boxes; putting each
keyword in it's own field, then trying to link them via a
One-to-One and One-to-Many Relationship.

It's not coming out right, and I'm sure I'm missing
something easy.

I've gotten some off-line help on a previous posting on
this matter, but the SQL code given is way above my head
at this stage, and the sample data base of keywords sent
is one I can't figure out how to "attach" to the other
table to get what I need.

I'd appreciate any and all advice.

Thanks in advance,
Mike

Hi Mike,

What you want is a many to many relationship through the use of a
thrid table, a transaction table...at least that's what I call it.

The tables will look something like this:
PK=Primary key, FK=Foreign key, CK=Composite key

tblDocuments trxDocsKeywords tblKeywords
-DocID (PK) -DocID (FK, CK) - KeywordID (PK)
-DocLocation -KeywordID (FK, CK) - Keyword

The relationships will be set as one-to-many from tblDocuments to
trxDocsKeywords and as one-to-many from tblKeywords to
trxDocsKeywords, essentially creating the many-to-many relationship
you're seeking. Of course, actual table and field names will vary
depending on your technique.

Hope this was helpful...

Bryan
.