How to ensure counts are unique in my ornithological data set

[MW0CBC]

Hi Again,

I am making progress with learing SQL and Access 2010 to help me prepare my ornithological data for statistical analysis but I have gotten stuck again.

I have a ringing (banding) database with 12,900 records (sample attached). Each record represents a capture of a particular bird with a unque ring number and then various data about the bird.

The birds were captured at a number of sites. Sites have a Site code and also a numeric site coding variable which I use in my stats package.

There are two races of birds and the field 'Icelandic=1' codes 0 for British race and 1 for Icelandic race.

In plain language, I want a count of British and Icelandic race birds during the months of August and September BUT I need to be sure that each individual bird is only counted ONCE at each site, during those months.

For example, in the database extract attached (which is only for September data - the whole thing gets a bit big), DN71828 was captured twice, at the same site (OGW) on 11/09/1988 and 30/09/1989. The second capture must not be counted in the summary for these two months. Had the second capture taken place at another site, then it would need to be counted in the summary.


The code I created below works fine, in that it produces a perfect summary of the number of birds of each race for each site. It reports in a format that I can cut and paste directly into my stats package but I cannot work out how to ensure I only count each bird once. I'm pretty sure this I have witten will pick up every capture of every bird during those two months, not just the FIRST capture. Can anyone offer any suggestions as to how I might ammend my query to do what I need?

I hope what I'm asking makes sense. I'm finding it rather hard to describe what I'm trying to do.

Thanks

Denis,

SELECT Distinct Racial_Analysis.[Site code], racial_analysis.[site-coding_variable], Racial_analysis.[icelandic=1], Count (*) AS Num_birds

FROM Racial_Analysis

WHERE (((Racial_Analysis.[Site code]) Like '*BAN*' Or (Racial_Analysis.[Site code]) Like '*LLA*' Or (Racial_Analysis.[Site code]) Like '*OGW*' Or (Racial_Analysis.[Site code]) Like '*PEP*' Or (Racial_Analysis.[Site code]) Like '*RHO*' Or (Racial_Analysis.[Site code]) Like '*SPF*' Or (Racial_Analysis.[Site code]) Like '*TYC*' Or (Racial_Analysis.[Site code]) Like '*WIG*') AND ((Month([racial_analysis].[date]))=8)) OR (((Month([racial_analysis].[date]))=9))

GROUP BY Racial_Analysis.[Site code], Racial_analysis.[icelandic=1], racial_analysis.[site-coding_variable];

 

[JHB]

You can do it on this way with 2 queries.
First select them out so each bird only occurs one time for each different site.
Then count them.

1. query call it: BirdsPerSiteCode

SELECT DISTINCT Racial_Analysis.[Icelandic=1], Racial_Analysis.RingNo, Racial_Analysis.[Site code], racial_analysis.[site-coding_variable]
FROM Racial_Analysis
WHERE (((Racial_Analysis.[Site code]) Like '*BAN*' Or (Racial_Analysis.[Site code]) Like '*LLA*' Or (Racial_Analysis.[Site code]) Like '*OGW*' Or (Racial_Analysis.[Site code]) Like '*PEP*' Or (Racial_Analysis.[Site code]) Like '*RHO*' Or (Racial_Analysis.[Site code]) Like '*SPF*' Or (Racial_Analysis.[Site code]) Like '*TYC*' Or (Racial_Analysis.[Site code]) Like '*WIG*') AND ((Month([racial_analysis].[date]))=8 Or (Month([racial_analysis].[date]))=9))
ORDER BY Racial_Analysis.RingNo;

Then make the second query based one the first query.

SELECT BirdsPerSiteCode.[Icelandic=1], Count(BirdsPerSiteCode.RingNo) AS Num_birds, BirdsPerSiteCode.RingNo
FROM BirdsPerSiteCode
GROUP BY BirdsPerSiteCode.[Icelandic=1], BirdsPerSiteCode.RingNo;

 

[MW0CBC]

Hiya,

Many, many thanks for this. I've been away with work for a couple of days but will be able to try this over the weekend.

I really do appreciate your help and will report back soon.

Best wishes

Denis

 

[MW0CBC]

Hi,

Tried these queries and it works just fine - thank you

Is there any way of combining these two into a single query? I have tried but can't seem to do it in a way that doesn't give me error messages. I only ask because I have to run a lot of these with seperate parameters (dates etc) and it would be more managible in one query.

Thank you so much for your help.

best wishes

Deis

 

[JHB]

No I don't think so, because first you've to select them out so each bird only occurs one time for each different site.