Using a Field List as a data source

[Mishanya]

In most examples of databases I've found the next design of Field List in
forms:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
ORDER BY Customers.CompanyName;

while the scrolling list in the form shows the values of CompanyName field
from the Customers table.

1. When I try to use this command, the scrolling list shows both the
CustomerID and CompanyName fields. I've tried to copy exactly all the other
properties - still got the both. What's the trick?
2. Why using both the fields (the index and the actual value) in the command
instead of querying only the actual value field?

 

[Allen Browne]

To show only the 2nd column, set the first one to zero-width.
Properties:
Column Count: 2
Column Widths: 0

In a relational database, there are one-to-many relations. For example, one
customer will (hopefully) submit many orders over time. Therefore your
Orders table needs a field that relates back to the Customers table. It's
better to use a CustomerID to identify the customer. That way if the company
changes names (of if you just misspelled it), you still have their orders
connected up the the right customer correctly.

 

[Mishanya]

Got it!
Thank U very much.

To show only the 2nd column, set the first one to zero-width.
Properties:
Column Count: 2
Column Widths: 0

In a relational database, there are one-to-many relations. For example, one
customer will (hopefully) submit many orders over time. Therefore your
Orders table needs a field that relates back to the Customers table. It's
better to use a CustomerID to identify the customer. That way if the company
changes names (of if you just misspelled it), you still have their orders
connected up the the right customer correctly.