Consider an “orders table” which holds a number for the employee who looks after the order. When you view the “order table” you do not see the number that is stored in the “orders table” you see the employee’s name. The name comes from an “employees table” which associates the name with the number.
The sample MS Access database “north-wind” uses this feature, see below. I have used the feature in my own test database , but have some questions:
. What is this feature called?
. Where can I read more about it?
. Can this be done by writing a query rather than by using SELECT DISTINCTROW, as shown below.
. What are the pros and cons of using this feature
From the north-wind database we have:
** Employees table structure
Employees.EmployeeID
. autonumber
. Long integer
. New values = increment
. Indexed = yes no duplicates
Employees. LastName
. text
. Field size = 20
. Caption = Last Name
. Default value = none
. Required = yes
. Allow zero length = no
. Indexed = yes no dups
. Unicode compression = yes
. Ime mode = no control
. Ime sentence mode = none
FirstName
. similar to last name
** Orders table structure
Orders.OrderID
. autonumber
. Long integer
. New values = increment
. Caption = Order ID
. Indexed = yes no duplicates
Orders.EmployeeID
. number (Viewing the table shows the employee last name, and first name. It does not show this employee number)
. Field size = Long integer
. Decimal places = auto
. Caption = Employee
. Required = no
. Indexed = yes no duplicates
EmployeeID Look up
. Combo box
. Row source type = table query
. Row source
SELECT DISTINCTROW [employees].[employeeID], [LastName] & “, ” & [FirstName] AS name FROM employees ORDER BY [employees].[LastName}, [employees].[FirstName];
. Bound column = 1
. Column count = 2
. Column heads = no
. Column widths = 0cm ( it did not work without this - strange)
. List rows = 8
. List width = auto
. Limit to list = yes
. Allow value lists edits = no
. Show only row source value = no
I welcome your comments