Parameter Query, Multiple fields

  • Thread starter Thread starter tR
  • Start date Start date
T

tR

I have a table "Notes", in which each note can have three
differenct topics (Topic1, Topic2, Topic3)- each of these
fields are based on the same table (Topics). When I do
a general query, all three fields show up. How can I
get a parameter query to search all three fields (i.e.
list all the notes that have the topic "cures" in one of
the three fields).

tR
 
TR,

The essential answer to your question is that you shouldn't have 3
Topic fields. This data should be in a separate linked table, with
one Topic field, and another field to identify the Note each record is
related to. So my first recommendation would be to correct your table
design... Is that a possibility?

Otherwise, as a workaround you can make a calculated field in the
query, like this...
AllTopics: [Topic1] & "|" & [Topic2] & "|" & [Topic3]
.... and then put your parameter criteria in this column, i.e.
Like "*" & [what word are you looking for] & "*"

- Steve Schapel, Microsoft Access MVP
 
Thank you for answering my question. I would like to
clarify. Each of my notes can belong to more than one
topic. As an example, note No 1 could be
either "Cures", "Doctors" and/or "Beliefs". If I only
have one Topic field in the table (as you suggest), will
I have to repeat the note three times to list it under
all the different topics?

tR
 
TR,

I am not sure what the meaning of the "Note" is, but I assume there
are other pieces of information about the Note in addition to the
Topics it belongs to? Just guessing, maybe a DateWritten, maybe an
Author, whatever.

It is normal database design practice to assign an ID number of some
sort to each note, for which an Autonumber will most likely be
suitable. And then in the Notes table, you put all the fields that
have a *one-to-one* relationship with each note. Then, in the table
that you use for entities that have a one-to-many relationship with
each Note, as is the case with the Topics in your example, these go in
another table, and use the ID field from the Notes table to identify
the Note that the Topic refers to. So, for example, this part of the
database might look like this...

Table: Notes
NoteID
Note
DateWritten
Author

Table: Topics
TopicAssignmentID
Topic
NoteID

Then, whenever you need to use the Topic information for the Notes,
you make a query that joins these two tables.

As regards entry of this data, the basic method here would be to use a
subform based on the Topics table on the main Notes form, and possibly
a combobox for the actual topics choices.

It may seem a little cumbersome to do it this way, especially if you
are not used to thinking in this way, but this is actualy a very basic
and essential concept of relational database design, and in fact is
the basis of what gives a database its power. The way you have done
it, with separate columns for each topic, is the kind of thing that
might be applicable in a spreadsheet or some such, but really not
applicable in database.

- Steve Schapel, Microsoft Access MVP
 
Back
Top