On Sun, 18 Apr 2010 11:12:01 -0700, shumate62
wrote:
Let me explain... I need a database with contact names and then which type of
group they are part of.
1. I created a table with ContactID (fields with name, address)
Good...
2. I created a table with GroupID (fields with speaker, author, media)
Good...
3. I selected lookup wizard as the data type in ContactID and then select
that it choose from GroupID options (speakers, author, media)
WRONG!!!!
See
http://www.mvps.org.access/lookupfields.htm for a critique of this
misfeature.
4. that automatically created a one to many relationship from group ID to
contact ID.
Exactly. The lookup wizard isn't very bright. There *IS NO* relationship
(directly) from Groups to Contacts. *There can't be*. A field can (should,
multivalue fields being a really bad decision on Microsoft's part) have *only
one value*.
And to clarify, is this the correct design to allow me to sort for tasks
such as:
print labels for speakers and authors?
or creating lists of only attendees and the media
by doing it this way I will be able to filter for that, no?
Yes. Two one-to-many relationships; tblPerson to tblPersonGroup, tblGroup to
tblPersonGroup.
If Jane Smith is a Speaker, there'd be a record for Jane in tblPerson; a
record for Speakers in tblGroups; and a record with Jane's ID and the Speakers
GroupID in tblPersonGroups.
The user presentation of this could be a Form based on tblPerson; a Subform
based on tblPersonGroup; on the subform you could have a combo box bound to
tblPersonGroup.GroupID, storing the GroupID and displaying the group name.
--
John W. Vinson [MVP]