lookup
in [Getting Started]
|
Prev: New User- Is this possible in excel or access?
Next: how do I attach a value to a check box in access 2007?
From: shumate62 on 18 Apr 2010 14:12 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) 2. I created a table with GroupID (fields with speaker, author, media) 3. I selected lookup wizard as the data type in ContactID and then select that it choose from GroupID options (speakers, author, media) 4. that automatically created a one to many relationship from group ID to contact ID. 5. I created a form through wizard. 6. but when I go to enter data, nothing comes up in the dropdown box. where did I go wrong? 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?
From: John W. Vinson on 18 Apr 2010 16:20
On Sun, 18 Apr 2010 11:12:01 -0700, shumate62 <shumate62(a)discussions.microsoft.com> 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] |