Using count to update a table field.

[FurryGorilla]

I have a table which contains information about cattle (such as eartag_no, breed, sex, etc). The last field that I need to implement is one which contains an integer value based on the number of times the animal appears in another table (called Claimed). The animal does not have to appear in the second table but can only have a value of 0,1, or 2 in the field that I am trying to implement.

TABLES

[ul][li]CATTLE: Eartag_no, breed, sex, number_of_claims(this is the field that needs to be updated)
[/li] [li]CLAIMED: Eartag_no (needs to count this), claim_form[/li][/ul]
I have tried using SQL and a few queries but I have been unable to do it. X-)

Any help would be appreciated.

Chris

 

[kathryn]

OK, I would not make this a field in the table itself. I would create a field using a query and link that query to the Cattle table.

The query you would make, call it qryCountClaim, would be

SELECT Eartag_no, Count(*) AS ClaimCount
FROM CLAIMED
GROUP BY Eartag_no;

This query will return two fields: the eartag_no and the count of the number of claims for each eartag_no. If you then do another query joining this query to your cattle table with a join on the eartag_no, you will have what you are looking for, if I understood your question.

Hope this helps. Kathryn