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 7 3244
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
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
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.
"Marco Simone" <ma*********@ne t.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.
> 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
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********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com... "Marco Simone" <ma*********@ne t.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.
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********@hot mail.com> wrote in message news:bf******** *************** ***@posting.goo gle.com...
"Marco Simone" <ma*********@ne t.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Koen |
last post by:
Hi all,
My application uses a lot of lookup tables.
I've splitted the frontend (forms, reports, etc) from the backend (data).
The database has around 10 different users.
The values in the lookup tables are not likely to change.
Question 1: Should I include them in the backend (with rest of data) or the
frontend?
|
by: my-wings |
last post by:
I've been reading about how evil Lookup fields in tables are, but I've got
to be missing something really basic. I know this subject has been covered
before, because I've just spent an hour or two reading about it on google,
but there is something I still don't understand, and I'm hoping someone will
be willing to explain it to me in small words.
Let's say I have a table for addresses, and it includes a field for state.
What I would...
|
by: Zachary Turner |
last post by:
I want to make a Lookup Field based on another Lookup field. In other
words, I have this table A with two fields: ID and Name, where ID is
an Autonumber and Name is a friendly name. Then I have a table B
which links to A via a Lookup Field so that I can select the friendly
name from combo box. Now, I want to have a table C which links to
table B via a lookup field, and only displays the items which are
selected in some combo box of some...
|
by: google |
last post by:
I have a database with four table. In one of the tables, I use about
five lookup fields to get populate their dropdown list. I have read
that lookup fields are really bad and may cause problems that are hard
to find. The main problem I am having right now is that I have a report
that is sorted by one of these lookup fields and it only displays the
record's ID number. When I add the source table to the query it makes
several records...
|
by: Paul H |
last post by:
Say I have a table called tblPeopleInfo, one of the fields in the table is
called FavouriteFruit. The FavouriteFruit field is a lookup field and will
contain Apples, Oranges, Grapes etc..The list can be added to by users of
the database. What is the best way to construct this lookup?
Should it lookup a Table/Query or a Value List?
If it looks up to a Table/Query should the lookup table just have a single
"text" field or should it have...
| |
by: Greg Strong |
last post by:
Hello All,
Is it possible to change table field lookup properties in code?
I've been able to change other field properties in code, however so far
no luck with field lookup properties. What I've done for test purposes
is use a text input file for the table field lookup properties. I
thought that I'd start first by just changing the 'Display Control'
property. Thanks to Allen Browne for some ideals per...
|
by: Paul H |
last post by:
Suppose I have a table called tblPeople and I want a field to illustrate
whether each person prefers cats or dogs. I could do it one of three ways.
1. A plain text field
Create a text field in tblPeople called PreferredPet.
2. A lookup field that stores text values.
Create a text field in tblPeople called PreferredPetID and use it to lookup
an identical text field in tblPreferredPets.
|
by: samdev |
last post by:
I have set up a table with a few fields that are set to
required....all work fine except the fields that are Lookup from
another table.
What am I forgetting to set?
Thanks!!
|
by: Mark |
last post by:
I have a table with a field that uses a combobox to populate values. The
Lookup tab within table design mode is the following:
Display Control Combo Box
Row Source Type Table/Query
Row Source SELECT .LastName, .FirstName, .EmployeeNumber FROM ;
Bound Column 3
Column Count 3
Column Heads Yes
Column Widths 1";1";1"
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |