Alternatives to Lookup Fields

[John Baker]

Hi:

I have a general question about table access and look ups.

Over simplifying things,and using a very theoretical example, lets say I have two tables;
Client and Bill.

The client table has a unique auto number (which is indexed) and the Client name, as well
as address and other key data

I wish to record a key in the Bill Table that will link it to a unique record in the
Client Name Table, so that I can use data in that record on a report along with the Bill
Table data.

A complication is that some of the some clients change their names over time, and I can
not be certain from one day to the next that a given client will have the same name. They
also move frequently (one step ahead of the law?), so nothing in the Client Record is
consistent except the original auto number field.

My approach has been to put a lookup field in the Bill table, looking up on the client
name, but store the Client Key (the table auto number), so that I can consistently access
the name regardless of what it is. I know that this is not the recommended approach, and
that I should (according to the Gods of Databases) use the client name itself as the key
BUT that presents a problem when the client changes there name, since changing the client
name in the client table will destroy the link if I have used the name in the Bill Table..

I would appreciate some comment on this paradox, and suggestions for ways around it that
make the system more effective and easier to maintain that using a lookup approach.

Best

John Baker

[Rick Brandt]

"John Baker" <Ba******@Verizon.net> wrote in message
news:tv********************************@4ax.com...

Hi:

I have a general question about table access and look ups.

Over simplifying things,and using a very theoretical example, lets say I have
two tables;
Client and Bill.

The client table has a unique auto number (which is indexed) and the Client
name, as well
as address and other key data

I wish to record a key in the Bill Table that will link it to a unique record
in the
Client Name Table, so that I can use data in that record on a report along
with the Bill
Table data.

A complication is that some of the some clients change their names over time,
and I can
not be certain from one day to the next that a given client will have the same
name. They
also move frequently (one step ahead of the law?), so nothing in the Client
Record is
consistent except the original auto number field.

My approach has been to put a lookup field in the Bill table, looking up on
the client
name, but store the Client Key (the table auto number), so that I can
consistently access
the name regardless of what it is. I know that this is not the recommended
approach, and
that I should (according to the Gods of Databases) use the client name itself
as the key

[snip]

You've completely misunderstood the "Gods of Databases". Storing a key and
displaying the name is EXACTLY what you should do according to proper design
principles. This does NOT however require the use of a lookup field. You
simply use a ComboBox on a FORM to do the same exact thing.

The basic "issue" with lookup fields is that they provide an interface when
viewing a table that should more appropriately be provided by a form. Since
tables should never be viewed directly except by the developer during debugging
there is no reason to (in a table) to store one thing while displaying another.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[John Baker]

Rick:

Yes your right I did misunderstand.

The point, then, is that I should use a Combo Box on the form, and then set the Como Box
to save the Autonumber key for the Client name in the Bill record.

Right?

Best and thanks
John
"Rick Brandt" <ri*********@hotmail.com> wrote:

"John Baker" <Ba******@Verizon.net> wrote in message
news:tv********************************@4ax.com.. .

Hi:

I have a general question about table access and look ups.

Over simplifying things,and using a very theoretical example, lets say I have
two tables;
Client and Bill.

The client table has a unique auto number (which is indexed) and the Client
name, as well
as address and other key data

I wish to record a key in the Bill Table that will link it to a unique record
in the
Client Name Table, so that I can use data in that record on a report along
with the Bill
Table data.

A complication is that some of the some clients change their names over time,
and I can
not be certain from one day to the next that a given client will have the same
name. They
also move frequently (one step ahead of the law?), so nothing in the Client
Record is
consistent except the original auto number field.

My approach has been to put a lookup field in the Bill table, looking up on
the client
name, but store the Client Key (the table auto number), so that I can
consistently access
the name regardless of what it is. I know that this is not the recommended
approach, and
that I should (according to the Gods of Databases) use the client name itself
as the key

[snip]

You've completely misunderstood the "Gods of Databases". Storing a key and
displaying the name is EXACTLY what you should do according to proper design
principles. This does NOT however require the use of a lookup field. You
simply use a ComboBox on a FORM to do the same exact thing.

