Problems with Access Look-Up fields

Question

I have set up 3 tables: Brands, Products, Consumption.

The first is straightforward as it has only one field apart from the ID.

The Products table has several fields covering product and nutritional data.  The important fields are a look-up field for the brand and a text field for the product description. I am using a look-up field for the brand to avoid complications in misspelling and other duplications which can affect subsequent analysis.

The Consumption table needs to identify the brand/product consumed and needs to avoid confusion where similar products are available under more than one brand. For example, two supermarkets could each sell pork sausages but with different nutritional compositions. It is important that the correct brand and product are identified for subsequent analysis.

If I use a look up field for the brand and for the product itself in the Consumption table I can still end up with the wrong brand being attributed to the product and this will result in false data in nutritional comparisons. I have therefore attempted to create calculated fields in the Products which concatenate the brand and the product description and wish to use these in the Consumption table. I am however coming up with various problems in this:

1. the resultant concatenated description contains the ID number for the brand and the product description
2. in attempting to put in the column numbers for the look up fields in the expression I am told that this cannot be done in a calculated field
3. I get type error messages for the output

What is particularly irritating is that in an earlier version of these databases I managed to obtain a result which gave the brand and product description in text format but in trying to repeat this in a updated version of the tables I seem to have run into errors.

I would appreciate some advice as to how I overcome these difficulties and create a table in which I can clearly identify the brand and product which is being consumed.

Answer 1

This is a modelling issue, and there are two possible models.  If each brand can encompass multiple products, and each product can be encompassed by more than one brand, there is a relationship type between brands and products, which must be modelled by a table with two foreign keys, referencing the primary keys of the brands and products tables respectively.  The primary key of this table is a composite of the two foreign keys.  Consequently the foreign key in the consumption table would be a composite of two columns, again referencing the primary keys of the brands and products tables.  For an example take a look at Relationships.zip in my public databases folder at:

https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAAB3aLXYo7DdARg01KnQIyoLg?e=fASl3m

This little demo file uses a simplified medical prescriptions database, whose model is:

![Image](https://learn-attachment.microsoft.com/api/attachments/e90afc78-5246-4076-877a-48574b6c9a71?platform=QnA

Answer 2

A number of points here.

"I am using a look-up field for the brand to avoid complications in misspelling and other duplications which can affect subsequent analysis."

That is the prime reason to us lookup TABLES (note not fields). To standardize input.

But as George and DBGuy stated, lookup FIELDS are not recommended. Lookups should be done using list controls on the form level, NOT at the table level.

"If I use a look up field for the brand and for the product itself in the Consumption table I can still end up with the wrong brand being attributed to the product and this will result in false data in nutritional comparisons. I have therefore attempted to create calculated fields in the Products which concatenate the brand and the product description and wish to use these in the Consumption table."

This is NOT the correct approach. In your Product table, there should be a Product ID which identifies the Brand and Product. You should not have multiple fields in your Consumption table for Brand and Product, just one foreign key for the Product ID. If you need to choose from both Brand and Product, then you have a couple of options. One would be to use a List box instead of a combobox on your form. With a List box you can display multiple columns so you can see both columns. The drawback to a list box is the space it takes up on the form.

Another option is to display multiple columns in your combobox. The drawback here is that the multiple columns are only displayed when the list is dropped down. So the combo only shows one column once selected. You can get around this by using an unbound comb box set to display the hidden column.

A third option is use a Rowsource for a combo that concatenates the Brand name and Product description.

Note: in each option, what is stored in the table is the ProductID, not the text descriptions.

"create a table in which I can clearly identify the brand and product which is being consumed."
The solution to your problem is proper table design and proper use of queries and forms. You shouldn't be concerned about "clearly identifying the brand and product in a table". That is a function for a query, form or report. You mentioned using a calculated field. I don't recommend them as they are limited. Calculations should be done in query columns or form controls.

Answer 3

The irony of Lookup fields in tables is that seasoned developers, who probably could implement them safely and effectively, avoid them because of the inherent problems in using them.

Those new to relational database applications in general, and Access in particular, are unfortunately more likely to implement them, and do so inappropriately because of the lack of experience with handling relational data.

Hence the advice not to go down that twisted side path.

Get away from the idea of Lookup fields in this situation, which is probably too complex for a usable table lookup anyway.

Use queries to create the combined or concatenated display of the fields you want to display. Join the tables in the query on the Primary and Foreign Key fields in the tables and add other fields as required to the query output results.

Answer 4

Using lookup fields at the table level is considered by many experts as something to avoid. Users won't make any mistakes entering the brand names if you use a combobox on a form for entering records. It is also considered unsafe to manually enter records directly into tables. Use forms as much as possible for your user interface.

As for obtaining a unique combination of brand and product for consumption, if you have a foreign key for the brand in the product table, then using the primary key of the product table in the consumption table should be enough to tell which combination of product and brand it is, since that primary key and easily tell the brand by the foreign key associated with it.