MVPs - The Evils of the Lookup Field

[legere864]

I give. I have read the evils of Lookup Fields at
http://www.mvps.org/access/lookupfields.htm. I am curious whether this evil
only pertains to lookup fields at it relates to tables or all lookup fields.
How else do I have a combo box that draws its data from another table without
the lookup field? Maybe I am misunderstanding the term. I am currently
having difficulty with a popup form that filters a report because I have two
combo boxes that lookup fields from other tables and I cannot get my filter
on those fields to work. Is this considered a lookup field? Is there
another way? I receive a filter that contains the following:
(Lookup_FieldName). I hope you can clear this up for me.

 

[Allen Browne]

Good question.

The problem discussed in the article is certainly *not* with the concept of
lookup tables, nor foreign key fields in your table. And, it is perfectly
appropriate to use a combo on your form to interface many of these foreign
key fields.

The problem is with:
a) Putting a combo in the table. This causes no end of confusion for users
about what is really stored in the table, and therefore now to filter on the
field (actually behaves differently in different versions of Access), apply
a form's filter to a report (requires special aliasing as per your example
name), or even do to basic things like trying to build a filter string or a
SQL statement.

b) The actual implementation of the Lookup Wizard: spurious indexes,
unnecessary hidden fields, and so on.

Any decent database is going to have lookup tables, probably lots of them.

 

[legere864]

