Relationships and a form - Am I doing this the right way?

Hi, all.

Using Access 2K. I think what I'm trying to do is quite simple, but
I've been out of the loop for a few years so I've forgotten much.

I have a table of records each of which can belong to multiple
categories. I have a table of all the categories available.

I have a crossreference table which links records to categories.

What I want to do is have a form for the records, with an unlinked
subform which displays _all_ the categories, with a check mark next to
the ones which have a crossreference record. If a checkbox is checked,
a new crossreference records is created. If a checkbox is cleared, the
crossreference record is deleted.

I'm not entirely sure that the way I'm planning to do it is
particularly efficient anyway, but at some point I want to enable
"triple state" for the checkbox, the "grayed out" state indicating
where the category was added after the record, so the system "doesn't
know" if the record belongs to the category.

Does anyone have any comments or examples of how I can better do this,
assuming it's not already being done appropriately.

re: Relationships and a form - Am I doing this the right way?

Assuming you are refering to gramaphone records as opposed to records in a
table, the normal way of doing it is to have a table of rRecords, a table of
Categories abd a JoinRecordCategory table consisting of RecordID and
CategoryID as joint keys.

You then have a Record form and a continuous subform based on the
JoinRecordCategory table and linked to the main form on the RecordID

The subform has on it a combo box for selecting the category and the
ControlSource will be JoinRecordCategory!CategoryID

HTH

Phil



"Gorf" <g.p.hardy@gmail.comwrote in message
news:1165331073.637356.221820@j72g2000cwa.googlegr oups.com...

re: Relationships and a form - Am I doing this the right way?

Assuming you are refering to gramaphone records as opposed to records in aThanks for your reply, Phil. I'm sorry it's taken so long to get back
to you.

It's not a gramophone database, that's my fault for using archaic terms
such as "files", "fields" and "records" when I should be using
"tables", "columns" and "rows". It just shows you how far out of touch
I've got!

Anyway, it's not a bad analogy so I'll stick with it. Let's say I have
categories of "Rock", "Punk", "Pop" etc. On frmRecords I want a
continuous subform of categories where all of them are displayed as
text, and only the relevant ones are checked (so a song can be both
"Pop" and "Rock" for example). Where a category is added _after_ the
song, the triplestate of the checkbox is enabled so that it's plain to
see where songs need attention because new categories may apply.

I think I'll have to do it by populating the xref table with all
current categories when the song is added, then deal with the absence
of a category at a later stage.

If it sounds a bit ridiculous, it's because it's actually a FAQ
database, and we certainly will be adding categories as the business
moves into new areas, but some existing FAQs may well apply to new
categories. I'd like an easy way of seeing which FAQs predate the new
categories.