searching for terms?

[shumate62]

Help the newbie! How is I understand tables and queries but I can't wrap my
head around this simple problem?
I'm using a basic database with name, address etc but I need to sort my
names with tags. I created a "Category" box and was going to have assigned
terms for my tags because some people are just one tag, or two tags or three
tags but I need to be able to search and filter for those tags (for example,
some people are Speakers, some are Guests, one person might be a speaker and
a guest but I want to be able to run a query that just searches for "guests")
should I not lump them in one box with tags?
Do I really have to create 15 different boxs for each tag?

 

[Jeanette Cunningham]

Make a table to hold names - I have called it tblPeople in this example.
Make a table to hold the name of each tag.
Make a table to hold the PersonID and the Tag name.

---------------------------
tblPeople - Primary key - PersonID - auto number
FirstName
LastName
(other fields as needed)

tblTag - Primary key - TagID - text - use the tag name as the primary key

tblPersonTag - Primary key PersonTagID - auto number
PeopleID - foreign key from tbl People - related one person to many tags.
TagID - foreign key from tblTag - related one tag to many people.
------------------------

Make a form to enter people's details.
On the form put a subform (continuous view) based on tblPersonTag
On the subform, put a combo based on tblTag - it will let you choose a tag
If a person has more than one tag, create a new row in the subform for each
tag.


The above is the outline of how to set it up.
Post back with questions as you develop this further.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jerry Whittle]

You create a new table for the tags. Use the primary key data in the first
table as the foriegn key in the Tags table. Something like this:

PersonID Tag
1 Speaker
1 Guest
2 Guest
3 Speaker

Then join the two tables with a query.

 

[shumate62]

clearly I'm insane and useless because what you're saying makes perfect sense
but I'm staring at my little wizard baffled. I have the Person ID and table,
I made a Category ID and table and I made the Person ID the foreign key in
the Category ID. Why is not making sense to me how the form connects. My tiny
brain just can't understand how when I'm doing data entry with a thousand
names I'm filling out the two forms with the two tables. Am I supposed to
enter the Person ID into the specific category box for each form sheet- does
that make any sense?

 

[shumate62]

well those were perfect directions, I did the tables and got all the way up
to created the subform and got a 'type mismatch' box so I must have made an
error some where, I fear for my relationships- even though I did the steps, I
don't know.

 

[John W. Vinson]

clearly I'm insane and useless because what you're saying makes perfect sense
but I'm staring at my little wizard baffled. I have the Person ID and table,
I made a Category ID and table and I made the Person ID the foreign key in
the Category ID. Why is not making sense to me how the form connects. My tiny
brain just can't understand how when I'm doing data entry with a thousand
names I'm filling out the two forms with the two tables. Am I supposed to
enter the Person ID into the specific category box for each form sheet- does
that make any sense?

You have two tables.

You need THREE tables!

The first table is for People:

People
PersonID <primary key>
LastName
FirstName
<other biographical data>

The second table is for Categories:

Categories
CategoryID <primary key>
Category <text>

The *third table* relates the two:

CategoryAssignments
PersonID <link to People>
CategoryID <link to categories>

Your Form could be based on either People (if you want to take a person and
assign them several categories) or on Categories (if you want to start with a
category and assign a bunch of people to it). On the Form you would have a
subform based on CategoryAssignments. If you are using People on the main
form, the subform would have a Combo Box displaying the category text, but
storing the CategoryID; the subform's Master/Child Link Field would be the
PersonID. Reverse the roles of the IDs if your mainform is based on
Categories.

 

[Steve]

Try these tables:

TblTag
TagID
Tag

TblPerson
PersonID
<name, address etc fields>

TblPersonTag
PersonTagID
PersonID
TagID

A query based on all three tables will give you a list of each person and
all tags for each person.

Steve
(e-mail address removed)