Thread: redo my design
View Single Post
  #2  
Old April 17th, 2010, 06:55 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default redo my design

On Sat, 17 Apr 2010 10:17:02 -0700, shumate62
wrote:

I'm trying to build a database with minimal working knowledge of Access so
even though I've been through the tutorial it seems way more complicated then
what I need. I have a list of people's name for an annual event and basically
need an address book for mailing labels but I need to be able to sort them
for about 10 different fields (author, speaker, speaker's guest, media,) but
some people belong to more than one group so if I do a print out of labels I
want to be able to select to just print 'media and speakers' etc. was this
still supposed to be two tables joined together?


No... you need three tables, not two.

Each person may play several roles (speaker, author, media); each role may be
filled by several people. You should consider a table of people (PersonID,
lastname, firstname, address, email, etc.); a table of Roles (about ten rows
in it at the moment); and a table of PeoplesRoles, fields PersonID and Role,
with one record for each role that a person plays.

You can fill this in easily by having a Form for people with a subform based
on the PeoplesRoles table; use the PersonID as the master/child link field and
a combo box based on Roles to select the role.

Your mailing list would be based on a query joining all three tables, with a
criterion on the role. You can use "OR" logic on the criteria by selecting
"Speaker" on the first criteria line in the query grid, "Media" in the second
and so on, to get a label printed out whichever role a person plays. Use the
"UNIQUE VALUES" property of the query to print only one label per person.

--

John W. Vinson [MVP]