MS Access is a relational database and well suited for this sort of task as
what you are describing is a many-to-may relationship between Notes and Tags.
In a relational database this would be modelled by having a Notes table, a
Tags table and a third NoteTags table to model the relationship between them.
The Notes table would have a NoteID as its primary key; this can be an
autonumber column. It would also have a Notes memo field into which the
text for each note is entered.
The Tags table really just need the one field Tag, of text data type, and
set as the table's primary key.
To relate each note to multiple tags and vice versa the NoteTags table just
needs two fields NoteID, a straightforward long integer number data type in
this case, not an autonumber, and a text Tag field. The two columns would be
made the table's composite primary key.
For data entry you'd create a form, in single form view, based on the Notes
table with a large text box to enter the text into. This form would have
within it a subform based on the NoteTags table, with the subform linked to
the main form on NoteID. The subform would be in continuous form view and
would have just one control, a combo box bound to the tag field. This would
have a RowSource which lists the tags alphabetically:
SELECT Tag FROM Tags ORDER BY Tag;
To select tags for the note you've entered you simply create however many
records are necessary in the subform by selecting a Tag value from the combo
box's list for each record you create. You can even set it up to add a new
tag into the tags table by typing it into the combo box if its not already in
the list (we can go into that in more details later if you wish).
To interrogate the database you can create another unbound dialogue form,
i.e. one not based on any table. This could have a combo box of the tag
values so that when you select one it would allow you to open your bound form
at those notes which are 'tagged' with the value you've selected; you could
also print a report of the relevant notes if you wish from the same dialogue
form.
Rather than having a combo box on the dialogue form, another option would be
to have a multiselect list box, which would enable you to select one or more
tags from the list and output the notes which match any of those tags. You
could even design it to give you the choice of finding those notes which
match all the selected tags, or those which match any of them; i.e. a Boolean
AND or OR operation in terms of the underlying logic.
If you decide to head down this road I'd concentrate on getting the tables
and main form/subform set up first. We can then come back to adding the
other functionality.
Ken Sheridan
Stafford, England
