Alternative to lookup needed

[bws93222]

I want to avoid using lookup (wizard) to pull data from other tables in my
combo box. But what exactly do you use as an alternative when you want to
establish a relationship which enforces referential integrity and cacscades
updates? Thx.

 

[Douglas J. Steele]

You use combo boxes, bound to the table fields, on forms. You should always
be using a form anyhow: tables aren't intended to be user interfaces.

 

[Larry Linson]

Lookup _Fields_, as created by the Wizard, obscure what is actually
stored... they were added only recently.

A better approach is what we have always used, instead of a Lookup Field,
use your own lookup Table... that is, you create a related lookup Table,
with a "foreign key" in the main table which will contain the value of the
primary key of the related lookup Table, open the Relationships window (in
Access 2003 and earlier, on the menu Tools | Relationships), add both Tables
(or whichever one is not already shown in the Relationship diagram) drag
from the primary key of the referenced (lookup) Table to the foreign key
field of the referencing table, then click on the join line and set the
details of the relationship.

Larry Linson
Microsoft Office Access MVP

I want to avoid using lookup (wizard) to pull data from other tables in my
combo box. But what exactly do you use as an alternative when you want to
establish a relationship which enforces referential integrity and
cacscades
updates? Thx.
--
bws93222

__________ Information from ESET Smart Security, version of virus
signature database 3990 (20090406) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 3990 (20090406) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[bws93222]

Not sure I understand, Douglas--can you elaborate? I have a tblOrders and a
tblCustomers (and I want to have a combobox in orders display Customers).
Are you saying I need a third table? How are the related?

 

[bws93222]

....I wrote "Douglas" but I meant 'Larry'

 

[John W. Vinson]

Not sure I understand, Douglas--can you elaborate? I have a tblOrders and a
tblCustomers (and I want to have a combobox in orders display Customers).
Are you saying I need a third table? How are the related?

All you need for the Customers - Orders relationship is a one to many
relationship. You can open the Relationships window in table design view - the
icon looks like three little squares with lines joining them. Add the
Customers table and the Orders table to the window; drag the CustomerID from
Customers to the CustomerID field in Orders. Access will pop up a window for
the properties of the relationship. Click "Cascade Deletes" if you are *quite
absolutely certain* that you want to be able to permanently and irrevokably
destroy all information about an order when you delete a customer - hint: you
probably DON'T. Do check the "Enforce Referential Integrity" checkbox. The
join line should turn to an arrow with the arrowhead by the "many" table.

You can then (well, you already could!) put a Combo Box based on tblCustomers
on your Orders form, bound to the CustomerID.

 

[bws93222]

My tblCustomerNames table has two fields: ID and CustomerName. My tblOrders
table has three fields: ID, OrderDetails, and CustomerName. In Relationship
View, I've tried dragging every conceivable combination of fields from
tblCustomerNames to tblOrder but it wont allow me to check the
integrity/cascade checkboxes--I'm obviously missing a critical step somewhere.
bws93222

 

[bws93222]

....the problem when I use lookup (or anything similar) is that the
CustomerName field in tblOrders is a numerical rather than text field--and I
want to be able to do a query-sort of CustomerNames in tblOrders that sorts
based on text and not the numerical ID. (I know I can just modify my query to
query the CustomerNames table but I want to be able to do it by refering only
to the tblOrders table)

 

[John W. Vinson]

...the problem when I use lookup (or anything similar) is that the
CustomerName field in tblOrders is a numerical rather than text field--and I
want to be able to do a query-sort of CustomerNames in tblOrders that sorts
based on text and not the numerical ID. (I know I can just modify my query to
query the CustomerNames table but I want to be able to do it by refering only
to the tblOrders table)

Ummm...

The name does not exist (and SHOULD not exist) in tblOrders.

Therefore you CANNOT search for it or sort by it in tblOrders.

It's a bit like the drunk looking under the streetlamp for the wallet he lost
a block away because the light is better there!

If you want to sort Orders by customer name you must - no choice, no option -
create a Query joining tblOrders to tblCustomer, and sort *that query*.

The (very ugly) alternative is to denormalize your database by storing
customer name information redundantly in tblOrders, but it should not be
necessary to do so.

 

[tina]

remove the CustomerName field from tblOrders. replace it with a field called
CustID; make sure the field's data type matches the ID field in
tblCustomerNames (hint: if the ID field is Autonumber data type, set the
data type of tblOrders.CustID to Number, field size Long Integer). in the
Relationships window, drag the ID field *from* tblCustomerNames *to* the
CustID field in tblOrders. the relationship should be automatically defined
as one-to-many. enforce Referential Integrity; and you can enable
CascadeUpdates - but, as John said, you probably don't really want to do
that.

