Solved

Lookup & Relationship functionality in a table

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Explanation of Access VBA code 2 36
Auto Filter in Combo Box 7 33
Error in query expression 3 37
MS-Access 2002 error (Win XP on Win7Pro) 19 49
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now