Prev: Creating a "Greater Than Date" criterion
Next: New User- Is this possible in excel or access?
From: shumate62 on 17 Apr 2010 13:17
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?
From: John W. Vinson on 17 Apr 2010 13:55
On Sat, 17 Apr 2010 10:17:02 -0700, shumate62
<shumate62(a)discussions.microsoft.com> 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]
From: Steve on 17 Apr 2010 13:59
You can do this with three tables ....
TblPerson
PersonID
<name and address fields>

TblGroup
GroupID
Group (author, speaker, speaker's guest, media, etc)

TblPersonGroup
PersonGroupID
GroupID
PersonID

TblPersonGroup gives you a list of persons in each group.

To print your labels, create a query that includes TblPerson and
TblPersonGroup. Include the name and address fields from TblPerson and
GroupID from TblPersonGroup. Set the recordsource for the labels to this
query. You can set the criteria of GroupID for the Group or groups you want
to print.

Steve
santus(a)penn.com






"shumate62" <shumate62(a)discussions.microsoft.com> wrote in message
news:6699807A-6DDE-49DD-A8E0-3034E816F08D(a)microsoft.com...
> 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?


From: shumate62 on 18 Apr 2010 13:04
So, I made the three tables as suggested, tblPerson, Tbl PersonGroup,
TblGroup and then tried to create the relationship for them but it's stuck on
only having a one to many relationship, which of course it isn't it's a many
to many (there are multiple authors, and people belong to more than one
group) did I do something wrong in the formatting of the tables that blocked
it from being a many to many?

"Steve" wrote:

> You can do this with three tables ....
> TblPerson
> PersonID
> <name and address fields>
>
> TblGroup
> GroupID
> Group (author, speaker, speaker's guest, media, etc)
>
> TblPersonGroup
> PersonGroupID
> GroupID
> PersonID
>
> TblPersonGroup gives you a list of persons in each group.
>
> To print your labels, create a query that includes TblPerson and
> TblPersonGroup. Include the name and address fields from TblPerson and
> GroupID from TblPersonGroup. Set the recordsource for the labels to this
> query. You can set the criteria of GroupID for the Group or groups you want
> to print.
>
> Steve
> santus(a)penn.com
>
>
>
>
>
>
> "shumate62" <shumate62(a)discussions.microsoft.com> wrote in message
> news:6699807A-6DDE-49DD-A8E0-3034E816F08D(a)microsoft.com...
> > 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?
>
>
> .
>
From: Duane Hookom on 18 Apr 2010 14:11
Why not take a couple seconds to explain which tables/fields you attempted
to create the relationships on? The person and group tables should each be
joined one-to-many to the Person Group table.

I hope the space in "Tbl PersonGroup" is just a typo. Avoid all spaces in
object names.

--
Duane Hookom
MS Access MVP


"shumate62" <shumate62(a)discussions.microsoft.com> wrote in message
news:00855B6E-9B77-4891-8CE5-D7ED6B145D0A(a)microsoft.com...
> So, I made the three tables as suggested, tblPerson, Tbl PersonGroup,
> TblGroup and then tried to create the relationship for them but it's stuck
> on
> only having a one to many relationship, which of course it isn't it's a
> many
> to many (there are multiple authors, and people belong to more than one
> group) did I do something wrong in the formatting of the tables that
> blocked
> it from being a many to many?
>
> "Steve" wrote:
>
>> You can do this with three tables ....
>> TblPerson
>> PersonID
>> <name and address fields>
>>
>> TblGroup
>> GroupID
>> Group (author, speaker, speaker's guest, media, etc)
>>
>> TblPersonGroup
>> PersonGroupID
>> GroupID
>> PersonID
>>
>> TblPersonGroup gives you a list of persons in each group.
>>
>> To print your labels, create a query that includes TblPerson and
>> TblPersonGroup. Include the name and address fields from TblPerson and
>> GroupID from TblPersonGroup. Set the recordsource for the labels to this
>> query. You can set the criteria of GroupID for the Group or groups you
>> want
>> to print.
>>
>> Steve
>> santus(a)penn.com
>>
>>
>>
>>
>>
>>
>> "shumate62" <shumate62(a)discussions.microsoft.com> wrote in message
>> news:6699807A-6DDE-49DD-A8E0-3034E816F08D(a)microsoft.com...
>> > 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?
>>
>>
>> .
>>
 |  Next  |  Last
Pages: 1 2
Prev: Creating a "Greater Than Date" criterion
Next: New User- Is this possible in excel or access?