this is obviously confusing to you, so i urge you to read up on relational
design principles. you need to understand what you're doing, and why, so you
can build your database properly and leverage the full power of Access. for
more information, see http://home.att.net/~california.db/tips.html#aTip1.
while you're there, read http://home.att.net/~california.db/tips.html#aTip4
and http://home.att.net/~california.db/tips.html#aTip8. those two should
help you understand some of what you've been told in this thread.

hth

 

[bws93222]

<<The name does not exist (and SHOULD not exist) in tblOrders>> If the list
of orders in tblOrders doesn't include the CustomerNames, then what good is
tblOrders? If you don't know who placed the order, how can you fill the
order or charge the customer?

 

[Larry Linson]

My tblCustomerNames table has two fields: ID and CustomerName. My
tblOrders
table has three fields: ID, OrderDetails, and CustomerName. In
Relationship
View, I've tried dragging every conceivable combination of fields from
tblCustomerNames to tblOrder but it wont allow me to check the
integrity/cascade checkboxes--I'm obviously missing a critical step
somewhere.
bws93222

Is Customer Name in the tblOrders the Lookup Field? (That's certainly what
it sounds like.) If so, you are seeing Customer Name, but it is probably
storing the ID for that Customer Name. If it is, _delete_ that field and
replace it with a Long Integer Field which you will populate from a Combo
Box, with a two-column Row Source, ID and Customer Name. You will store the
ID, and when you want to use the tblOrders, to show the Customer Name,
you'll retrieve the information via a Query that includes both Tables.

It is not necessary to have all the information in the same Table in a
relational database, because you can create Queries to join the related,
normalized Tables and extract the needed information from multiple tables.

I avoid using identical Field Names (e.g., "ID") in different tables because
it can make it difficult when you are going back and trying to follow what
you did, when trying to correct or modify the database.

Larry Linson
Microsoft Office Access MVP



__________ Information from ESET Smart Security, version of virus signature database 3991 (20090407) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[John W. Vinson]

<<The name does not exist (and SHOULD not exist) in tblOrders>> If the list
of orders in tblOrders doesn't include the CustomerNames, then what good is
tblOrders? If you don't know who placed the order, how can you fill the
order or charge the customer?

You're using a relational database.

This is HOW RELATIONAL DATABASES WORK.

tblOrders contains a CustomerID, a numeric *LINK* to CustomerNames.

You use a Query - the absolute bedrock essential feature of any relational
database - to *relate* - *link* - to CustomerNames in order to find out the
name. That's precisely what a Lookup Field is: a concealed query, linking to
the CustomerNames table.

If you're assuming that all the information you'll ever need must be jammed
into the same table, you're simply not correctly understanding how databases
work.

 

[bws93222]

Thanks everyone, I think I'm getting it now. Leaving aside for now the best
practice idea of using only forms to change data, here is what y'all are
saying (correct me if I'm wrong)
-- Lookups should not be used and there is no need to have customer names
displayed in the orders table--only the customer ID (key) needs to be there.
Unless I want to create some totally unnecessary (non-lookup) customer query
WITHIN the orders table, the recommended standard way to see both order and
customer info is via a separate standalone query.
--2 Remaining questions: 1) Assuming I follow the steps above so that
customers' IDs and NOT the customer names are displayed in the orders table,
is it okay to use the lookup wizard to implement this?--Or does the mere use
of the lookup wizard even in its most limited aspect, cause other unmentioned
problems that make it still an undesirable tool? 2) Larry, in using the
2-column approach you mentioned above, this still causes only the customer ID
(key) to be stored in the orders table--the only advantage here would be that
should I decide to use the dropdowm list in the orders combobox, it would
also show me the customer names associated with the customer IDs, correct?

Thx again everyone for all your help!

bws93222

 

[Clif McIrvin]

I'll jump in here and see if I can help out by attempting to clarify
some terms:

