[AccessD] Let's discuss lookup tables a bit more

Susan,

Great that you are getting on well with the project:

I think most of the responders are keen that you should avoid the pitfalls they
encountered in their early? Days. 

------------------------------- 

No particular problem that I can (currently) think of -
Just the regular considerations for selection lists: 

Easy to select the wrong one if there are multiple entries that start with the
same words
Manipulation of the entries should be by you doing maintenance, 
but by others using simple forms that do not include change or delete.
Include 'reports' that can be run (selected) by the user when they have used the
update form.

Also consider that changing the lookup value changes all the associated entries 
So when "Indiana zoo" becomes "Indiana Zoo Inc."
Do you want to change all the entries - considering that the new name may also
include different qualities, authorities etc. - such as being able to handle
restricted species 

It may be a good idea to include a 'no longer selectable' marker that will
exclude the marked entries from the selection list in appropriate forms.

That can be used to stop the use of  'errors' in the additions done by others,
who can email? you to have the erroneous ones deleted - after you check they
have not been used.

---------------------------------------------

Also consider  - maintenance - 
Include 'reports' that can be run (selected) by the user when they have used the
update form.
If you have to reconstruct the database it will help if you have flat files of
the content of various tables - or maybe export them to Excel sheets, or have
Excel load a set of worksheets/tables with the data from the database.
(From within Excel create .dqy files to SQL the data from the external
(database) data store.)

Once you take over a data management process, you are assumed to be the one
responsible for it, and the data in it! 

JimB
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Tuesday, September 02, 2014 2:34 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Let's discuss lookup tables a bit more

After our discussion on normalization I've simplified the table structure
in the animal tracking database quite a bit. I especially appreciated the
idea that all animals have birth parents -- they just won't always be
known. I've expanded that to cover institutions too -- all animals come
from an institution, just sometimes, it's our own (ie, they're born here).

But that brings me to an interesting possibility regarding the institution
table. There won't be very many because we work with just a few in our
region. I'm leaning toward thinking of the institution table as a lookup
table. At least, I can use it that way, even if, traditionally, it wouldn't
be. However, they'll need the ability to add institutions on the fly, but
that's no big deal.

Any warnings?

Thanks!
Susan H.
--