Thank you for clearing that up for me. I never create fields using the
Lookup wizard in Tables. Now, about the lookup fields in my report that I
want to filter. I have tried using a popup form to filter on these fields,
but it does not work. I have tried filtering the form the report is based on
and exporting the filter string to the report, and it works except the field
is blank. When I go to the report and use the exported string,
((([Lookup_Payee#].PayeeName="Example Name"))), Access throws the following
error "Microsoft can't find the Payee# referred to in your expression. The
name has been changed ... etc." When I select OK, the report runs and is
filtered, but again the field is blank. You mentioned an alias. How do I do
this? Thank you in advance for your help.

 

[Allen Browne]

Create a query that uses both the main table where your data is for the
report and also the Payee# table.

Right-click the Payee# table in the upper pane of the query design window,
and choose Properties.

Beside the Alias property enter the name that matches what Access used in
your form, i.e.:
Lookup_Payee#
In that query, the table is now aliased with that name.

Use that query as the RecordSource for your report, and it will be able to
understand the filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for clearing that up for me. I never create fields using the
Lookup wizard in Tables. Now, about the lookup fields in my report that I
want to filter. I have tried using a popup form to filter on these
fields,
but it does not work. I have tried filtering the form the report is based
on
and exporting the filter string to the report, and it works except the
field
is blank. When I go to the report and use the exported string,
((([Lookup_Payee#].PayeeName="Example Name"))), Access throws the
following
error "Microsoft can't find the Payee# referred to in your expression.
The
name has been changed ... etc." When I select OK, the report runs and is
filtered, but again the field is blank. You mentioned an alias. How do I
do
this? Thank you in advance for your help.

Good question.

The problem discussed in the article is certainly *not* with the concept
of
lookup tables, nor foreign key fields in your table. And, it is perfectly
appropriate to use a combo on your form to interface many of these
foreign
key fields.

The problem is with:
a) Putting a combo in the table. This causes no end of confusion for
users
about what is really stored in the table, and therefore now to filter on
the
field (actually behaves differently in different versions of Access),
apply
a form's filter to a report (requires special aliasing as per your
example
name), or even do to basic things like trying to build a filter string or
a
SQL statement.

b) The actual implementation of the Lookup Wizard: spurious indexes,
unnecessary hidden fields, and so on.

Any decent database is going to have lookup tables, probably lots of
them.

 

[legere864]

You're the man, Mr. Brown. I will try that out.

One last question, currently I am an intermediate access user at best, if I
want to become an advanced user, are there books and/or courses you can
recommend? Thanks.

Create a query that uses both the main table where your data is for the
report and also the Payee# table.

Right-click the Payee# table in the upper pane of the query design window,
and choose Properties.

Beside the Alias property enter the name that matches what Access used in
your form, i.e.:
Lookup_Payee#
In that query, the table is now aliased with that name.

Use that query as the RecordSource for your report, and it will be able to
understand the filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for clearing that up for me. I never create fields using the
Lookup wizard in Tables. Now, about the lookup fields in my report that I
want to filter. I have tried using a popup form to filter on these
fields,
but it does not work. I have tried filtering the form the report is based
on
and exporting the filter string to the report, and it works except the
field
is blank. When I go to the report and use the exported string,
((([Lookup_Payee#].PayeeName="Example Name"))), Access throws the
following
error "Microsoft can't find the Payee# referred to in your expression.
The
name has been changed ... etc." When I select OK, the report runs and is
filtered, but again the field is blank. You mentioned an alias. How do I
do
this? Thank you in advance for your help.

Good question.

The problem discussed in the article is certainly *not* with the concept
of
lookup tables, nor foreign key fields in your table. And, it is perfectly
appropriate to use a combo on your form to interface many of these
foreign
key fields.

The problem is with:
a) Putting a combo in the table. This causes no end of confusion for
users
about what is really stored in the table, and therefore now to filter on
the
field (actually behaves differently in different versions of Access),
apply
a form's filter to a report (requires special aliasing as per your
example
name), or even do to basic things like trying to build a filter string or
a
SQL statement.

b) The actual implementation of the Lookup Wizard: spurious indexes,
unnecessary hidden fields, and so on.

Any decent database is going to have lookup tables, probably lots of
them.

I give. I have read the evils of Lookup Fields at
http://www.mvps.org/access/lookupfields.htm. I am curious whether this
evil
only pertains to lookup fields at it relates to tables or all lookup
fields.
How else do I have a combo box that draws its data from another table
without
the lookup field? Maybe I am misunderstanding the term. I am
currently
having difficulty with a popup form that filters a report because I
have
two
combo boxes that lookup fields from other tables and I cannot get my
filter
on those fields to work. Is this considered a lookup field? Is
there
another way? I receive a filter that contains the following:
(Lookup_FieldName). I hope you can clear this up for me.

 

[Allen Browne]

The classic for many develoepers has been the Microsoft Access xxxx
Developers Handbook by Ken Getz et al, published by Sybex. (xxxx is the
Access version number.) Just volumne 1 should do if you are working working
with native Access (JET) tables.

I also teach from Microsoft Office Access 2003 by John Viescas (Microsoft
Press, 2005).

Alison Balter's style is also easy to follow, and she had some video courses
out as well.

Others might suggest their favorite authors as well.

 

[legere864]

Thanks for the information, I truly appreciate it. I will get those books
right away.

Back to my problem briefly. I can't get the alias to work. I have aliased
the table that has the foreign keys and I have aliased the table that has the
actual data. Neither works. I am aliasing the table as Lookup_Payee#. That
is what access calls it in the filter:
((([Lookup_Payee#].PayeeName="FirstName LastName"))) where FirstName and
LastName are the actual payee being filtered. What am I doing wrong? Do you
need more info?

 

[Allen Browne]

It is the table that has the PayeeName field you need to alias.

After doing that in query design, type some name into the Criteria row under
the PayeeName field, e.g.:
John Smith

Then switch the query to SQL view (View menu.)
In the query statement, you should see:
WHERE [Lookup_Payee#].PayeeName = "John Smith"

If the WHERE clause matches exactly what you have in the Filter property of
your form, the query should work. If not, copy the SQL statement and paste
it here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the information, I truly appreciate it. I will get those books
right away.

Back to my problem briefly. I can't get the alias to work. I have
aliased
the table that has the foreign keys and I have aliased the table that has
the
actual data. Neither works. I am aliasing the table as Lookup_Payee#.
That
is what access calls it in the filter:
((([Lookup_Payee#].PayeeName="FirstName LastName"))) where FirstName and
LastName are the actual payee being filtered. What am I doing wrong? Do
you
need more info?

The classic for many develoepers has been the Microsoft Access xxxx
Developers Handbook by Ken Getz et al, published by Sybex. (xxxx is the
Access version number.) Just volumne 1 should do if you are working
working
with native Access (JET) tables.

I also teach from Microsoft Office Access 2003 by John Viescas (Microsoft
Press, 2005).

Alison Balter's style is also easy to follow, and she had some video
courses
out as well.

Others might suggest their favorite authors as well.

 

[legere864]

I don't even want to say why it didn't work. Sometimes I surprise myself by
how careless I can be. Thank you, I am in business. It works.

It is the table that has the PayeeName field you need to alias.

After doing that in query design, type some name into the Criteria row under
the PayeeName field, e.g.:
John Smith

Then switch the query to SQL view (View menu.)
In the query statement, you should see:
WHERE [Lookup_Payee#].PayeeName = "John Smith"

If the WHERE clause matches exactly what you have in the Filter property of
your form, the query should work. If not, copy the SQL statement and paste
it here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the information, I truly appreciate it. I will get those books
right away.

Back to my problem briefly. I can't get the alias to work. I have
aliased
the table that has the foreign keys and I have aliased the table that has
the
actual data. Neither works. I am aliasing the table as Lookup_Payee#.
That
is what access calls it in the filter:
((([Lookup_Payee#].PayeeName="FirstName LastName"))) where FirstName and
LastName are the actual payee being filtered. What am I doing wrong? Do
you
need more info?

The classic for many develoepers has been the Microsoft Access xxxx
Developers Handbook by Ken Getz et al, published by Sybex. (xxxx is the
Access version number.) Just volumne 1 should do if you are working
working
with native Access (JET) tables.

I also teach from Microsoft Office Access 2003 by John Viescas (Microsoft
Press, 2005).

Alison Balter's style is also easy to follow, and she had some video
courses
out as well.

Others might suggest their favorite authors as well.

You're the man, Mr. Brown. I will try that out.

One last question, currently I am an intermediate access user at best,
if
I
want to become an advanced user, are there books and/or courses you can
recommend? Thanks.