Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Lookup & Relationship functionality in a table

Posted on 2012-04-06
3
Medium Priority
?
369 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

609 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