How To Change The Data Displayed In A Linked Field In An Access Table

If you link two tables in Access, for example a primary key in one table to a non primary key in another table, the linked field in the second table only shows primary field values from the first table. You can change what is displayed in the second table by using a lookup based on the primary key to show more meaningful data from the first table. Read this brief article to discover how to do this.

Have you ever come across this situation? You have two tables in an Access database such as a Staff table and a Sales table. The primary key in the Staff table contains a unique staff code for each member of staff. You link the tables through common fields, for example StaffID in the Staff table and StaffRef in the Sales table. So values entered into the sales table under StaffRef must be one of the values from StaffID. The trouble is not everyone remembers the staff ID. So we want to change the display in StaffRef in the Sales table to show the actual staff names, even though the field still stores the StaffID.

In our example, suppose the Staff table has the fields StaffID, Firstname and Surname. The StaffID is the primary key and stores each person's staff code. The Sales table has the fields SalesID, StaffRef and product. The tables are linked using the fields StaffID and StaffRef. So the StaffRef field in the Sales table is used to type in the code for member of staff who made the sale, provided the codes are known or course.

We want to change the display for the StaffRef in the Sales table so that we see the actual firstname and surname for each member of staff making a sale, whilst allowing the StaffRef field to still store the StaffID codes. That way it will be easy to record which staff member made each sale.

We're going to do this in two steps. First we create a query based on the Staff table to create the display we want. The query will have two fields, StaffID and a new Fullname field to display firstname and surname with a space between. So we create a new query based on the table Staff.

In the query design view and add the field StaffID to the lower query builder. Then in the next empty column type the following, without the quotes. "Fullname:Firstname & " " & Surname" and then save the query as, for example, QryFullnames. Now run the query and you should see a list a two field staff list, showing StaffIDs and corresponding fullnames. The query can now be closed.

Secondly we change the data displayed in the StaffRef field in the Sales table to show the actual staff fullname for each sale made. We do this by setting the StaffRef field to lookup the stored StaffRef value for a match in the query we just created, and display the corresponding Fullname.

So to start this second part, ensure the Sales table is open, switch to design view and select the StaffRef field. Then in the properties sheet in the lower part of the display select the lookup tab. In the first white box in the lookup tab change the setting to Combo. Then in the lower part of the tab, leave the Row Source setting at Table/Query, as we're going to lookup a query. Then move one box down and click into the white box to the immediate right of Row Source. Select the query QryFullnames.

We're just about there, but we now want to set the size of the lookup display. So lower down the tab, change the Column Count to 2, so the combo control contains two columns, StaffRef and Fullname. However we don't want to see StaffRef, so select the white box right of Column Widths, and type in 0;3 (a zero, then a semi colon, then a three) and then save the design changes. We set the first column width to zero to hide StaffRef in the combo and the displayed data in the StaffRef field. That's it, so you can test the field display.

So ensure the Sales table is open in regular view and select any record under the StaffRef field and click on the combo control. The staff fullnames should be displayed. Assign some staff to several sales records, and you'll see the staff fullname is always displayed. The field is actually storing only the StaffRef value but we're using this value to display the staff fullnames via our query.

Hopefully this article has given you a brief insight into the possibilities of using lookups in Access tables. If you want to take this further you might like to consider attending a training course and find out much more about Access table relationships and field properties.