Displaying data instead of ID in lookup tables

[riccifs]

Hi to everyone,

I have a question about lookup tables.
Instead to display the ID number in my table I'd like to show the name
associated with it.
Until now, I was use to edit the table in design view then select the
field I wanted to lookup and chose the Lookup tag on the bottom.
But my question is, if the data on a field I want to look up came form
to a previous form that I have looked up before?
May be I'm in a big confusion about the way to organize the "views" on
the tables of a referential db, I don't known!
Could someone help me to make my idea clear in that?

Many thanks,
Stefano.

 

[FredFred]

What I think you seek to do is a common practice, but it is hard to fully
understand your question. For example, one simple way is to make a separate
table with your lookup fields & lists, and then the lookup wizard in table
will specifically take you there.

 

[benyod79 via AccessMonster.com]

Hi Stefano,

The wording of your question is a bit confusing, but I think I might get it.
For a lookup, you have to specify what column is bound, the number of columns
to show and the widths of those columns. My assumption is that the first
column is the ID number, followed by the "name" you'd like to show in the
second column. Therefore, I'd suggest the following:

Bound Column: 1
Column Count: 2
Column Width: 0", 1"

Hope this helps.

 

[Jeff Boyce]

It sounds like you are working directly in the table, rather than via forms.
Access tables store data, Access forms display data.

A lookup table (data that 'fills in', like categorizations/types) works very
well when you show it to the users via a combobox on a form.

A lookup field in a table is quite confusing to users, as it displays one
value (the looked-up value), but actually stores a different value (the
primary key of the looked-up row).

You may find it quite confusing to have the value rather than the ID
displayed in the table, and particularly so if you are trying to work
directly in the table.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[riccifs]

What I think you seek to do is a common practice, but it is hard to fully
understand your question. For example, one simple way is to make a separate
table with your lookup fields & lists, and then the lookup wizard in table
will specifically take you there.

Hi Fred,
you and Jeff have better understood my problems... (I know my wording
has been really confuse!)
However, could you show me a db example to better understand your
idea? (I'm quite novice in building relational db)
I will appreciate any web-site where looking for something like that.
Where is the lookup wizard in a table that you are talking about?
Many thanks for your interest in my case.

Bye,
Stefano.

 

[Jamie Collins]

It sounds like you are working directly in the table, rather than via forms.
Access tables store data, Access forms display data.

That phrase again <g>! Look me in the eyes and tell me *you* never
work directly in a table

Seriously, can anyone tell me what this means?

My real life 'Christmas card list' (contacts database) is in an mdb.
Once a year I use a mail merge in Word to produce address labels.
Occasionally throughout the year I need to amend some data, so I open
the mdb in Access, open the table in 'datasheet' view and make the
changes there. Is anyone seriously suggesting that I am doing
something wrong here? (Remember, I'm the guy who does pretty much
everything else using the SQL language <g>!) Isn't that what the
'datasheet' view is for?

Jamie.

--

 

[Rick Brandt]

That phrase again <g>! Look me in the eyes and tell me *you* never
work directly in a table

Seriously, can anyone tell me what this means?

My real life 'Christmas card list' (contacts database) is in an mdb.
Once a year I use a mail merge in Word to produce address labels.
Occasionally throughout the year I need to amend some data, so I open
the mdb in Access, open the table in 'datasheet' view and make the
changes there. Is anyone seriously suggesting that I am doing
something wrong here? (Remember, I'm the guy who does pretty much
everything else using the SQL language <g>!) Isn't that what the
'datasheet' view is for?

Jamie.

Okay, so the standard recomendation could be modified to be...

"If you expect to see lookup data, have drop down selection lists, nice
formatting, and the ability to have events then use a form". If your
expectation when looking at a table is to see what is actually in that table
and also be able to change it then using the datasheet is fine.

Enterprise Manager in SQL Server 6.5 (if I remember correctly) didn't even
offer an option to "view" the contents of a table. All you could see
directly was the design. To see the data you needed to use a query. In my
opinion that is how all such tools should be set up as it reinforces the
idea that tables are *structures* where your data is stored, As soon as
people see them as data interface objects they are quickly off-track.

 

[Jamie Collins]

Okay, so the standard recomendation could be modified to be...

"If you expect to see lookup data, have drop down selection lists, nice
formatting, and the ability to have events then use a form". If your
expectation when looking at a table is to see what is actually in that table
and also be able to change it then using the datasheet is fine.

Enterprise Manager in SQL Server 6.5 (if I remember correctly) didn't even
offer an option to "view" the contents of a table. All you could see
directly was the design. To see the data you needed to use a query. In my
opinion that is how all such tools should be set up as it reinforces the
idea that tables are *structures* where your data is stored, As soon as
people see them as data interface objects they are quickly off-track.

It seems to me we've got to the point where most people seem to give
the impression that the datasheet view (and/or perhaps the Design
view) of a table *is* the table itself. To me, it's just an Access
Form with a grid and a recordset etc albeit a system-owned one. That's
why I don't get the "You should be using a Form to enter data" retort
either i.e. datasheet table view *is* a Form!

I appreciate such comments are intended to help newbies but when they
are not strictly correct it gets to be grating after a while. Thanks
for your time, rant over

Jamie.

--

 

[Jeff Boyce]

Stefano

Instead of using the Lookup data type in your table definition (looking at
your table in design view), use the data type that represents the primary
key of the table that holds your "lookup" values. For example, in North
America, a common table created to allow such lookups is a [StateProvince]
table. This would probably only have two fields, Abbreviation and
StateProvince. These two fields would hold values such as:

WA Washington
OR Oregon
CA California
BC British Columbia
...

Instead of storing "Washington" in a table of [Address], you'd only need to
store "WA".

In Access, a typical approach to designing an application would have you
store the "WA" in the table, and use a combo box on a form to allow the user
to select "Washington". What the user sees on the form is "Washington".

Lookup fields, in tables, cause confusion. Lookup controls (i.e., combo
boxes, list boxes) on forms make an application easier to use.

The "lookup wizard" is what Access walks you through if you are in design
view on a table and choose the "lookup" data type.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP