Showing related value in form's field value

  • Thread starter Thread starter rgrantz
  • Start date Start date
R

rgrantz

Okay, I have a problem performing the following:

In DB design, I've always assumed it's better to assign numeric values to
things like names, textual descriptions, etc., so I make several tables that
hold these values.

Example: Table "EmpNames" has 2 fields, "EmployeeNumber" and
"EmployeeName." I use comboboxes on forms for the data entry people to
choose the actual text name, but have the numeric value associated with it
go into the field.

Now, however, for some reason I can't seem to pull the associated name text
from the field when viewing data or running queries. I realize that I can
use two fields in the data-entry table (both name and number), but it seems
I should be able to show reports and query results that return the
associated text based on the numeric value that the table actually contains.

I've tried DLOOKUP and a few other things, and was hoping I could get some
quick help on this while I tackle a couple other VBA issues.

Is this where a SUB query kind of thing should be used?

I use the query and expression builders, by the way, so if this can be
presented as an expression to put in the Criteria field or some such thing I
would appreciate it.

Thanks for the help
 
Create a query that includes both the main table and the lookup table. You
can then choose the text field from the lookup table to display in the
query.

One trap with that approach is if there are records that have no lookup
value (e.g. they don't have any category). In that case, the default join
means that the query does not show them. To fix that, double-click the line
joining the 2 tables in the upper pane of query design. Access pops up a
dialog offering 3 options. Choose the one that says:
All records from MyMainTable, and any matches from...

With entities like employees, it is best to use an EmployeeID autonumber,
because it takes many fields to uniquely identify an employee (e.g. you can
even have 2 employees with the same name and address - father and son).
However with general categories it is handy sometimes to use the text field
as the primary key. The down-side of this approach is that you have to
remember that it's a text type and use extra quotes as delimiters when
looking up the field. But a strong benefit it that it overcomes the
limitation of Access combos that can't show anything at all if you hide the
bound column and have to filter out some records (e.g. inactive categories).
 
Back
Top