Thanks everyone, I think I'm getting it now. Leaving aside for now
the best
practice idea of using only forms to change data, here is what y'all
are
saying (correct me if I'm wrong)

It's going to be difficult to "lay this aside", as you'll see as I
answer various points below.

-- Lookups should not be used and there is no need to have customer
names
displayed in the orders table--only the customer ID (key) needs to be
there.

You use the word displayed here which to my way of thinking muddies the
waters, so to speak. The customer ID (foreign key, or FK) is *stored* in
the orders table; assuming that this key is a meaningless number useful
only to the JET database engine you will *always* display the related
customer name when exposing the order information through the user
interface.

Also, *lookups* (at least, the function of lookup) is far too useful to
say "Lookups should not be used." Again, this may be a matter of
semantics, but it is *Lookup Fields* that should not be used.

Unless I want to create some totally unnecessary (non-lookup) customer
query
WITHIN the orders table, the recommended standard way to see both
order and
customer info is via a separate standalone query.

Whoa --- I'm not at all certain that I even follow what you just said
there.

Your Customers table and Orders tables are linked in the relationships
window in a one to many relationship on customer ID, correct? This join
'automagically' connects the customer name with the order ... all you
need to do when building the Query that will become the recordsource for
your form or report is to include the customer name field as one of the
columns in your query. The JET engine uses the defined relationship join
to know which customer name to retrieve.

There is no "separate standalone query" involved --- merely the query
that defines the fields that make up the order information.

--2 Remaining questions: 1) Assuming I follow the steps above so that
customers' IDs and NOT the customer names are displayed in the orders
table,
is it okay to use the lookup wizard to implement this?--Or does the
mere use
of the lookup wizard even in its most limited aspect, cause other
unmentioned
problems that make it still an undesirable tool?

I have never used the lookup wizard ... but see above for my answer to
this question.

2) Larry, in using the
2-column approach you mentioned above, this still causes only the
customer ID
(key) to be stored in the orders table--the only advantage here would
be that
should I decide to use the dropdowm list in the orders combobox, it
would
also show me the customer names associated with the customer IDs,
correct?

The *bound* column is the column stored in the table. By setting a
column width in the control properties for your combo box you can make
that column invisible.

Thx again everyone for all your help!

You're welcome!

HTH

 

[John W. Vinson]

Thanks everyone, I think I'm getting it now. Leaving aside for now the best
practice idea of using only forms to change data, here is what y'all are
saying (correct me if I'm wrong)
-- Lookups should not be used and there is no need to have customer names
displayed in the orders table--only the customer ID (key) needs to be there.
Unless I want to create some totally unnecessary (non-lookup) customer query
WITHIN the orders table, the recommended standard way to see both order and
customer info is via a separate standalone query.

Well... you can't have a "customer query WITHIN the orders table".

A Query is constructed of tables - not vice versa.

--2 Remaining questions: 1) Assuming I follow the steps above so that
customers' IDs and NOT the customer names are displayed in the orders table,
is it okay to use the lookup wizard to implement this?--Or does the mere use
of the lookup wizard even in its most limited aspect, cause other unmentioned
problems that make it still an undesirable tool? 2) Larry, in using the
2-column approach you mentioned above, this still causes only the customer ID
(key) to be stored in the orders table--the only advantage here would be that
should I decide to use the dropdowm list in the orders combobox, it would
also show me the customer names associated with the customer IDs, correct?

You're apparently still assuming that you need to open the table to work with
data.

That assumption IS WRONG.

Tables have only one purpose: to store data. Users should *NEVER* see table
datasheets, period! Even you should only look at table datasheets when you're
debugging.

The *ONLY* beneifit of Lookup Fields and Subdatasheets is to make it easier to
violate this rule and to work in table datasheets. Since you shouldn't be
working in table datasheets in the first place, they lose most of their
rationale. It's really easy to create basic forms; all interaction with data
should be done on a Form. And of course, on the form the user will see a combo
box which *displays* the customer name, but *stores* the customerID.

 

[Larry Linson]

John and Clif have answered your questions.

I emphasize especially what John said... Lookup Fields and Subdatasheets
only make it somewhat easier to violate the basic rule that you should not
be working with your data via the Table itself. By obscuring what is
actually stored in the record, Lookup Fields make just about everything else
you do with the record far less easy and far less understandable. You should
create Forms to work with the data... the Forms can use the Table as the
Record Source, but often it is simpler to use a Query as the Record Source
of the Form.

Larry Linson
Microsoft Office Access MVP



__________ Information from ESET Smart Security, version of virus signature database 3994 (20090407) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[bws93222]

OK, I understand fully. To put it succinctly: lookups are bad only when used
in TABLES because they contaminate the tables with queries and other non-data
elements. Lookups are fine for forms.

 

[John W. Vinson]

OK, I understand fully. To put it succinctly: lookups are bad only when used
in TABLES because they contaminate the tables with queries and other non-data
elements. Lookups are fine for forms.

Absolutely.