2 become 1?

ShedAndy

New member
Local time
Today, 23:33
Joined
Dec 18, 2015
Messages
3
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.
 
Quite often multiple queries run faster than a single complex query. I'm a big fan, if it ain't broke and isn't glacially slow, don't fix it.
 
Just a reminder that date is a reserved word. Using it as a field name will at some point create an ambiguous error which you will spend days trying to figure out. So change it now to something more meaningful. Other popular 'names' are name, month, year -see this link for a full list

https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe
 
Thanks for the responses and advice, sounds pretty reassuring. I'll sort out the Date issue before it becomes too much of a pain to fix.

Have a great Christmas all.

Andy.
 

Users who are viewing this thread

Back
Top Bottom