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…
1 Like
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!
Thanks, I can work with that.
I doubt that. It will be a nightmare to maintain. What if the membership fees change? What if you have appended 12 booleans for each field?
One possible standard conformant solution may look like:
membership.odb (15.6 KB)
- Tables: List of members, list of memberships (with start/end-date and monthly fee), list of payments (date, amount).
- Queries: Calculating the total amounts of debit, credit, balance for each member or for each membership.
- Form: The form lets you edit the members, the memberships for each selected member and the payments for each membership.
- Test data:
– Peggy is a member since 11 months with a monthly fee of 10 bucks. She didn’t pay since September.
– Charles has been a member in 2024. He paid all the fees of 9 bucks for the 12 months. Since July 25 he has another membership for 12 bucks per month with 12 bucks deficit.
– Leonard was a member from March 24 until April 25. He still ows 12 bucks to us.
– Cindy is a brand new member owing the first 11 bucks.
1 Like
That is good, I could not come up with that level of sophistication. I thought I was doing well getting a dropdown list to work through a filter table. For what I need the Teams are fixed for the season and the season runs for nine months, so I thought a check box for each payment would not be unreasonable. Its either paid or not.
I’ll certainly look at the example you have provided, the form navigator view is blowing my mind! Thanks.
If this is all perhaps 1 Spreadsheet per season.
No need for DB 
OK. A spreadsheet may be adequate in this particular case. I was thinking of many (possibly different) club memberships during many years.
The yellow form has members.
The gray subform shows the memberships of the selected member plus the calculated months and totals (months * fee) for each membership (query “Membership_Debit”).
The payments are entered into a sub-subform showing all payments for the selected membership.
Finally, this sub-subform has another subform showing the sum of payments for the selected membership.
The debit, credit and balance below the memberships are 3 subforms bound to 3 queries "Members_Debit, Member_Credit and Members_Balance showing the 3 sums of all debits for each selected member.
1 Like
The finance side was to be an extension to the already existing database. Where I have Venues, Teams per Venue and Players per Team. I also want to manage the team and individual player knockout competitions through it, which would just be a refined list of players. I am also looking to add Fixtures and league tables. I’ll put the finance on the back burner for now, plenty to go at…
So you need to treat it as a DB component.
Not like a Spreadsheet.
By the way: if there’s already a DB there’s the table TEAMS (= members). So first step 
to the table
PAYMENTS.
Or to have an independent “support” Spreadsheet – as it looks like you want – following the lines of… Spreadsheets.
Just another suggestion for a solution using a normalized payments table with year, month, team-ID and booleans.
Having a table of years (2025-2030) and months (1-12 and month names), it is easy to generate a table of due payments for each team, each year and each month until December 2030.
After adding a new team, you just have to run an SQL statement in order to add the due payments to the table. If you add more years > 2030 to the years table, you can run the same statement and get new payment entries for the new years to come.
One form shows each team and its payments.
Another form shows the payment table filtered by due payments (unpaid as of current or past months).
Form “New Team” gets a new team name and a reminder to run the SQL statement in order to update the payments for the new team.
Instead of removing any team, you may flag it as expired.
Before you can remove any team, you’ve got to delete all the related payment entries of that team: Click the upper left corner of the subform grid in order to select all payment records, then right-click>Delete and confirm.
Teams_Finance_2.odb (52.6 KB)
1 Like
Teams_Finance_2.odb was quite OK. I managed to simplify the concept.
There is no more prefilled payments table. No need to run any SQL INSERT statements.
The forms add new payments as needed.
Teams_Finance_3.odb (27.0 KB)
1 Like
It feels like its come around full circle. As it just need to count the boolean fields, multiply the count by a fee and it can generate the finance report. Its all very clever stuff. Thank you.
I’m glad you like it. Found another minor glitch in form “tblTeams and Payments”.
Open the form for editing, call the form navigator and drag subform “Due_Payments” from the “MainForm” (teams table), to the “Payments” form. Connect from master field “TID” to slave field “TID”.
Now the update button updates the yellow form of payments together with the red form of due payments.
Query “Due_Payments” is the core of this solution. It generates the cartesian product (all combinations) of all years, months and teams: SELECT "Y"."ID","M"."ID", "T"."ID" FROM "tblTeams"AS "T", "Y", "M" returns 6x12x7=504 records for 6 years, 12 months and 7 teams.
This record set is joined with the payments, so any missing payment entry is represented by NULL values: WHERE ( COALESCE ( "P"."PAID", FALSE ) = FALSE shows entries from the cartesian product without any payment entry and any matching payments where “PAID”=False.
AND ( "Y"."ID" = YEAR( CURRENT_DATE ) AND "M"."ID" <= MONTH( CURRENT_DATE ) OR "Y"."ID" < YEAR( CURRENT_DATE ) ) ) discards the future months.