Database Relationships

[David M C]

When you look at the Northwind Databse example, in each of their orders they
are able to select the company name from a combobox. When you look at the
relationships diagram the SupplierID fields in Order and Suppliers are
linked. They alter the lookup field for SupplierID in the Order table to
select the appropriate SupplierID from the suppliers table (I have been able
to achieve this in my database design). However, instead of displaying
SupplierID, they have it displaying CompanyName. How can I do this?

I have all the keys and relationships setup, copied the lookup field from
the Northwind sample into my own database and edited accordingly, but I can
only select Suppliers by ID.

Any ideas?

Thanks

Dave

 

[Jeff Boyce]

David

I may not be understanding your situation, so ...

It sounds like you are using a lookup field in a table. While functional,
they are confusing.

A scan through this (tablesdbdesign) newsgroup will reveal a strong
consensus against using lookup datatypes. Instead, use a combo box in a
form. By setting the width of the first column (the ID field) to 0, what
gets displayed is the second column.

 

[David M C]

Thanks, almost there now. I'm putting:

SELECT [SupplierID], [CompanyName] FROM Suppliers ORDER BY [CompanyName];

In the "Row Source" field of the "Lookup" tab in the design view of a table.
Having now set the Column Widths to 0, I get nothing displayed in the
Combobox, as opposed to the SupplierID's I used to have. I'm obviously doing
something wrong.

Is this anywhere near what your were suggesting?

Thanks

Dave

 

[Jeff Boyce]

David

No. I was suggesting that you get AWAY from using the lookup field in the
table. Create your lookup table, sure. But use FORMS for adding/edit data,
not the tables.

You could still use the SQL statement, but as the Control Source for a combo
box in a form.

Regards

Jeff Boyce
<Office/Access MVP>

Thanks, almost there now. I'm putting:

SELECT [SupplierID], [CompanyName] FROM Suppliers ORDER BY [CompanyName];

In the "Row Source" field of the "Lookup" tab in the design view of a
table.
Having now set the Column Widths to 0, I get nothing displayed in the
Combobox, as opposed to the SupplierID's I used to have. I'm obviously
doing
something wrong.

Is this anywhere near what your were suggesting?

Thanks

Dave

David

I may not be understanding your situation, so ...

It sounds like you are using a lookup field in a table. While
functional,
they are confusing.

A scan through this (tablesdbdesign) newsgroup will reveal a strong
consensus against using lookup datatypes. Instead, use a combo box in a
form. By setting the width of the first column (the ID field) to 0, what
gets displayed is the second column.

--
Regards

Jeff Boyce
<Office/Access MVP>

 

[Bernard Piette]

Jeff,
I think I asked the same thing yesterday
Can you point out a reference to lookups not being the best alternative?
Thanks

Bernard Piette

David

No. I was suggesting that you get AWAY from using the lookup field in the
table. Create your lookup table, sure. But use FORMS for adding/edit data,
not the tables.

You could still use the SQL statement, but as the Control Source for a combo
box in a form.

Regards

Jeff Boyce
<Office/Access MVP>

Thanks, almost there now. I'm putting:

SELECT [SupplierID], [CompanyName] FROM Suppliers ORDER BY [CompanyName];

In the "Row Source" field of the "Lookup" tab in the design view of a
table.
Having now set the Column Widths to 0, I get nothing displayed in the
Combobox, as opposed to the SupplierID's I used to have. I'm obviously
doing
something wrong.

Is this anywhere near what your were suggesting?

Thanks

Dave

David

I may not be understanding your situation, so ...

It sounds like you are using a lookup field in a table. While
functional,
they are confusing.

A scan through this (tablesdbdesign) newsgroup will reveal a strong
consensus against using lookup datatypes. Instead, use a combo box in a
form. By setting the width of the first column (the ID field) to 0, what
gets displayed is the second column.

--
Regards

Jeff Boyce
<Office/Access MVP>

When you look at the Northwind Databse example, in each of their orders
they
are able to select the company name from a combobox. When you look at
the
relationships diagram the SupplierID fields in Order and Suppliers are
linked. They alter the lookup field for SupplierID in the Order table
to
select the appropriate SupplierID from the suppliers table (I have been
able
to achieve this in my database design). However, instead of displaying
SupplierID, they have it displaying CompanyName. How can I do this?

I have all the keys and relationships setup, copied the lookup field
from
the Northwind sample into my own database and edited accordingly, but I
can
only select Suppliers by ID.

Any ideas?

Thanks

Dave