3 Replies Latest reply on May 18, 2015 9:04 PM by philmodjunk

    Help with counting unique alphanumeric codes in same field

    LKozuch

      Title

      Help with counting unique alphanumeric codes in same field

      Post

      Hello Forum - I am Curator of archaeological collections. We have a box inventory, and sometimes artifacts from many different sites in the same box. One record per box. Site numbers are alphanumeric, e.g., 11MO80 or 11S2258 (two-digit number, one or two letters, then a number). If multiple site numbers are in the same field, how do I count the number of unique site numbers in the database? I don't want to count a site number twice. I assume some type of IF THEN operator is involved, but I don't know what to do. Any help is appreciated. 

        • 1. Re: Help with counting unique alphanumeric codes in same field
          philmodjunk

          Sounds like you need to modify the design of your database to be something like this:

          Boxes----<Contents>-----Sites

          Boxes::BoxID = Contents::BoxID
          Sites::SiteID = Sites::SiteID

          Contents would be a related table that replaces your field with multiple site ID's with a table of related records with one record for each item found in the box.

          That single text field with multiple site numbers just isn't going to be something that you can easily work with. I suggest setting up the above tables (Sites is only needed if you need to record additional info about each site) and using a looping script to move the data from the current text field into a set of new records in the Contents table.

          • 2. Re: Help with counting unique alphanumeric codes in same field
            LKozuch

            Thanks. I understand. We do need additional information for the sites, and that would be the project information. Sometimes sites are excavated several times (for several projects, sometimes spanning 50 or more years), and there are three more fields for recording project information: 1) Project name, 2) Project number, and 3) Date excavated. Sometimes we only have a project name, sometimes only a project number (sometimes both), and sometimes we can only tell the date excavated by looking inside each box at the dates written on the bags holding the artifacts. We also have over 15,000 records, so modifying the design is untenable. Oh well, onward! Thanks again.smiley

            • 3. Re: Help with counting unique alphanumeric codes in same field
              philmodjunk

              Modifying the design should not be untenable from what you describe. A looping script can do most of the work needed to generate the new records in the new table(s). The fact that you have 15,000 records simply means that your looping script will take more time to loop through the data. (I've got a 3 million record table that I'm preparing to modify....)