A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access combo box-show name, not ID, in table?



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2005, 01:01 AM
write on
external usenet poster
 
Posts: n/a
Default Access combo box-show name, not ID, in table?

I have read through some old posts relative to my question, but came away
three times as confused and intimidated. Here is a simple and common example
of the equivalent of what I want to do:

Let's say I have a Suppliers table and form, and a Products table and form.
On the Products form I want to create a combo box for the Supplier field that
will use the Supplier table as a source to look up data. I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table. When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Is there a REASONABLE way for a non-programmer to accomplish this?

I notice that the sample Northwinds database shows names (as opposed to IDs)
in tables routinely. I have carefully studied design, field properties, and
relationships until my eyes glazed over. But for the life of me I cannot
duplicate this.

Then there's the question of expressions vs. SQL statements. How do I tell
the difference? How do I know which I need? How do I learn to write either
one?

I am using Access (in Office) 2003--with an Idiot's Guide for Access 2000.
(That's where I started, but didn't get far. This time I mean it--but I'm
beginning to wonder if I'm crazy.)
  #2  
Old April 22nd, 2005, 01:11 AM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

Yes, you can set up the equivalent of a combo box in the table. However,
tables are just to store data. They shouldn't be used by regularly going to
the table. As you indicate, the combo box on the form does show the name and
it is possible to have the name show in the results of a report with just
the ID being stored in the table.

What you are looking for is setting up the Lookup tab for that field in the
table. You would set it up just as you do the combo box on the form. The
problem with doing this is that if you do look at the table, what you see
isn't what is really there and this can cause confusion and problems later.

See item #2 at this link.
http://www.mvps.org/access/tencommandments.htm

--
Wayne Morgan
MS Access MVP


"write on" write wrote in message
...
I have read through some old posts relative to my question, but came away
three times as confused and intimidated. Here is a simple and common
example
of the equivalent of what I want to do:

Let's say I have a Suppliers table and form, and a Products table and
form.
On the Products form I want to create a combo box for the Supplier field
that
will use the Supplier table as a source to look up data. I want the combo
box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table. When I look at the table, I want that
info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Is there a REASONABLE way for a non-programmer to accomplish this?

I notice that the sample Northwinds database shows names (as opposed to
IDs)
in tables routinely. I have carefully studied design, field properties,
and
relationships until my eyes glazed over. But for the life of me I cannot
duplicate this.

Then there's the question of expressions vs. SQL statements. How do I tell
the difference? How do I know which I need? How do I learn to write either
one?

I am using Access (in Office) 2003--with an Idiot's Guide for Access 2000.
(That's where I started, but didn't get far. This time I mean it--but I'm
beginning to wonder if I'm crazy.)



  #3  
Old April 22nd, 2005, 02:40 AM
Albert D. Kallal
external usenet poster
 
Posts: n/a
Default

As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table.


what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).


When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.


Yes...you build query to do this. You then build combo box on the form that
displays that returns the id..and this comb box can search/display by any
column you want..but you always still store the id.


Is there a REASONABLE way for a non-programmer to accomplish this?


You can use a query to do the above. Just make the combo box return the
"id"...and make sure the query has as many (or all) fields from the other
table. once done, then no code at all needs to be written here...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal



  #4  
Old April 23rd, 2005, 03:45 PM
write on
external usenet poster
 
Posts: n/a
Default

Thanks, Wayne.

OK, you've convinved me to do things the "normal" way--a rarity for me. That
list of the Evils of Lookup Fields is pretty scary, all right. Between you,
the Ten Commandments you linked me to, and the other response to this post,
some (not all) of my "Why?" questions have been answered. (See my response to
Albert Kallal).

I love the Ten Commandments, but I don't understand all of them. To wit:

#4 What's a "procedure"--as in "write comments in your procedures and
explain each variable"?
#5 What's error handling?
#6 What does it mean to split a database?
#9 I'm not even going to touch it.
#10 I dread to ask, although this sounds like the really important one--
*Back-up my database? I regularly backup my documents (to a zip disk). I
assumed my Access files (databases) would be included in that. Am I wrong?
Does this mean something different?
*Work only on the Production Database? What? How?
*Prototype copy? What? How?

Thanks for your help.


"Wayne Morgan" wrote:

Yes, you can set up the equivalent of a combo box in the table. However,
tables are just to store data. They shouldn't be used by regularly going to
the table. As you indicate, the combo box on the form does show the name and
it is possible to have the name show in the results of a report with just
the ID being stored in the table.

What you are looking for is setting up the Lookup tab for that field in the
table. You would set it up just as you do the combo box on the form. The
problem with doing this is that if you do look at the table, what you see
isn't what is really there and this can cause confusion and problems later.

See item #2 at this link.
http://www.mvps.org/access/tencommandments.htm

--
Wayne Morgan
MS Access MVP


"write on" write wrote in message
...
I have read through some old posts relative to my question, but came away
three times as confused and intimidated. Here is a simple and common
example
of the equivalent of what I want to do:

Let's say I have a Suppliers table and form, and a Products table and
form.
On the Products form I want to create a combo box for the Supplier field
that
will use the Supplier table as a source to look up data. I want the combo
box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table. When I look at the table, I want that
info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Is there a REASONABLE way for a non-programmer to accomplish this?

I notice that the sample Northwinds database shows names (as opposed to
IDs)
in tables routinely. I have carefully studied design, field properties,
and
relationships until my eyes glazed over. But for the life of me I cannot
duplicate this.

Then there's the question of expressions vs. SQL statements. How do I tell
the difference? How do I know which I need? How do I learn to write either
one?

I am using Access (in Office) 2003--with an Idiot's Guide for Access 2000.
(That's where I started, but didn't get far. This time I mean it--but I'm
beginning to wonder if I'm crazy.)




  #5  
Old April 23rd, 2005, 05:17 PM
write on
external usenet poster
 
Posts: n/a
Default

Thanks for the response.

You wrote,
As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.


First, viewed how? I find subdatasheets to be too cumbersome. (I am visually
impaired, so the less I have to scan through and pick out the better.) Is
there a better way to link to the Supplier Table that I'm missing? I guess my
reasoning went something like this: Queries and reports are useful for
sorting or displaying data in a format that's not readily available in a
table (especially using fields from more than one table). But if I can set up
a table to show exactly what I want to see, why not do that, rather than
bothering to write a query, which I then have to go to and run just to see
what I want? Put another way, why duplicate the table with a query, if I can
just have the table show what I want in the first place? (Answer: Because
that ain't so easy after all!)

But it turns out that I apparently have to write a query anyway to put the
info in the table the way I want it. Still, that saves navigating to and
running another object every time I want to see the info. But then, I could
just open the query, rather than even opening the table, which I guess is the
point after all. Sorry I'm rambling; I'm "thinking out loud" here.

You say:
The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..


I'm sorry, but I have to ask: If the normal and recommended way is to store
and display the [Supplier] ID# in the [Products] table, WHY is the sample
database written to display [Supplier] Names in the [Products] tables, which
is the ABnormal and NOT recommended way? This is confusing to us newbies (at
least this newbie), who presumably use the sample to help us understand the
normal way to use a database.

Also, I'm afraid I don't quite follow. 1) Are you implying that I should be
looking at the form within the table somehow? 2) Are you saying that the
combo box in the form, which displays Supplier Names to choose from for data
entry, but stores the ID in the table, is itself a kind of query? 3) I don't
understand what you mean by "set the ID." 4) I guess I'm not clear yet on
join queries, and I'm not sure which queries you are referring to here.

