Assign Unique IDs to Multiple Tags

[willbmisled]

Hello, I work with Mark and Recapture data for animals. I have a situation where Individuals can receive more than one Identification tag. The number of animals is large (>70,000) and the field technicians do not always keep good records. So I have a list of individuals each with at least one identification number assigned to it. If an animal has more than one tag visible both tag numbers are recorded. I'd like to use an SQL query to read the list of tag numbers and assign unique ID's to each individual. For example I might have this table with two columns:

Tag1, Tag2
782, NULL
67, NULL
8, 67
254, 67
8, 903


8, 254
254, 995

This represents two individuals; one has a single tag and the other was given 5 different tags over the course of its life. I'd like to use a query to generate a new two column table that links each tag to a unique ID for the individual. Here is what is will look like this:

ID,Tag
1, 782
2, 8
2, 67
2, 254
2, 903
2, 995

Is this something that can be done with a query? Any help will be appreciated.

[jzwp11]

What you want to do can be done with a query but it hinges on how you set up your tables. Since a particular animal can have many tags, you have a one(animal)-to-many(tags) relationship which would be structured as follows

tblAnimals
-pkAnimalID primary key, autonumber (this would function as your uniqueID)
-other fields associated with the animal (i.e. species, gender etc.)

tblAnimalTags
-pkAnimalTagID primary key, autonumber
-fkAnimalID foreign key to tblAnimals (provides a join between the two tables)
-TagNo