Data manipulation query - Ornithological data

[MW0CBC]

I’m currently analysing some bird ringing (banding) data..

I want to examine how various measurements on the birds change as the birds age. If anyone wants to know why, happy to give a more complete explanation – it’s not just “because it’s there”, there are real conservation and wildlife management reasons.

In the example attached are 12 rows of data (I have 13,000 records to work with). Columns B - I as they are today and L - X as I would like them to be.

Each ring number represents an individual bird. The RTYPE column tells me whether this is a (N)ew bird or a (R)etrap a bird that we had ringed previously and then recaptured. The AGE column tells me if it’s a juvenile bird (codes 3 & 5) or something older (code 4 and codes 6 – 15. Don’t ask about the codes….they’re not mine!!). I’m not interested in any other codes (there are some, not shown on the example)

I want to compare the differences between individual bird’s first capture as a juvenile and first re-capture as an adult.

As an example, DK17689 was first ringed as a juvenile on 30 Sept 1989 (AGE = 3) and was subsequently recaptured, again, still a juvenile, (AGE = 5) on 24th February 1990. It was also caught again in 1991 when it had grown into an adult (AGE = 7), then again in 1992 and 1993. You can see how I would like to arrange the data for the re-capture in the example. I am only interested in

DK17700 I’m not interested in. It was a juvenile when first ringed and still was when subsequently recaptured.

DK17729 I’m not interested in because it was already an adult when first captured.

DK17739 was a juvenile when first captured and an adult when re-captured so that one gets included in the analysis.

It may be that I could use some complex formulae in Excel to do what I need but I thought Access (2010) was probably the best option. I’ve done very little work with it though so, any help or suggestions would be very much appreciated. I am very much a novice.

Thanks in advance.

Denis

 

[plog]

Yes, access would be perfect for this. Excel is good for quick and dirty data manipulation, but if its something you are going to be doing repeatedly or updating your data, Access is the answer.

Attached is a database that accomplishes what you want. It has 2 tables--one is your data, the other is an AgeGroup table which defines which ages are juvenile and which are adult. Then it has 2 queries, a sub-query to determine for each bird its first Juvenile Capture date and its first adult recapture date. The Recapture query is the one that spits out the data that you wanted. Run that one and it should give you what you wanted.

 

[MW0CBC]

I can't thank you enough for this. I have been trying to fathom how to achieve this for days. I haven't tried it on my full dataset yet but I'm sure it will be fine.

Can I ask you please how you did it? I presume you wrote this directly in SQL rather than using any of the query tools in Access itself? Did it take you very long?

I will have a few more of these (well, similar)to do over coming weeks so I will try and get my head around the code you've written. It will serve as a good example for me. One of the most important things to come out of this, apart from the end result, is having it confirmed that Access is the way to go for this kind of work.

Thank you so much. You've made an old ornithologist very happy

Denis

 

[jdraw]

You may find this free data model of some value. It isn't meant to be your specific database, but to serve as a source for some ideas. Your model may have many more subject areas.

http://www.databaseanswers.org/data_models/bird_research/index.htm

Good luck with your project

 

[plog]

Your welcome, I always enjoy a unique challenging project. It probably took me 10 minutes to get your data in and to set up the other table. Then another 10 to write the queries--I created a new query in design view, brought in the tables I needed, linked them in Design view, manually wrote the conditional statements for the First Capture fields. Then I did that again for the main query.

In fairness, I have a lot of experience doing this--I work with medical data and am always asked to find first patient visits meeting certain criteria and then to look for specific events that occur later on to those patients. This was very similar.

 

[MW0CBC]

Thank you for the suggestion, jdraw . It's not quite what I needed but thank you for taking the time to look for it anyway!

Best wishes

Denis

 

[MW0CBC]

Hi plog,

Seriously impressed with how little time it took you. I will confess, I am not entirely sure I understand how the script works yet but I will keep trying to work it out. I have a few more queries that require "firsts" e.g. in each year, I need to be able to identify the first capture of any bird coded as "R" and make some changes based on that - so I do need to get my head around how to do it..

Anyhow, I thought you would be interested to know where the query you wrote for me has gone.

Back in 1988, some researchers devised a method, for assigning these birds (Redshanks) to one of two races. The two races mix here in the UK in the winter but breed separately in Iceland and the UK. They went to the breeding grounds and took sample measurements from birds at or near their nests (thus, they were of known race). From the measurements, they were able to build a discriminant function which assigned birds to race with better than 90% accuracy.

This gave those of us researching the birds in the winter, a way of assigning race, but only for adults. It had never been tested with Juvenile (first year) birds. Thanks to your help, I've been able to use the differences between birds in my dataset first captured as juveniles and then subsequently captured as adults to devise a correction factor for juvenile measurements within the discriminantfunction such that it now works for birds of all ages.

This is important because we suspect that that two races have different feeding requirements (and thus, potentially, different conservation management needs) in the winter. Typically, about 1/3 of the birds we catch in winter are juveniles - so their race remained unknown.

Hope all that makes sense and is of some interest.

Best wishes

Denis