I manage some teams, as part of the operation I collect monthly fees. I have set up a table which has a tick box to indicate the monthly fee has been paid. My idea is then to count the “ticks” multiply it by the monthly subscription and and up with a value. I can not generate a query which will count “booleans” in a column. Even chatGPT as pleased when I gave up!
SELECT COUNT("MyBoolean") FROM "Table" WHERE "MyBoolean" IS TRUE
gives the whole number of choosen boolean fields.
Better you offer an example where we could see the problem and solve it.
Teams_Finance.odb (4.5 KB)
I have attached a sample table. The Fee for each month is £10.00. I’d like to query what has been paid by each team and by each month.
I have included various queries as given by ChatGPT. They work a lot better in this sample table than they do in my original file. I couldn’t get any of them to work at all in my original file. I believe that there is something not quite right in my original file?
Problem is your DB is not “normalized”. Like e.g.
-
Table TEAMS: [ID pk] [NAME]
-
Table PAYMENTS: [ROW_ID pk] [TEAM_ID (= ID of the paying team)] [DATE (of the payment)] [PERIOD (the month payment is for - can be e.g. YYYY-MM-< last day of that month >)]
Just a tentative example. It can be better designs…
But not adding columns to the table.
Your table is like a Spreadsheet.
SELECT “TEAMS”.“NAME”, […] FROM “TEAMS”
JOIN “PAYMENTS” ON “PAYMENTS”.“TEAM_ID” = “TEAMS”.“ID”
GROUP BY […]
But to write a concrete example, you must first normalize your DB.
Also it may be convenient another table to register the fee values as they change in time. Or another field in PAYMENTS to hard store the value paid at each month.
I understand, I posted a simple table requesting how to count the boolean fields. I’m not posting the full database. My database has various linked tables using IDs. e.g. tblVenues, tblTeams, tblPlayers, tblTournamentFees, tblTeamFees, tblFixtures. All I would like is to count the boolean fields. It may just be easier to change from boolean to integer and just enter the fee.
No need to count anything:
SELECT "TeamName", "Fee Jan" * 10 AS "PaidJan", "Fee Feb" * 10 AS "PaidFeb", "Fee Mar" * 10 AS "PaidMar" FROM "tblTeamsFees"
gives all what has been paid in a month.
But in this construction you will need a new column for every month…
The boolean fields are counted as per @RobertG 's answer above.
SELECT COUNT(“the target column”) AS “OKs” FROM “YourTable” WHERE “the target column” IS TRUE
Yes, like I said above:
PAYMENTS
[ROW_ID - pk auto] [TEAM_ID] [DATE (of the payment)] [PERIOD (month payment is for)] [VALUE (fee ammount paid]
PERIOD can be YYYY-MM-01 (better) or text “MMYYYY”
Your choice.
All info needed is here. No need for this boolean column!