A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

redo my design



 
 
Thread Tools Display Modes
  #1  
Old April 17th, 2010, 06:17 PM posted to microsoft.public.access.gettingstarted
shumate62
external usenet poster
 
Posts: 13
Default redo my design

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?
  #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]
  #3  
Old April 17th, 2010, 06:59 PM posted to microsoft.public.access.gettingstarted
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default redo my design

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







"shumate62" wrote in message
...
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?



  #4  
Old April 18th, 2010, 06:04 PM posted to microsoft.public.access.gettingstarted
shumate62
external usenet poster
 
Posts: 13
Default redo my design

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







"shumate62" wrote in message
...
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?



.

  #5  
Old April 18th, 2010, 07:11 PM posted to microsoft.public.access.gettingstarted
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default redo my design

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" wrote in message
...
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







"shumate62" wrote in message
...
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?



.

  #6  
Old April 18th, 2010, 09:15 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default redo my design

On Sun, 18 Apr 2010 10:04:01 -0700, shumate62
wrote:

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?


There IS NO SUCH THING as a many to many relationship in the table relations
window. The only relationships you can see in the window are indeterminate,
one to one, one to many; the first is useless and the second is legitimate but
pretty rare.

A many to many relationship *consists* of two one to many relationships.

Each tblPerson record is related to many tblPersonGroup records - a one to
many relationship. Each tblGroup record is related to many tblPersonGroup
records - another one to many. tblPerson is only related to tblGroup *by way
of these two one to many relationships* - it's indirect; each record in
tblPersonGroup encodes the information that one particular record in tblPerson
is related to one particular record in tblGroup. If there are many connections
between the two tables, each such connection is stored in a different record
in tblPersonGroup.
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.