I want to use a lookup field to populate other fields in a record

[Guest]

Table 1 is a list of Raw Materials and their properties.

Table 2 is a product built from those raw materials. I want to pick from a
list the name of the raw material from Table 1 AND have its properties fill
matching fields in Table 2.

The lookup wizard does only 1 field, not multiple. I am relatively new to
programming is Access and was wondering is there's an easy way to do this.

 

[Duane Hookom]

Is there a good reason to store the same information when it can be derived
from Table 1 in a query?

 

[Vincent Johns]

What I'm about to say (except for the Access details) applies to just
about any relational database system, not just Access.

Before making substantial changes, back up your database file.

If you want to create (just once) a new version of [Table 1] that has
some of the same fields as [Table 1] and some new ones, you might just
make a copy (including structure plus data), erase the fields you don't
need, and add new fields to describe the product. Then, since you won't
need [Table 1] any more, delete it.

If you want your products to contain references to raw materials, then
follow Duane Hookom's suggestion -- use a Query.

If you do use 2 Tables (and I recommend giving them suggestive names,
like [RawMaterials]), then a good use for a lookup query would be to
hide the hard-to-read foreign key values in your table. For example,
suppose your Tables look like this:

[RawMaterials] Table:

RawMaterialsID Material Name Supplier Name
-------------- ------------------ -----------------------
-1728209000 Oddballium Acme Widgets
-1342726727 Trinitrotoluene Fun House
-1219592561 Sildenafil citrate Growth Industries, Inc.
-142938234 Gunk Hall-Mart

[Products] Table, without lookup query:

ProductsID Product Name RawMaterialsID
----------- ------------ -----------------------
-1477909648 Power Pills -1342726727
-643542030 Magic Pills -1219592561
1816566449 Greasy Pills -142938234

The foreign key, [RawMaterialsID], is correct but hard to read.



I might at this point define a lookup query something like this, listing
abbreviated supplier names and full material names:

[Q_LookupRawMaterials] Query:

SELECT RawMaterials.RawMaterialsID,
Left$([RawMaterials]![Supplier Name],5)
& ": " & [RawMaterials]![Material Name]
AS Lookup
FROM RawMaterials
ORDER BY RawMaterials.[Material Name];

This will produce records looking like this, in which the 1st field
contains the foreign key and the 2nd field contains something meaningful:

[Q_LookupRawMaterials] Query, in Datasheet View:

RawMaterialsID Lookup
-------------- -------------------------
-142938234 Hall-: Gunk
-1728209000 Acme : Oddballium
-1219592561 Growt: Sildenafil citrate
-1342726727 Fun H: Trinitrotoluene

You might wish to choose some other version of the [Lookup] field, but
you should try to make it easy to understand.

Having defined [Q_LookupRawMaterials], you can open [Products], using
Table Design View. Select the [RawMaterialsID] field and open the
"Lookup" tab. Change its properties as follows:

Display Control: List Box
Row Source: Q_LookupRawMaterials
Column Count: 2
Column Widths: 0;1

Save the Table and open it in Datasheet View, and it will appear with
meaningful labels instead of those hard-to-read foreign key values:

[Products] Table, with lookup query:

ProductsID Product Name RawMaterialsID
----------- ------------ -----------------------
-1477909648 Power Pills Fun H: Trinitrotoluene
-643542030 Magic Pills Growt: Sildenafil citrate
1816566449 Greasy Pills Hall-: Gunk

This technique works well only for foreign keys such as
[RawMaterialsID]. Most of the time I just hide each Table's primary
key, such as the [ProductsID] field, which is an Autonumber type in the
[Products] Table. It needs to be there, but it doesn't normally mean
much other than to identify each record, especially if you use
Autonumber values, as I did here. Human beings' jobs aren't made much
easier by having to read primary key values if they don't mean anything
else. In Table Datasheet view, you can hide a primary key column by
right-clicking at the top of the column and selecting "Hide Columns".

Note: If you want to list more than one [RawMaterials] item for any one
[Products] item, you'll likely need an additional Table, in which each
record will identify some raw material used in some product. In that
case, each record would contain foreign keys [ProductsID] and
[RawMaterialsID] and possibly other information. In that case I would
recommend using lookup queries for both foreign keys.


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Is there a good reason to store the same information when it can be derived
from Table 1 in a query?

-- Duane Hookom MS Access MVP

"Richard Bates" <Richard (e-mail address removed)> wrote in

message news:[email protected]...