Solved

Lookup & Relationship functionality in a table

Posted on 2012-04-06
3
364 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

810 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