Data Entry and Primary key

[hadi]

Hi,
I am making a new database on access for dairy farm to capture data on cows reproduction, milking, treatment, feeding etc. cows are identified by "ear tag numbers" by the doctors and in old database on excel. I can not use ear tag numbers for primary key, because they are repeated and needs replacement if they get lost. Also, I am stuck with problem of linking tables in the database.
If i generate a new series for primary key and linking tables, there will be problem of translating new series and "ear tag numbers" in data entry and report generation.
Please advise me on how to solve problem of translating between ear tag numbers and primary key. Thanks in advance.

Hadi

 

[MStef]

Is there any other data which is not repeatid, (number, name, and s.o.)???

 

[CameronM]

You can always link on a primary key that is 'invisible' to the user. That way when you need to change tag numbers, the underlying record is still linked to any other tables.

For example, use an autonumber field called AnimalID in your main 'cows' table, which is then used as the foreign key in your 'milking' table. So that cow with AnimalID number 1, will always refer to the same cow, irrespective of the tag number.

 

[hadi]

thanks for showing interest. there is no other unique data related to cow, tag numbers are unique to a large extent, out of 450 cows, there is are less then 10 instances of repetition. although i can make a composite key of tag + date of birth or tag number + calf's mother's tag number, but i am not sure if that is feasible for linking tables and make data entry possible.
Tag numbers are like employee names, can i make enter data relating to employee without referring to employee ID. further i have data of last 3 years (about 100,000 entries) in which cows are referred by their tag numbers, how i will link those tag numbers with any new primary key which i ll generate.
thank you for your time.

 

[wiklendt]

i presume each tag is only used by one cow at any one time.

how does your previous 100,000 records distinguish between these tags/cows? that is, with about 440 tags, how had you PREVIOUSLY known which tag meant which cow?

also, to take on the analogy you yourself provided with employees.

employeeID is the easiest way to refer to a single employee between related forms. why? because say you have seven employees byt he name of "Andrew Smith".

you will not be able to pick the correct Andrew Smith if you ONLY have the name to go by. so what else do you go by? why, address or mobile number would be a good indicator that Andrew Smith 1 (who lives in Redfern) is different to Andrew Smith 2 (who lives in Newtown), but the Andrew Smith that you are actually trying to call is Andrew Smith 3 (who lives in Newcastle).

so, if you apply an EmployeeID to these employees, all of a sudden you can say "i want to speak to EmployeeID=3" and know you have the right person INSTEAD OF saying "i want to speak to the employee whose name is 'andrew smith' and who lives in newcastle". it's just easier and you're only referring to ONE piece of data, rather than trying to pull together many in one go.

so, primary key should be something like CowID in your tblCows table and all the information that is about JUST the cow.

(i.e., if you were making an employee table, you might have DOB, gender, etc but you wouldn't have "shirt worn today" becuase that will be constantly changing and has nothing to do with the person themselves - anyone could wear that shirt. so you'd have a separate table for shirts called tblShirts and each shirt would have an autonumber primary key called ShirtID.)

you should then have a separate table, tblTags, with another primary key called TagID and any info about that tag which is JUST about that tag. e.g., tag colour, tag number, tag shape (anything that CAN'T change about that tag).

that's Normalisation 101. have a separate table for separate entities. e.g., separate table for companies, separate table for employees, separate table for products, separate table for... you get the idea. so, that is to say, a company MAY supply a product, but they should NOT go into ONE table- they should be separated into two. on which stores JUST the products' details, and another stores JUST the company's details.

now, you need to somehow join the tag to the cow. i presume one cow will only have one tag their whole life on that farm?

if so, you should add a field in your tblCow table to accomodate this one tag. it will be of datatype "number" (not autonumber) and should be called TagID.

in the relationship window, you can then create a link between the tblTags table and the tblCows table by the TagID (just click and drag the TagID from one table to the other, and make sure to check for 'referential integrity' - which basically means that a cow cannot have a non-existent tag).

...let's stop there and see if this sinks in and where you might want help from here.

you have to understand that access is not excel, and that a 'spreadsheet' is not what you are making in access.

access will do a lot of the work for you IF you set it up correctly in the first place.