1

Hello and thanks in advance.

I am creating a DB that contains a number of individual animals and want a unique ID for each row (each individual) to follow a specific format. It should begin with the two letter abbreviation of the species (either MT or BH), a sequential number that is different for each species (i.e. each species starts at 001 and counts sequentially by 1), and the sex of the animal(i.e. M or F).

For example, the first BH would read BH001M. The second female would read BH002F.

When entering a new record I envision a pop up window that requests the species and then the sex of the new individual and then Access would generate the sequential number.

I realize this is a specific question without any data attached, but would appreciate any suggestions on first steps to take.... I.e. is this possible thorough a macro, or do I need to work in VB?

EDIT: In addition to the unique ID, I will also have separate fields for Species and Sex, but also want the unique ID to include the info as well. Having Species and Sex incorporated into the ID will help to identify important quickly.

Per the comment below @OverMind, including a single number is a good idea.

The header of my Access table looks like:

tbl

From the table, I created a form (below) enter image description here

I wanted to hit the "Add new Animal" button and then have the popup windows as mentioned above. The data to be entered (only a few records currently) are in an excel spreadsheet, but there will be many more (a few 100) individuals that will need to be entered in the future.

I am in Access 2013

Thanks.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
B. Davis
  • 3,391
  • 5
  • 42
  • 78
  • It is never a good idea to create "intelligent" numbers, especially to form primary keys. You should use two separate fields for species and sex and if you must save the sequential number, calculate it when records are added. Use an auto-number field as the unique identifier / primary key. keep in mind that if you save the sequential number and then delete a record, you will have gaps in the table and the MAX(species) will not reflect the correct number. – ron tornambe Aug 28 '14 at 17:43
  • 3
    Where are you getting MT or BH from? This is possible but it all depends on how you want it to work. It could also depend on your knowledge of VBA. Side note: I would have 1 autonumber field as well as your BH###MF unique ID. – Mark C. Aug 28 '14 at 17:43
  • @OverMind. See edits which include your helpful info and more information. – B. Davis Aug 28 '14 at 18:39
  • @B.Davis Thanks. Is there already an `Animal` table? Or where is the user getting this list of animals from? Perhaps you can show some sample table / a sample schema? – Mark C. Aug 28 '14 at 18:41
  • @OverMind. More added above. – B. Davis Aug 28 '14 at 19:16
  • This is going to look really messy and I don't think it's going to work the way you are imagining.. For example, just getting the abbreviations for the animal. How do you foresee this happening? If the user can enter in the species abbreviation and sex, you can develop your unique ID using that information. – Mark C. Aug 28 '14 at 19:29
  • It looks like `IndID` is an AutoNumber field, so it can serve as your actual Primary Key for that table. Once you've created your composite "unique ID", in what field do you plan to store it? – Gord Thompson Aug 28 '14 at 19:57
  • @OverMind. Yes, the user would enter the species abbreviation and sex, the number would be assigned, and then the remaining data would be filled in. That was my hope anyway... – B. Davis Aug 28 '14 at 19:58
  • Are you familiar with VBA? We can use the Left function to determine if there are currently records in the table for that species. We can use the Mid function, if the first queries returns results, to determine the Max of Mid(yourField, 3, 3).. This will give us the 001, 002 etc. and concatenation the gender to formulate this ID. Will try to post more when I'm not on my phone. Sorry – Mark C. Aug 28 '14 at 20:29

1 Answers1

2

If you wanted to save the unique ID in a Short Text field named [UniqueID] then you could use a Before Change data macro on the table, like this:

DataMacro.png

For more information on Data Macros see

Create a data macro

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I stay away from Macros, but I think this is the exact route I was going to suggest in VBA, except perform a check based off of if the species abbreviation already exists, if so, create a query to filter using that species abbreviation and get the max of the mid (the #'s) and increment by 1. – Mark C. Aug 28 '14 at 21:14
  • @Gord Thompson Thanks or the helpful macro. However, I keep getting an error within the Look Up A Rec... row when I begin typing SELECT. Any thoughts... – B. Davis Aug 28 '14 at 23:34
  • @B.Davis If you are having difficulty entering or editing the macro then [this question](http://stackoverflow.com/q/20661510/2144390) might help. (TL-DR: You need to open the table in *Datasheet* view, then click the "Before Change" button on the ribbon bar. If you open the table in *Design* View then Access may choke on the SELECT statement.) – Gord Thompson Aug 29 '14 at 10:51