Calculated Count Field in Access 2010 Table

[johnsmith1]

Hello world, I have a challenge with an access database I created and administrate. Please assist me on this:

I have a table that contains a range of fields on sea turtle data: Turtle Releases table. One of the fields in it is the “Tag” field with entries like: KE5665. Each turtle has a unique tag, while each entry/record represents a First capture/Recapture event for a turtle. The same turtle can be captured many times and hence the number of times its Tag appears in the table represents the number of times it is captured.



1. How can I create a calculated field in the table that is going to calculate how many times each tag appears in the table?

2. 2. How can I create a calculated field in the same table that gives the recapture status of each turtle? For example returns 1 or Y for a record if the tag field of the record (hence turtle) appears more than once in the table, and N or 0 for the first time a tag is recorded

Thank you.

[RuralGuy]

Let's start with "Calculated fields do not belong in tables, they belong in queries".

[AmyLambes]

Create a field called count.
after you enter the tag create an event that runs and update query that will add one to the value in the count field. If you need more help I will send you a sample.

[RuralGuy]

Here's a link on this topic for everyone to read: http://allenbrowne.com/casu-14.html

[SteveH2508]

Fields store data - queries manipulate data.

A totals query (use the sigma button on the ribbon) with group by Turtle tag and count should do what you want.

[johnsmith1]

Thank you for the answer. I do know that queries are the ideal way to do this, but I asked how to do it in the table in case there was away to especially because there seems to be a problem with using queries to achieve these tasks. So now ill just point out what exactly I am trying to do on the queries that doesn't quite work out right (which led me to ask the first question). I have a table called, say, Table 1 containing 14 years of turtle capture data. Each record represents either a first capture or recapture of a turtle, and the Tag field is filled for each record: whether its a new capture or a recapture. This means that if I create a query with a list of all tags from the table and add the Count function, I can see how many times a tag (hence turtle) has been recorded: A count of 1 means that a tag/turtle has been recorded only once/has never been recaptured. Now what I want to do is to create a query that counts how many new turtles/new tags have been recorded in year 2012. So I have created a query based on Table 1, lets say, Tags_2012 query: this lists all tags recorded in 2012 captures: BOTH recaptures and first captures. To Isolate the two, I need to have a count field in Tags_2012 query that shows how many times each tag listed in Tags_2012 query appears in Table 1 i.e. I need Tags_2012 query to count from Table 1.
How should I do that?
Is there a better way to go about this?: I am open to any new approach.
Thank you for the help .

[RuralGuy]

So what do you do for a turtle that was originally captured in 2011 but recaptured in 2012?

[johnsmith1]

Each record represents a capture event. A turtle that was captured in 2011 is already in the database(or rather, its tag is). When it is captured again in 2012, the tag on it (same tag that was attached in 2011) gets recorded in the Tag field of the record. Therefore the number of times a tag appears on the database represents the number of times a turtle has been captured(Inclusive of the very first capture).

[RuralGuy]

But if you only examine 2012 records, you will not see that the 1st 2012 for "this turtle" is actually a recapture.

[johnsmith1]

Yes that is correct. So I need to count how many times each 2012 tag/turtle listed in Tags_2012 query appears in the Tag field of the main table that contains data for all years. If a turtle/tag from 2012 has a count of 1 from the main table, it means it was caught for the first & only time in 2012 (And that is the task I need to achieve: Isolate 2012 first timers (which would appear only once in the main table) from 2012 recaptures (which would appear more than once in the main table).

[recyan]

In total agreement with RuralGuy & SteveH2508.
To keep things moving meanwhile, just check if below gives some guidelines :

SELECT 
	tblTurtles.turtleTag, 
	Count(tblTurtles.turtleTag) AS CountOfturtleTag, 
	Count(tblTurtles.turtleTag)-1 AS NoOffRecaptures
FROM 
	tblTurtles
WHERE 
	((Year([DateOfCapture])=2012))
GROUP BY 
	tblTurtles.turtleTag;

Thanks

[johnsmith1]

Hello all, I have been able to solve the first problem: Isolating tags from a specific period that have been recorded only once in the database. This I have done by: 1. Creating a query off the main table that counts how many times each tag is recorded in the main table. 2. Adding the criteria ">1" to the count field of the query created in step 1 so that I have a list of tags recorded more than once i.e Turtles caught more than once. Lets call this "Recaptures" query. 3. I then created "Find unmatched " query that returns records in Tag field of Tags_2012 query that are not in Tag field of "Recaptures" query. The result gives me a list of tags that have been used for the first time in 2012. That leaves us with the second problem:
How do I:
Create a calculated field that returns "0" the first time a turtle is captured i.e. when a certain tag is recorded for the very first time, THEN, if the same tag is ever recorded again (Turtle recaptured), the field returns "1" each time?. In other words, for every first time a Tag is entered, this field would return "0" and for all subsequent times the same tag is entered, this field would return "1".

[johnsmith1]

Thanks you al for the assistance provided, I realy apperciate it. I have managed to answer my last question after a lot of gooling and some random reaing on SQl. The The query to answer my very last questio
n looks like: SELECT [tbl_Watamu Turtle Bycatch Data].[DateTime Caught], [tbl_Watamu Turtle Bycatch Data].[Tag 1], IIf([DateTime Caught]=DMin("[DateTime Caught]","tbl_Watamu Turtle Bycatch Data","[Tag 1]='" & [Tag 1] & "'"),"New","Recapture") AS [Release Type]
FROM [tbl_Watamu Turtle Bycatch Data];


SELECT [tbl_Watamu Turtle Bycatch Data].[DateTime Caught], [tbl_Watamu Turtle Bycatch Data].[Tag 1], IIf([DateTime Caught]=DMin("[DateTime Caught]","tbl_Watamu Turtle Bycatch Data","[Tag 1]='" & [Tag 1] & "'"),"New","Recapture") AS [Release Type] FROM [tbl_Watamu Turtle Bycatch Data];