×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using count to update a table field.

Using count to update a table field.

Using count to update a table field.

(OP)
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

  • CATTLE: Eartag_no, breed, sex, number_of_claims(this is the field that needs to be updated)
  • CLAIMED: Eartag_no (needs to count this), claim_form

I have tried using SQL and a few queries but I have been unable to do it.

Any help would be appreciated.

Chris

RE: Using count to update a table field.

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
 
 
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close