You asked:
Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table.


what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).


Sorry I wasn't clear. What I had in mind was this:
I would use a Products Form for data entry, to feed a Products Table. The
Products Table would have a Supplier field. The corresponding field on the
Products Form
would be a combo box (or list box, if you prefer). In order to enter the
Supplier to go with a particular product, I would use this combo/list box (on
the Products Form) to choose the Supplier Name (so I don't have to memorize
all Supplier ID#). I understand the norm is to then store the Supplier ID in
the Supplier field, in the Products Table. What I originally intended,
however, was to store, or at least display, the Supplier Name (rather than
ID) in the Products Table, as well as displaying it in the form. My thinking
was, "If I view the data in the Products Table, what good is the Supplier ID
to me, unless I have memorized which Supplier belongs to which ID?" But I
guess I'm SUPPOSED TO view the data either one record at a time in a form, or
in a query, or in a report. The table is apparently not MEANT for viewing;
it's just a storage closet from which to pull out whatever I want to view
somewhere else. Did I finally get it?

I guess most of this is a moot point if I just stick to the normal way of
doing things. Thanks again for your help.



"Albert D. Kallal" wrote:

As others mentioned, the WHOLE idea of relational database system is hat you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier NAMES
(not ID#s) in the Products table.


what do you mean list of names? do you mean suppler names? or do you look up
a name..and what the suppler name to appear? (I guess you are not clear on
name vs product name...or do you mean one and the same?).


When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.


Yes...you build query to do this. You then build combo box on the form that
displays that returns the id..and this comb box can search/display by any
column you want..but you always still store the id.


Is there a REASONABLE way for a non-programmer to accomplish this?


You can use a query to do the above. Just make the combo box return the
"id"...and make sure the query has as many (or all) fields from the other
table. once done, then no code at all needs to be written here...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal




  #6  
Old April 24th, 2005, 03:46 AM
Fred Boer
external usenet poster
 
Posts: n/a
Default

Dear "write on":

It caught my eye that you say you are visually impaired. I teach at a school
for blind and visually impaired students. I have taught computer courses
involving Access to totally blind and low vision students. If you have
problems with Access related to your vision, perhaps I might be of
assistance; or, perhaps I can learn from you!

Fred Boer

"write on" wrote in message
...
Thanks for the response.

You wrote,
As others mentioned, the WHOLE idea of relational database system is hat

you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.


First, viewed how? I find subdatasheets to be too cumbersome. (I am

visually
impaired, so the less I have to scan through and pick out the better.) Is
there a better way to link to the Supplier Table that I'm missing? I guess

my
reasoning went something like this: Queries and reports are useful for
sorting or displaying data in a format that's not readily available in a
table (especially using fields from more than one table). But if I can set

up
a table to show exactly what I want to see, why not do that, rather than
bothering to write a query, which I then have to go to and run just to see
what I want? Put another way, why duplicate the table with a query, if I

can
just have the table show what I want in the first place? (Answer: Because
that ain't so easy after all!)

But it turns out that I apparently have to write a query anyway to put the
info in the table the way I want it. Still, that saves navigating to and
running another object every time I want to see the info. But then, I

could
just open the query, rather than even opening the table, which I guess is

the
point after all. Sorry I'm rambling; I'm "thinking out loud" here.

You say:
The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..


I'm sorry, but I have to ask: If the normal and recommended way is to

store
and display the [Supplier] ID# in the [Products] table, WHY is the sample
database written to display [Supplier] Names in the [Products] tables,

which
is the ABnormal and NOT recommended way? This is confusing to us newbies

(at
least this newbie), who presumably use the sample to help us understand

the
normal way to use a database.

Also, I'm afraid I don't quite follow. 1) Are you implying that I should

be
looking at the form within the table somehow? 2) Are you saying that the
combo box in the form, which displays Supplier Names to choose from for

data
entry, but stores the ID in the table, is itself a kind of query? 3) I

