?
Solved

Lookup & Relationship functionality in a table

Posted on 2012-04-06
3
Medium Priority
?
368 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
[X]
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
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 500 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 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

764 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