Help a beginner with Query syntax?

[Vernon Compton]

I am having a total block on this, and would be grateful for any help. I have a small database setup to record my staff's work, and am trying to figure out how to keep track of what's left to be done in each ward they're working on:


What I would like to do is find out how to get an "outstanding" figure by counting how many Records in "Respondents" have the WardID, and subtract that count from the "quota" field in ward. Every time I try to add a "count" function, I get a nasty error message that I don't really understand, something like this


I've tried right join, left join, inner join, but the real problem is that I clearly don't understand how to achieve my intended outcome. Can anyone help?

[audionuma]

hello,
the problem is that when you have a query with an agregate function (like COUNT() in your example), all others fields that are in the query outside of the agregate function must be in a GROUP BY clause.

For you, it would be like :

[DrewJensen]

Not sure I want to help on this...but suppose if I don't ask which party you are working for I can't be sorry for helping, right..LOL....just kidding.

I will use the QiQ feature of OOo2.0.4 for this -

First I want to get the total respondants per ward.

The SQL would be
SELECT COUNT( "RespondentID" ) AS "Total Respondents",
"WardID" FROM "Respondent" GROUP BY "WardID"

The query designer for this is of course:
Total Rrespondents

Then this value is used against the Ward table Quota field.

Thte SQL is:
SELECT *, "Quota"-"Total Respondents" AS "Outstanding" FROM "Ward",
(
SELECT COUNT( "RespondentID" ) AS "Total Respondents",
"WardID" FROM "Respondent" GROUP BY "WardID"
) AS "tmpTbl"
WHERE "Ward"."WardID" = "tmpTbl"."WardID"

Here is this in the Query Designer

Outstanding Quota

EDIT - Noticed that Audionuma had responded while I was working on my response. This is how it would look setup in the designer -

Single Query for values

[Vernon Compton]

Thank you both very much for clearing up both the particular problem and explaining how it works. Oh, and Drew, you can relax, this is for a research company, surveying residents' satisfaction with council performance. The client is the council itself, and the research is strictly apolitical.

[DrewJensen]

Oh, good I was afraid you were representing THAT OTHER political party - course I am not saying which one that is..

[Vernon Compton]

If I might push my luck a bit further:

Thanks to the help in this thread, I've managed to create queries to give me tallies of respondents by staffID and age/gender groupings. I've also managed to create the QiQ to give the balance outstanding for one group, M40+. What I'm wondering is if I can "daisy chain the queries for each age/gender group.

Here's the first QiQ I made:


What I'm wondering is how to "daisy chain" this sort of query. I've tried adding in another one, like this:


but as the screenshot shows, it doesn't actually work. Can I daisy chain like this, to get all the quotas and "outstanding" sums in one query? Also, if that is possible, can I order the columns, so that, for example, "M18-39quota" is adjjacent to "m<40 left", etc.?

[Vernon Compton]

I'm just bumping this up since I'd love to know if I can concatenate queries as I'm trying to or not.

[tuari hemi]