Lookup field in table

[Marco Simone]

Hi,

What is your opinion about using Lookup field in table. I would like to use
lookup field in table 1, so that I can choose data in combo box from table
2.
Is this good design of database?

Thanks in advance, Marco

[Pavel Romashkin]

It is a very common design, but it is best to use forms for data entry
and not to expose tables to the users.

Pavel

Marco Simone wrote:

Hi,

What is your opinion about using Lookup field in table. I would like to use
lookup field in table 1, so that I can choose data in combo box from table
2.
Is this good design of database?

Thanks in advance, Marco

[Bas Cost Budde]

I agree with Pavel.

'lookup' is an expression that already has some meaning to Access users.
As such, it is likely to trigger a lively discussion.

If you mean a field that contains a copy of a primary key in another
table, that is called a foreign key. Perfectly legal, and even quite
necessary.

If you mean the display issue that Microsoft added to its table design,
allowing you to display a combobox in the place of the field, hiding the
actual data in the field, and displaying the corresponding value from
the "parent" table (with possibility to select another value from a
list), that is called a lookup field. It comes with an added
relationship, which becomes visible when you open the Relationship
window and choose to display all relationships. (Does it, John Vinson?
It may not, because the Relationship window is a little buggy.)

Standing advice is:
* do create that other (parent) table
* do create a relationship (but do so by hand, enabling you to choose
enforced relational integrity and cascading update, I like those)
* don't choose a display type in the original (child) table--even though
it adds the bonus that when you create a form on this table, the
combobox is already built for you. There are other, cleaner ways for that.

Marco Simone wrote:

Hi,

What is your opinion about using Lookup field in table. I would like to use
lookup field in table 1, so that I can choose data in combo box from table
2.
Is this good design of database?

Thanks in advance, Marco

--
Bas Cost Budde

[Marco Simone]

Thanks for reply. I was thinking to use lookup field in table 1 for looking
for records from table 2.
In this way, I have same data in table 1 and table 2. Table 2 has all values
for user to choose, and in table 1 I choose 1 value (for every record) from
table 1. Off course, I use form for entering data in table 1 and 2. I hope
you understand what I am trying to say.
So this way I am duplicating data in table 1 (am I right and is this OK with
relational database).

Thanks, Marco

"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:bv**********@news2.solcon.nl...

I agree with Pavel.

'lookup' is an expression that already has some meaning to Access users.
As such, it is likely to trigger a lively discussion.

If you mean a field that contains a copy of a primary key in another
table, that is called a foreign key. Perfectly legal, and even quite
necessary.

If you mean the display issue that Microsoft added to its table design,
allowing you to display a combobox in the place of the field, hiding the
actual data in the field, and displaying the corresponding value from
the "parent" table (with possibility to select another value from a
list), that is called a lookup field. It comes with an added
relationship, which becomes visible when you open the Relationship
window and choose to display all relationships. (Does it, John Vinson?
It may not, because the Relationship window is a little buggy.)

Standing advice is:
* do create that other (parent) table
* do create a relationship (but do so by hand, enabling you to choose
enforced relational integrity and cascading update, I like those)
* don't choose a display type in the original (child) table--even though
it adds the bonus that when you create a form on this table, the
combobox is already built for you. There are other, cleaner ways for that.

[Pieter Linden]

"Marco Simone" <ma*********@net.hr> wrote in message news:<bv**********@ls219.htnet.hr>...

Hi,

What is your opinion about using Lookup field in table. I would like to use
lookup field in table 1, so that I can choose data in combo box from table
2.
Is this good design of database?

Thanks in advance, Marco

Personally, I *never* use lookup fields. I find them confusing,
because they store the ID of the related field, so what you see is NOT
what you get. I prefer to use a combobox on a form and go that route.

[Bas Cost Budde]

> Thanks for reply. I was thinking to use lookup field in table 1 for looking

for records from table 2.
In this way, I have same data in table 1 and table 2. Table 2 has all values
for user to choose, and in table 1 I choose 1 value (for every record) from
table 1. Off course, I use form for entering data in table 1 and 2. I hope
you understand what I am trying to say.
So this way I am duplicating data in table 1 (am I right and is this OK with
relational database).

You are duplicating *some* of the data all right. That is exactly how it
is meant.
I hope the key field is small--but the idea is that in this other table
may live a lot more fields, all of which get referred to by the one key
(may consist of more fields!) that gets 'duplicated' in the other table.

--
Bas Cost Budde

[Marco Simone]

Thanks for reply,

If I use combo box on a form , then I would have to use query to get data in
combo box from table 2, right?
Also, I am reading book about access and table 1 has lookup field for table
2, but table 2 doesn't have primary key, just field CITY. Is this ok,
because I was reading that every table should have primary key?

Thanks, Marco

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

"Marco Simone" <ma*********@net.hr> wrote in message

news:<bv**********@ls219.htnet.hr>...

Hi,

What is your opinion about using Lookup field in table. I would like to use lookup field in table 1, so that I can choose data in combo box from table 2.
Is this good design of database?

Thanks in advance, Marco

Personally, I *never* use lookup fields. I find them confusing,
because they store the ID of the related field, so what you see is NOT
what you get. I prefer to use a combobox on a form and go that route.

[Bas Cost Budde]

Since I expect that CITY is unique and identifying, it serves well as
primary key.

A table *must* not have a primary key in Jet, but having a primary key
reflects design choices. And it usually leads to a consistent and
controllable structure.

Marco Simone wrote:

Thanks for reply,

If I use combo box on a form , then I would have to use query to get data in
combo box from table 2, right?
Also, I am reading book about access and table 1 has lookup field for table
2, but table 2 doesn't have primary key, just field CITY. Is this ok,
because I was reading that every table should have primary key?

Thanks, Marco

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

"Marco Simone" <ma*********@net.hr> wrote in message

news:<bv**********@ls219.htnet.hr>...

Hi,

What is your opinion about using Lookup field in table. I would like to
use
lookup field in table 1, so that I can choose data in combo box from
table
2.
Is this good design of database?

Thanks in advance, Marco

Personally, I *never* use lookup fields. I find them confusing,
because they store the ID of the related field, so what you see is NOT
what you get. I prefer to use a combobox on a form and go that route.

--
Bas Cost Budde