not exists help

[mcalex]

Hi, I'm stuck with a tricky (to me bit of sql that might want a 'not exists' clause somewhere, i think.

I'm trying to count local council election data; the tables are like this:
A Council (tblCouncil) has (1 or more) Wards (tblWard).
Each Ward has a field: VacancyCount
Wards have (0 or more) Candidates (tblCandidate)
if the count of Candidates for a ward is greater than the Ward.VacancyCount figure, then
that Ward is 'Contested' (but only in reports, we don't have a boolean 'Contested' column).

What I want to do is create sql that asks if all the Wards in a Council are NOT contested



I can get all the way to whether each ward is contested | NOT contested, but I don't know how to count whether every ward in the council has less vacancies than candidates. My original thought was that I wanted to:

• create a 'IsContested' boolean column in tblWard and then count all the false's in that column for all the Wards in a Council, and if the count is equal to the number of wards then the council is uncontested

but after some searching I'm thinking it might be better to:

• create a type column in tblWard and put 'Contested' in it if the count of Candidates is greater than the Ward.VacancyCount for that Ward, and then use not exists in the sql

but I'm stuck using either approach. The query tells me the Council is not contested if any wards are not contested, not every. Searching for the solution to this brought me to the idea that 'not exists' would help, but i haven't used that syntax before.


does this make sense? can anyone point me in the right direction?

many tia

mcalex

 

[mcalex]

ha! talk about trying to make it too difficult

i used not in, instead of not exists.

too easy