Table/Form level lookups (1 Viewer)

WhatEvil

New member
Local time
Today, 05:44
Joined
Aug 28, 2013
Messages
3
Hi there,

I've recently started working on some databases in Access 2010 for the company I work for. I've never done any database work before but I've read most of Access 2007 for Dummies and I've been doing a fair amount of reading on various websites of Access MVPs.

I'm pretty sure I've got to grips with the relational database design principles, but I need a little clarification on how best to deal with Lookup fields.

I've read that you shouldn't use Table level lookups in a few places. I've managed to get some Form level lookups working, however I just wanted to check a couple of things about my implementation.

My Lookup field at table level is a Number field (long integer), and then at form level I have a ComboBox with the following settings:
Row Source: #SELECT TimbSectionType.TimbSectionTypeID, TimbSectionType.TimbSectionType FROM TimbSectionType ORDER BY TimbSectionTypeID;
Bound Column: 1
Limit to list: Yes
Column count: 2
Column widths: 0cm,2cm

This all seems to work correctly in that the form combobox displays the timber section name, and the ID of that field is stored in the underlying table. What's confused me a little is that at table level if you go to design view, select one of these number fields (which stores an FK referring to another table), in the "Field Properties" section at the bottom of the screen there are two tabs, General and Lookup. If you go to Lookup then you can change Display Control from Text Box (the default) to Combo Box, which then gives you Row Source etc. options. Should I ever use these, or is setting it up from there essentially the same as the result from using the Lookup Wizard to create a table level lookup (ie. is this bad)?

Additionally, if I wanted a datasheet/table view which shows the looked up value eg. "Pine" rather than the FK ID (eg. 3), do I have to go about this with a separate query to create a new table, or do I need to output it as a report, or is there some other way to set this up (eg. with the Lookup>ComboBox settings I just mentioned)?

Hopefully this question is clear, but if you need clarification then I can elaborate or maybe upload some of my files.

Many thanks.

WhatEvil
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 19, 2002
Messages
42,981
Welcome aboard:)

I would change the Order By to sort by the Type field rather than the ID field. You want the list sorted alphabetically by the visible field. Sorting by ID will confuse the user since the list won't be in alphabetical order (except accidentally).

Just ignore the lookups on tables. Stick with lookups on forms. You never expose users to tables or queries so you are the only one inconvenienced by not being able to see the text value. Users always ONLY work with forms. Datasheet view looks just like a table or query except that you have control because forms support events.

If you want to see the text value in a query (you will always want to do this for reports because you don't want them to have comboboxes), just join to the lookup table and select the text field. You will need to use a Left Join rather than an Inner Join if the lookup field is optional.
 

Users who are viewing this thread

Top Bottom