How to key records that are incomplete at first

[Coaltown]

A previous question on this forum made me think to ask this.

I have a table that includes, among other things, the name, year of birth and country of origin of certain registered animals. When the record is first created, only the name is known. I don't want duplicates in my master animal table, so I keyed the name field and this worked fine for a while.

However, over time, names are occasionally reused so that to properly differentiate these animals, the YOB and origin should also be key values. However, this info is not available in the original source and would greatly slow down data entry to look up each one at the time.

Now I'm starting to have cases of new animals that should be added not being added because they have the same name as an existing animal, and I can't even add them manually because of course I can't have duplicate names. I can't change the key to embrace all the fields it should because many animals don't yet have data in these fields. Even if I stopped now and filled in this information on every existing animal, it couldn't add new ones, since that data isn't available in the original source. An autonumber as I know it will not help - it won't do the original job of preventing multiple records for each animal.

Any ideas?

 

[fneily]

Create an exact copy of your table, structure only. An AnimalInfo2. Use this table as your "work" table - as data comes in, you add the information here. Then when a record is filled, create a query to pick off that record. Run the query as normal. THIS IS TO TEST TO SEE IF THE PICKOFF IS CORRECT. Then go back to design view and change it to an append query specifying the "main" table. Run it and the record is added to the main table. Then run a Delete query to take it off the work table. This way your main table which is used by everything only has completed information in it.

Make sense?

Neil

 

[johnnymac43]

While no solution to your problem will come with any guarantee of no duplicates, I would suggest a different approach, as follows:

Add a new field, called something like 'fldNameSequence' to your table. Combine it with your Name field as the primary key. Have it default to 1, so that you cannot add a second occurrence of a name without changing it to a higher value.

When you add Year of Birth and/or Country of Origin, provided you are sure you can add these values to the correct record (perhaps based on something like a consignment note number) you can run a query to determine whether or not you have a true duplicate.

If the volume of name duplication becomes substantial, you could automate the process of managing duplicates by having the system assign the next Name Sequence value automatically, and running a periodic report to advise you of any new duplicate names.

HTH

John

 

[fneily]

Mr. Johnnymac43
"While no solution to your problem will come with any guarantee of no duplicates" See below:
"you can run a query to determine whether or not you have a true duplicate."
"If the volume of name duplication becomes substantial, you could automate the process of managing duplicates by having the system assign the next Name Sequence value automatically"

What??!!!???
Look in a mirror with a friend(like a relative). Read my suggestion, then read yours.

Just say no.

 

[johnnymac43]

Here we have a perfect example of self-importance being placed ahead of common sense.

When the user is in possession of all the identifying details of each of 2 or more animals with the same name, they will be able to make a value judgment on whether they have a true duplicate. A query or report is merely a tool to facilitate this process.

Stick to the knitting and give up on the smart**** remarks.

John