Hi folks
I'm a bit of a newbie to queries that aren't *extremely* simple. More recently I've been trying to use slightly more complex queries to achieve what I need a bit more efficiently. However, I'm struggling to wrap my brain around this one and wondering if anyone more experienced/cleverer than me has any suggests for this situation.
I have 2 queries that I can run to achieve the results I need, the second using the results of the first. My question is whether they could be combined into a single query, and if so would that even be a good idea? Hopefully I can explain the situation well enough to be comprehensible.
The queries access 2 tables - Visits which records visits to a site to do a survey; Records with records individual observations of particular things on a visit. So, there will be situations with a visit but no records for that visit, but usually a visit has many records.
Query 1 - SumAbundInVisit (summed abundance seen of particular species in each visit, where Visit is a number unique for visits within a year)
SELECT Date, Visit, SUM(Abundance) AS SumAbund
FROM Records
WHERE Site = 'Bowdown Woods' AND CommonName='Adder'
GROUP BY Date, Visit;
Query 2 - Max Count (for a single visit within each year)
SELECT YEAR(Visits.Date) AS SurvYear, MAX(SumAbund) AS MaxCount, COUNT(Visits.Visit) AS NoVisits
FROM Visits LEFT JOIN SumAbundInVisit ON (Visits.Visit=[SumAbundInVisit].Visit) AND (Visits.Date=[SumAbundInVisit].Date)
WHERE Visits.Site = 'Bowdown Woods'
GROUP BY YEAR(Visits.Date);
These do achieve what I want (I'll be changing the conditional WHERE values in both queries programmatically), but I'd be interested to hear any comments on better ways to achieve the same end. And any way to fill in the blank MaxCount values where there was a visit but no records with a zero would also be very useful.
Thanks for looking.
Andy.
I'm a bit of a newbie to queries that aren't *extremely* simple. More recently I've been trying to use slightly more complex queries to achieve what I need a bit more efficiently. However, I'm struggling to wrap my brain around this one and wondering if anyone more experienced/cleverer than me has any suggests for this situation.
I have 2 queries that I can run to achieve the results I need, the second using the results of the first. My question is whether they could be combined into a single query, and if so would that even be a good idea? Hopefully I can explain the situation well enough to be comprehensible.
The queries access 2 tables - Visits which records visits to a site to do a survey; Records with records individual observations of particular things on a visit. So, there will be situations with a visit but no records for that visit, but usually a visit has many records.
Query 1 - SumAbundInVisit (summed abundance seen of particular species in each visit, where Visit is a number unique for visits within a year)
SELECT Date, Visit, SUM(Abundance) AS SumAbund
FROM Records
WHERE Site = 'Bowdown Woods' AND CommonName='Adder'
GROUP BY Date, Visit;
Query 2 - Max Count (for a single visit within each year)
SELECT YEAR(Visits.Date) AS SurvYear, MAX(SumAbund) AS MaxCount, COUNT(Visits.Visit) AS NoVisits
FROM Visits LEFT JOIN SumAbundInVisit ON (Visits.Visit=[SumAbundInVisit].Visit) AND (Visits.Date=[SumAbundInVisit].Date)
WHERE Visits.Site = 'Bowdown Woods'
GROUP BY YEAR(Visits.Date);
These do achieve what I want (I'll be changing the conditional WHERE values in both queries programmatically), but I'd be interested to hear any comments on better ways to achieve the same end. And any way to fill in the blank MaxCount values where there was a visit but no records with a zero would also be very useful.
Thanks for looking.
Andy.