don't
understand what you mean by "set the ID." 4) I guess I'm not clear yet on
join queries, and I'm not sure which queries you are referring to here.

You asked:
Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier

NAMES
(not ID#s) in the Products table.


what do you mean list of names? do you mean suppler names? or do you

look up
a name..and what the suppler name to appear? (I guess you are not clear

on
name vs product name...or do you mean one and the same?).


Sorry I wasn't clear. What I had in mind was this:
I would use a Products Form for data entry, to feed a Products Table. The
Products Table would have a Supplier field. The corresponding field on the
Products Form
would be a combo box (or list box, if you prefer). In order to enter the
Supplier to go with a particular product, I would use this combo/list box

(on
the Products Form) to choose the Supplier Name (so I don't have to

memorize
all Supplier ID#). I understand the norm is to then store the Supplier ID

in
the Supplier field, in the Products Table. What I originally intended,
however, was to store, or at least display, the Supplier Name (rather than
ID) in the Products Table, as well as displaying it in the form. My

thinking
was, "If I view the data in the Products Table, what good is the Supplier

ID
to me, unless I have memorized which Supplier belongs to which ID?" But I
guess I'm SUPPOSED TO view the data either one record at a time in a form,

or
in a query, or in a report. The table is apparently not MEANT for viewing;
it's just a storage closet from which to pull out whatever I want to view
somewhere else. Did I finally get it?

I guess most of this is a moot point if I just stick to the normal way of
doing things. Thanks again for your help.



"Albert D. Kallal" wrote:

As others mentioned, the WHOLE idea of relational database system is hat

you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier

NAMES
(not ID#s) in the Products table.


what do you mean list of names? do you mean suppler names? or do you

look up
a name..and what the suppler name to appear? (I guess you are not clear

on
name vs product name...or do you mean one and the same?).


When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.


Yes...you build query to do this. You then build combo box on the form

that
displays that returns the id..and this comb box can search/display by

any
column you want..but you always still store the id.


Is there a REASONABLE way for a non-programmer to accomplish this?


You can use a query to do the above. Just make the combo box return the
"id"...and make sure the query has as many (or all) fields from the

other
table. once done, then no code at all needs to be written here...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal






  #7  
Old April 24th, 2005, 03:58 AM
Fred Boer
external usenet poster
 
Posts: n/a
Default

Dear "write on":

While I am not an expert like Wayne or Albert, perhaps I can suggest some
answers:

I love the Ten Commandments, but I don't understand all of them. To wit:

#4 What's a "procedure"--as in "write comments in your procedures and
explain each variable"?


A procedure is a piece of programming code. Access uses Visual Basic for
Applications as its programming language. A procedure might look like this:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Errorhandler
Me.cboAuthor.SetFocus
ExitPoint:
Exit Sub
Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End Sub

This procedure, which runs when a form is opened, sets the focus to a
combobox on the form called "cboAuthor".

#5 What's error handling?


Error handling is programming code which deals with errors that might occur
with your program. In the procedure above, the lines 2,4,6,7, and 8 are
error handling code.


#6 What does it mean to split a database?


To actually have two MDB files: one which has only data tables, (the back
end), and one with all the forms, queries and reports (the front end). The
front end links to the tables in the back end. Usually the back end is on a
server, and the front end on individual workstations.

#9 I'm not even going to touch it.
#10 I dread to ask, although this sounds like the really important one--
*Back-up my database? I regularly backup my documents (to a zip disk). I
assumed my Access files (databases) would be included in that. Am I wrong?
Does this mean something different?


You back up a database by copying the database file to a backup location. No
one should be using the file when this is done. If you mean that you copy
the files in the folder "My Documents" to a zip disk, you may or may not be
backing up your Access database, depending on whether those files are in
that folder.

*Work only on the Production Database? What? How?
*Prototype copy? What? How?


Production Database=finished database application actually in use.
Prototype=unfinished database application


HTH
Fred Boer


Thanks for your help.


"Wayne Morgan" wrote:

Yes, you can set up the equivalent of a combo box in the table. However,
tables are just to store data. They shouldn't be used by regularly going

to
the table. As you indicate, the combo box on the form does show the name

and
it is possible to have the name show in the results of a report with

just
the ID being stored in the table.

What you are looking for is setting up the Lookup tab for that field in

the
table. You would set it up just as you do the combo box on the form. The
problem with doing this is that if you do look at the table, what you

see
isn't what is really there and this can cause confusion and problems

later.

See item #2 at this link.
http://www.mvps.org/access/tencommandments.htm

--
Wayne Morgan
MS Access MVP


"write on" write wrote in message
...
I have read through some old posts relative to my question, but came

away
three times as confused and intimidated. Here is a simple and common
example
of the equivalent of what I want to do:

Let's say I have a Suppliers table and form, and a Products table and
form.
On the Products form I want to create a combo box for the Supplier

field
that
will use the Supplier table as a source to look up data. I want the

combo
box
to show me a list of names (not ID#s), AND I want to store Supplier

NAMES
(not ID#s) in the Products table. When I look at the table, I want

that
info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Is there a REASONABLE way for a non-programmer to accomplish this?

I notice that the sample Northwinds database shows names (as opposed

to
IDs)
in tables routinely. I have carefully studied design, field

properties,
and
relationships until my eyes glazed over. But for the life of me I

cannot
duplicate this.

Then there's the question of expressions vs. SQL statements. How do I

tell
the difference? How do I know which I need? How do I learn to write

either
one?

I am using Access (in Office) 2003--with an Idiot's Guide for Access

2000.
(That's where I started, but didn't get far. This time I mean it--but

I'm
beginning to wonder if I'm crazy.)






  #8  
Old April 24th, 2005, 04:55 AM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default

"write on" wrote in message:
...

I love the Ten Commandments, but I don't understand all of them. To wit:


#6 What does it mean to split a database?


Just to add some additional information to Fred's excellent response,
here are some great links on splitting databases which should help
explain the purpose:

http://www.granite.ab.ca/access/splitapp/index.htm

http://allenbrowne.com.au/ser-01.html

http://www.access-experts.com/defaul...lSplitDB&sm=18

http://www.access.qbuilt.com/html/gem_tips.html#SplitDB

How to manually split a Microsoft Access database in Access 2002 or in Access 2003:
http://support.microsoft.com/kb/304932/

--
Jeff Conrad
Access Junkie
Bend, Oregon


  #9  
Old April 24th, 2005, 02:40 PM
write on
external usenet poster
 
Posts: n/a
Default

Fred,

Most of that is pretty scary, but thanks for the enlightenment. At least it
tells me what I need to worry about, and what I don't. That's helpful.

write on


"Fred Boer" wrote:

Dear "write on":

While I am not an expert like Wayne or Albert, perhaps I can suggest some
answers:

I love the Ten Commandments, but I don't understand all of them. To wit:

#4 What's a "procedure"--as in "write comments in your procedures and
explain each variable"?


A procedure is a piece of programming code. Access uses Visual Basic for
Applications as its programming language. A procedure might look like this:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Errorhandler
Me.cboAuthor.SetFocus
ExitPoint:
Exit Sub
Errorhandler:
fncWRMSErrMsg Err.Number, Err.Description
Resume ExitPoint
End Sub

This procedure, which runs when a form is opened, sets the focus to a
combobox on the form called "cboAuthor".

#5 What's error handling?


Error handling is programming code which deals with errors that might occur
with your program. In the procedure above, the lines 2,4,6,7, and 8 are
error handling code.


#6 What does it mean to split a database?


To actually have two MDB files: one which has only data tables, (the back
end), and one with all the forms, queries and reports (the front end). The
front end links to the tables in the back end. Usually the back end is on a
server, and the front end on individual workstations.

#9 I'm not even going to touch it.
#10 I dread to ask, although this sounds like the really important one--
*Back-up my database? I regularly backup my documents (to a zip disk). I
assumed my Access files (databases) would be included in that. Am I wrong?
Does this mean something different?


You back up a database by copying the database file to a backup location. No
one should be using the file when this is done. If you mean that you copy
the files in the folder "My Documents" to a zip disk, you may or may not be
backing up your Access database, depending on whether those files are in
that folder.

*Work only on the Production Database? What? How?
*Prototype copy? What? How?


Production Database=finished database application actually in use.
Prototype=unfinished database application


HTH
Fred Boer


Thanks for your help.


"Wayne Morgan" wrote:

Yes, you can set up the equivalent of a combo box in the table. However,
tables are just to store data. They shouldn't be used by regularly going

to
the table. As you indicate, the combo box on the form does show the name

and
it is possible to have the name show in the results of a report with

just
the ID being stored in the table.

What you are looking for is setting up the Lookup tab for that field in

the
table. You would set it up just as you do the combo box on the form. The
problem with doing this is that if you do look at the table, what you

see
isn't what is really there and this can cause confusion and problems

later.

See item #2 at this link.
http://www.mvps.org/access/tencommandments.htm

--
Wayne Morgan
MS Access MVP


"write on" write wrote in message
...
I have read through some old posts relative to my question, but came

away
three times as confused and intimidated. Here is a simple and common
example
of the equivalent of what I want to do:

Let's say I have a Suppliers table and form, and a Products table and
form.
On the Products form I want to create a combo box for the Supplier

field
that
will use the Supplier table as a source to look up data. I want the

combo
box
to show me a list of names (not ID#s), AND I want to store Supplier

NAMES
(not ID#s) in the Products table. When I look at the table, I want

that
info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Is there a REASONABLE way for a non-programmer to accomplish this?

I notice that the sample Northwinds database shows names (as opposed

to
IDs)
in tables routinely. I have carefully studied design, field

properties,
and
relationships until my eyes glazed over. But for the life of me I

cannot
duplicate this.

Then there's the question of expressions vs. SQL statements. How do I

tell
the difference? How do I know which I need? How do I learn to write

either
one?

I am using Access (in Office) 2003--with an Idiot's Guide for Access

2000.
(That's where I started, but didn't get far. This time I mean it--but

I'm
beginning to wonder if I'm crazy.)






  #10  
Old April 24th, 2005, 03:39 PM
write on
external usenet poster
 
Posts: n/a
Default

Dear Fred,

Wow! That's pretty cool!

I use ZoomText for screen magnification, and I love it. The downside is, it
doesn't play well with my graphics card (or visa versa), so mouse control can
be a hassle. The mouse can be difficult to position exactly where I need it;
it's hard to control highlighting of text, and may not stop highlighting when
I let go of the mouse button; mouse pointer may jump around the screen
out-of-control.

This has led me to learn and use keyboard shortcuts as much as I can. But
that presents its own set of challenges. First of all, There are cases where
the ZoomText hotkeys conflict with Microsoft shortcuts. ZoomText always wins.
Then I have to do the MS function another way--usually ALT+menu keys. But
even that doesn't always work. (Case in point: If I open a new message window
in Outlook Express, there is an address book icon next to the "To:" box
label. The idea is, you click that icon to open your address book and select
an address to put in the "To:" field. I cannot find a way to open that icon
without a mouse. I cannot even get it to receive focus so I can try to open
it. And this function can't be done with ALT+menus either. Do screen readers
have a way around this?)

Receiving focus is often a problem when trying to navigate. ZoomText has a
function that lets me ALT+TAB through all open windows, until I get to the
one I want. But the window I stop at doesn't always receive focus the way it
should, so that my next keyboard command will work there. I particularly have
trouble with Outlook in this regard.

Navigating within a window is usually a pain. The F6 key doesn't work most
of the time (to move between panes) the way MS shortcut key lists say it is
supposed to, and I don't even know why. I can't even find ZoomText conflicts
to explain that. There are certain areas of the Outlook main window, for
example, that just can't be had without a mouse.

Internet Explorer is a nightmare without a mouse. I can't even TAB or F6 out
of the menu bar/toolbar area, into the actual web page. This is where MS
MouseKeys comes in handy. I can use keys to move and click the mouse; once
you get used to it, control is much better than handling the mouse, although
it's slower. (See Accessibility Wizard in Control Panel. You can also adjust
the thickness and blink rate of the cusor.) ZoomText does have the Web Finder
feature that works with IE. That is useful, if I am determined to use the
keyboard just on principle, but it is more cumbersome and time-consuming than
using the mouse.

As for Access and keyboard shortcuts--well, I haven't even gotten that far
yet. Just getting comfortable with Access itself is all I can handle right
now. The shortcut learning curve will have to wait. And I'm leary of it,
because I supsect all the navigating between multiple objects and views will
be really hairy. Again, the ALT+TAB does help for switching windows. But
receiving focus...? I don't know.

Do you work with ZoomText much? Got any tips for me?

write on
P.S. Should we change the subject, and/or move this discussion to a
different forum? Can we? Will "Notify me of replies" work if we do?


"Fred Boer" wrote:

Dear "write on":

It caught my eye that you say you are visually impaired. I teach at a school
for blind and visually impaired students. I have taught computer courses
involving Access to totally blind and low vision students. If you have
problems with Access related to your vision, perhaps I might be of
assistance; or, perhaps I can learn from you!

Fred Boer

"write on" wrote in message
...
Thanks for the response.

You wrote,
As others mentioned, the WHOLE idea of relational database system is hat

you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.


First, viewed how? I find subdatasheets to be too cumbersome. (I am

visually
impaired, so the less I have to scan through and pick out the better.) Is
there a better way to link to the Supplier Table that I'm missing? I guess

my
reasoning went something like this: Queries and reports are useful for
sorting or displaying data in a format that's not readily available in a
table (especially using fields from more than one table). But if I can set

up
a table to show exactly what I want to see, why not do that, rather than
bothering to write a query, which I then have to go to and run just to see
what I want? Put another way, why duplicate the table with a query, if I

can
just have the table show what I want in the first place? (Answer: Because
that ain't so easy after all!)

But it turns out that I apparently have to write a query anyway to put the
info in the table the way I want it. Still, that saves navigating to and
running another object every time I want to see the info. But then, I

could
just open the query, rather than even opening the table, which I guess is

the
point after all. Sorry I'm rambling; I'm "thinking out loud" here.

You say:
The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..


I'm sorry, but I have to ask: If the normal and recommended way is to

store
and display the [Supplier] ID# in the [Products] table, WHY is the sample
database written to display [Supplier] Names in the [Products] tables,

which
is the ABnormal and NOT recommended way? This is confusing to us newbies

(at
least this newbie), who presumably use the sample to help us understand

the
normal way to use a database.

Also, I'm afraid I don't quite follow. 1) Are you implying that I should

be
looking at the form within the table somehow? 2) Are you saying that the
combo box in the form, which displays Supplier Names to choose from for

data
entry, but stores the ID in the table, is itself a kind of query? 3) I

don't
understand what you mean by "set the ID." 4) I guess I'm not clear yet on
join queries, and I'm not sure which queries you are referring to here.

You asked:
Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier

NAMES
(not ID#s) in the Products table.

what do you mean list of names? do you mean suppler names? or do you

look up
a name..and what the suppler name to appear? (I guess you are not clear

on
name vs product name...or do you mean one and the same?).


Sorry I wasn't clear. What I had in mind was this:
I would use a Products Form for data entry, to feed a Products Table. The
Products Table would have a Supplier field. The corresponding field on the
Products Form
would be a combo box (or list box, if you prefer). In order to enter the
Supplier to go with a particular product, I would use this combo/list box

(on
the Products Form) to choose the Supplier Name (so I don't have to

memorize
all Supplier ID#). I understand the norm is to then store the Supplier ID

in
the Supplier field, in the Products Table. What I originally intended,
however, was to store, or at least display, the Supplier Name (rather than
ID) in the Products Table, as well as displaying it in the form. My

thinking
was, "If I view the data in the Products Table, what good is the Supplier

ID
to me, unless I have memorized which Supplier belongs to which ID?" But I
guess I'm SUPPOSED TO view the data either one record at a time in a form,

or
in a query, or in a report. The table is apparently not MEANT for viewing;
it's just a storage closet from which to pull out whatever I want to view
somewhere else. Did I finally get it?

I guess most of this is a moot point if I just stick to the normal way of
doing things. Thanks again for your help.



"Albert D. Kallal" wrote:

As others mentioned, the WHOLE idea of relational database system is hat

you
only need to store the id field, and then the rest of the fields (like
supple name) can then be viewed.

The way the northwind works is that the id is stored, but the form (or
sub-form) is based on a query. When you set the id, then the name etc
magically will appear!. Those queries need to be left join queries..

Also, it is not clear:
I want the combo box
to show me a list of names (not ID#s), AND I want to store Supplier

NAMES
(not ID#s) in the Products table.

what do you mean list of names? do you mean suppler names? or do you

look up
a name..and what the suppler name to appear? (I guess you are not clear

on
name vs product name...or do you mean one and the same?).


When I look at the table, I want that info
(Supplier NAME) to just be there, without having to fish through
subdataforms, separate queries, or reports.

Yes...you build query to do this. You then build combo box on the form

that
displays that returns the id..and this comb box can search/display by

any
column you want..but you always still store the id.


Is there a REASONABLE way for a non-programmer to accomplish this?

You can use a query to do the above. Just make the combo box return the
"id"...and make sure the query has as many (or all) fields from the

other
table. once done, then no code at all needs to be written here...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
show data from a table based upon combo box Daiuy New Users 3 January 8th, 2005 04:54 PM
Access 2000 -vs- Access 2003? Mark General Discussion 5 November 30th, 2004 07:36 AM
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM
Access XP Compared to Access 2003 Mardene Leahu New Users 1 October 1st, 2004 05:11 AM
COMPARE THE TWO TABLES Stefanie General Discussion 0 June 4th, 2004 04:36 PM


All times are GMT +1. The time now is 08:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.