The basic "issue" with lookup fields is that they provide an interface when
viewing a table that should more appropriately be provided by a form. Since
tables should never be viewed directly except by the developer during debugging
there is no reason to (in a table) to store one thing while displaying another.

[Rick Brandt]

"John Baker" <Ba******@Verizon.net> wrote in message
news:4p********************************@4ax.com...

Rick:

Yes your right I did misunderstand.

The point, then, is that I should use a Combo Box on the form, and then set the Como Box to save the Autonumber key for the Client name in the Bill record.

Right?

Exactly.

Just to add... your original post also seemed to stray into the "AutoNumber
versus Natural Key" debate which is really a different subject. You can
use the strategy of storing the key while displaying something else when
using either a Natural Key or a Surrogate Key (like AutoNumber). While
that debate is largely religious at this point, I agree that when the best
natural key candidates are subject to being changed or when it requires
multiple fields to generate a Natural Key that I tend to prefer a
Surrogate.

Of course, it is also true that a Surrogate Key need not be an AutoNumber.
Account Numbers, Order Numbers, Invoice Numbers, etc., are all surrogates
of a fashion.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Bernard Peek]

In message <tv********************************@4ax.com>, John Baker
<Ba******@Verizon.net> writes

Hi:

I have a general question about table access and look ups.

Over simplifying things,and using a very theoretical example, lets say
I have two tables;
Client and Bill.

The client table has a unique auto number (which is indexed) and the
Client name, as well
as address and other key data

I wish to record a key in the Bill Table that will link it to a unique
record in the
Client Name Table, so that I can use data in that record on a report
along with the Bill
Table data.

A complication is that some of the some clients change their names
over time, and I can
not be certain from one day to the next that a given client will have
the same name. They
also move frequently (one step ahead of the law?), so nothing in the
Client Record is
consistent except the original auto number field.

My approach has been to put a lookup field in the Bill table, looking
up on the client
name, but store the Client Key (the table auto number), so that I can
consistently access
the name regardless of what it is. I know that this is not the
recommended approach, and
that I should (according to the Gods of Databases) use the client name
itself as the key
BUT that presents a problem when the client changes there name, since
changing the client
name in the client table will destroy the link if I have used the name
in the Bill Table..

I'm a strong advocate of natural keys, and I've often put that view
forward in comp.databases.theory so perhaps I'm the sort of person you
think is a god of databases. I can assure you that the gods often
disagree about natural versus surrogate keys.

I would suggest that you use a natural key if there is one available. If
you don't have a suitable natural key then a surrogate, like your
autonumber, is the best you can do.

The important thing to realise is that adding a surrogate key doesn't
solve the fundamental problem, that you have no natural key available.
What you need to do is to add some data processing systems that will
ensure that there is always a 1:1 match between real companies and the
autonumber field. You can't do this in software, someone needs to watch
out for companies changing their name and then make the changes in the
data.

You can make that easier by giving each company an account number, and
asking them to quote it on any correspondence, but don't trust them to
do that. Check both the customer name and the account number on
paperwork. Check the zipcode too, because it's unlikely that a customer
will change to a new address with the same zip (but it's not impossible
either.)

One important thing to note is that autonumbers aren't a good choice for
account-numbers. Once you issue an account-number to a customer you
should never change it. You probably don't need to do any arithmetic on
the account-number, so make it a text field - even if it only contains
digits. It's best to build your own key, and I strongly advise against
trying to embed any fancy coding system into it. Don't try using the
customer's name as part of the code, because if the customer changes
their name people may not find the old number and will issue a new one.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Ron]

Why NOT use an autonumber as an account number? What are the problems with
doing so? And if severe, how can a unique account number be generating--I
thought that was what the "autonumber" was for? I seek enlightenment.

TIA
ron

"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:V4**************@shrdlu.com...
<snip>

One important thing to note is that autonumbers aren't a good choice for
account-numbers. Once you issue an account-number to a customer you
should never change it. You probably don't need to do any arithmetic on
the account-number, so make it a text field - even if it only contains
digits. It's best to build your own key, and I strongly advise against
trying to embed any fancy coding system into it. Don't try using the
customer's name as part of the code, because if the customer changes
their name people may not find the old number and will issue a new one.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[John Baker]

