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]