Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Lookup & Relationship functionality in a table

Posted on 2012-04-06
3
365 Views
Last Modified: 2012-04-06
I'm just getting started with Access 2010, I was half-decent with Access 2003 a few years back.

So I am setting up my tables and relationships. I have a junction table "position candidates" that has the M-M relationship of positions and candidates, for a start-up business. So one candidate might be targeted for multiple positions, and each position is going to have multiple candidates to choose from.

When doing the Lookup & Relationship in the table view, I say "I want the lookup field to get the values from another table", but I'm only allowed to choose from tables, and not from queries . . . so this seems limiting to me.

If I remember my lessons from EE from Access 2003, it was to not use this lookup relationships. But I am a long way off from being able to design good forms, I was hoping to get my data going and then turn my attention to the forms.
0
Comment
Question by:Alaska Cowboy
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 125 total points
ID: 37818092
Your look-up values should come from a table with unique values, so I would pull from the candidates table and the position table not the bridge / junction table.
0
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 125 total points
ID: 37818097
I would also set up the position and candidate tables with a unique ID, Name, and description for each. Then in the bridge table you would have the ID of the candidate and the ID of the position.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37818167
jmiller,

what I am trying to do is on the Position-Candidates table, do a lookup to the Position table, but wanted to pull in the department descr on the positions (along with the sort order of departments) (so the drop down is easier to deal with)
- but I suppose that's a bit much in the table design, so I will see how it goes when building a form.

>> Your look-up values should come from a table with unique values
- yes, they are, but I wanted to pull from a query so I could get some sorting based on a parent relationship

>>in the bridge table you would have the ID of the candidate and the ID of the position.
- yes, I have it that way, I'm good on the data model but wrestling with Access 2010, although I was able to jump right in and start doing things.

I think I'll have more questions when it comes to building the forms.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Send Meeting Request from Shared Mailbox 3 58
database opened as read only 10 31
Problem with vba code 4 39
error in For & Next statements 4 23
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question