Thank you all for your observations, they are most helpful and give me some perspective on
the issues and practical solutions.

Best

John

[NeilAnderson]

I always use an autonumber as a primary key. If I need something that
is going to be seen i.e. an account number, or invoice number I add it
using dmax(invoice) + 1 on a form's before insert event. That way I
get the best of both worlds!

Neil Anderson

[Ron]

Thanks for your response.

But my question is...Why is it necessary to HAVE 2 worlds so that I can have
the best of both of them? I'd much rather have just one if I can--the KISS
theory.

If I've already got a unique, autonumbered field that is incremented with
each new record, what's wrong with using that say as the customer number
also? It's already the primary key, and I'm going to use it as a foreign
key in the invoice file, right? Then, in the invoice file, I have a primary
key (autonumbered, incrementing) be it's invoice number and in the invoice
detail file I can use that as the foreign key linking the details (which
again all have their own primary key that's autonumbered, incrementing) to
the invoice. Why do I need to come up with some other number such as your
dmax(invoice) + 1 to be seen by the users? Why can't I just show 'em the
primary key as the account number/invoice number,etc?

I've seen hints here that using this autonumbered pk for this purpose is
not a good idea, but I don't understand WHY it's not a good idea. Can
anyone explain it?

In a fog...
ron

"NeilAnderson" <ne***********@boroughmuir.edin.sch.uk> wrote in message
news:83**************************@posting.google.c om...

I always use an autonumber as a primary key. If I need something that
is going to be seen i.e. an account number, or invoice number I add it
using dmax(invoice) + 1 on a form's before insert event. That way I
get the best of both worlds!

Neil Anderson

[Rick Brandt]

"Ron" <ro*******************@earthlink.com> wrote in message
news:Ea*******************@newsread1.news.pas.eart hlink.net...

Thanks for your response.

But my question is...Why is it necessary to HAVE 2 worlds so that I can have the best of both of them? I'd much rather have just one if I can--the KISS theory.

If I've already got a unique, autonumbered field that is incremented with
each new record, what's wrong with using that say as the customer number
also? It's already the primary key, and I'm going to use it as a foreign
key in the invoice file, right? Then, in the invoice file, I have a primary key (autonumbered, incrementing) be it's invoice number and in the invoice detail file I can use that as the foreign key linking the details (which
again all have their own primary key that's autonumbered, incrementing) to the invoice. Why do I need to come up with some other number such as your dmax(invoice) + 1 to be seen by the users? Why can't I just show 'em the
primary key as the account number/invoice number,etc?

I've seen hints here that using this autonumbered pk for this purpose is
not a good idea, but I don't understand WHY it's not a good idea. Can
anyone explain it?

The issue is that some users might think that the number represents an
unbroken ordinal sequence (even if you don't describe it that way). As
long as everyone is content with "This is just a number we've assigned to
each Customer and there will be numbers that do not exist", then an
AutoNumber is likely not a problem.

In some accounting systems there can by rules (or laws) that disallow (an
Invoice number for example) to have gaps in the number sequence. Invoices
can be cancelled or voided, but all of the numbers have to be accounted for
(pun intended). In those situations an AutoNumber cannot be used.

I have a few cases where I use an AutoNumber but use Random instead of
Incremental and display it to the user in HEX. This eliminates negative
signs and makes it obvious to everyone that the value is random and nobody
has any expectations about what the next one will be or why they can't find
any particular one.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Ron]

Thanks for your response, Rick.

See my new thread about negative PKs--hey, you brought it up! ::grin::

ron

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:30*************@uni-berlin.de...

"Ron" <ro*******************@earthlink.com> wrote in message
news:Ea*******************@newsread1.news.pas.eart hlink.net...

Thanks for your response.

But my question is...Why is it necessary to HAVE 2 worlds so that I can have

the best of both of them? I'd much rather have just one if I can--the

KISS

theory.

If I've already got a unique, autonumbered field that is incremented with each new record, what's wrong with using that say as the customer number
also? It's already the primary key, and I'm going to use it as a foreign key in the invoice file, right? Then, in the invoice file, I have a

primary

key (autonumbered, incrementing) be it's invoice number and in the

invoice

detail file I can use that as the foreign key linking the details (which
again all have their own primary key that's autonumbered, incrementing)

to

the invoice. Why do I need to come up with some other number such as

your

dmax(invoice) + 1 to be seen by the users? Why can't I just show 'em the primary key as the account number/invoice number,etc?

I've seen hints here that using this autonumbered pk for this purpose is not a good idea, but I don't understand WHY it's not a good idea. Can
anyone explain it?

The issue is that some users might think that the number represents an
unbroken ordinal sequence (even if you don't describe it that way). As
long as everyone is content with "This is just a number we've assigned to
each Customer and there will be numbers that do not exist", then an
AutoNumber is likely not a problem.

In some accounting systems there can by rules (or laws) that disallow (an
Invoice number for example) to have gaps in the number sequence. Invoices
can be cancelled or voided, but all of the numbers have to be accounted

for (pun intended). In those situations an AutoNumber cannot be used.

I have a few cases where I use an AutoNumber but use Random instead of
Incremental and display it to the user in HEX. This eliminates negative
signs and makes it obvious to everyone that the value is random and nobody
has any expectations about what the next one will be or why they can't find any particular one.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Bernard Peek]

In message <Ea*******************@newsread1.news.pas.earthlin k.net>, Ron
<ro*******************@earthlink.com> writes

Thanks for your response.

But my question is...Why is it necessary to HAVE 2 worlds so that I can have
the best of both of them? I'd much rather have just one if I can--the KISS
theory.

If I've already got a unique, autonumbered field that is incremented with
each new record, what's wrong with using that say as the customer number
also?

Nothing, provided you can guarantee that the numbers will never change.
If you delete records and compact an Access database the autonumber
fields will be renumbered. You can build automatically incremented
sequences that don't have that failing, and they work pretty well as
long as you have systems in place to control the mapping between your
autonumber surrogate key and the real data.
--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Rick Brandt]

"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:PM**************@shrdlu.com...

In message <Ea*******************@newsread1.news.pas.earthlin k.net>, Ron
<ro*******************@earthlink.com> writes

Thanks for your response.

But my question is...Why is it necessary to HAVE 2 worlds so that I can have
the best of both of them? I'd much rather have just one if I can--the KISS
theory.

If I've already got a unique, autonumbered field that is incremented with
each new record, what's wrong with using that say as the customer number
also?

Nothing, provided you can guarantee that the numbers will never change. If you
delete records and compact an Access database the autonumber fields will be
renumbered. [SNIP]

Huh? AutoNumbers (in use) have never been renumbered by compacting and the
newer versions don't even reuse deleted values higher than the last AN in use
anymore. To reset the AutoNumbers in the newer versions you have to delete ALL
records before compacting.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Ron]

Whew!

Thanks for posting that correction, Rick. I was seeing my life flash before
my eyes! Broke out in a cold sweat and everything. Even if I don't show
'em to anyone, I still need that autonumber PK to remain unchanged. It can
be, after all, the link between files. To allow it to change would
definitely have caused some problems.

Hang in
ron

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:30*************@uni-berlin.de...

"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:PM**************@shrdlu.com...

In message <Ea*******************@newsread1.news.pas.earthlin k.net>, Ron
<ro*******************@earthlink.com> writes

Thanks for your response.

But my question is...Why is it necessary to HAVE 2 worlds so that I can havethe best of both of them? I'd much rather have just one if I can--the KISStheory.

If I've already got a unique, autonumbered field that is incremented witheach new record, what's wrong with using that say as the customer number
also?
Nothing, provided you can guarantee that the numbers will never change. If you delete records and compact an Access database the autonumber fields will be renumbered. [SNIP]

Huh? AutoNumbers (in use) have never been renumbered by compacting and

the newer versions don't even reuse deleted values higher than the last AN in use anymore. To reset the AutoNumbers in the newer versions you have to delete ALL records before compacting.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com