In article <[log in to unmask]>, Stuart Holm
<[log in to unmask]> writes
>a)  Assigned number - The accession number as originally allocated
>(exactly as it appears in the accession register, in other contemporary
>documentation or marked on the object itself).
>
>b)  Record number - The accession number in a "normalised" form,
>analysed into its components (as in Mick Cooper's example) and then
>reconstructed in a consistent logical sequence.  This is the form of
>number which is used for sorting, etc. in the information management
>system.


Our current database (in MS Access 97) does the analytical work for us.
After an accession number is added or edited in the Object ID field a
few lines of code breaks it into its component parts and updates the
analysis fields automatically. These analysed fields have several uses.

They enable sorting as described, but also have another vital use in our
inventory auditing. We're just winding up the reconciliation phase of a
massive project to database all our inventory records. The first part of
this was recording everything we *have*. The second part was comparing
this with everything we *should* have. We did the groundwork for the
latter by making a table containing all the accession numbers in the
collection registers. This was a relatively simple process using an
Excel spreadsheet to create numbers and analysed fields automatically
for whole years at a time by just keying in the year and letting Excel
functions do the rest. The results were then pasted into Access to give
us a basic list of all the Object IDs that *should* exist in the
Inventory.

This Register list was then compared with the Inventory list using
queries in Access. The first pass was to link the Object ID fields
together which gave us all the matches with complete Object IDs (i.e.
where the Register number was in exactly the same form as the Inventory
number). Subsequent passes linked the *analysed* fields in both tables
to give subtler matches. For example, in the first pass an original
Register entry of NCM 1890-123/(a) (the old style of number) wouldn't
match the Inventory entry for NCM 1890-123/1 (the modern standardised
method) and would appear as missing. But in the seond pass, which linked
museum code-museum code, year-year, and item-item, it would appear.

The queries gave us tables with columns containing the Register number
and others showing the matches. Missing numbers would show as gaps in
the match columns and could then be easily isolated. The missing objects
were then tracked down in the registers. From these we made lists of
wanted items for circulation to curators.

Other queries were used to find numbers extra to the expected series,
duplicated numbers, etc. So useful is this method that my assistant has
now begun to dread the words: "I've just thought of another query to run
on the database..." In this way we've so far resolved almost 50% of
3,000 problem numbers, many of which appear to have been knocking about
in our manual systems un-remarked for decades!

Sorry for the off-topic essay, it's been a long week.

Mick
Registrar, Nottingham Museums


--
Michael P. Cooper * Mineralist * [log in to unmask]