Totalling fields to create a Query table

[beemerbiker]

I’m on the verge of giving up. Any help much appreciated.

My DB has a single table “tbl_ClientLog” whose first few records might contain:

ID	Org	Year	Quarter	ClientsSeen	NrMale	NrFemale	Nr<26	Nr26-45	Nr46-65	Nr>65	Nr Assessments	ReportSubmitted?
0	RM Unlimited	2023	2	24	13	11	0	9	12	3	20	true
1	CC Onthego	2023	1	48	19	29	1	14	18	15	42	true
2	AUR Ready	2023	1	23	9	14	0	0	6	17	21	false
3	AUB Zipser	2023	1	31	15	16	0	0	0	31	31	true
4	AUR Ready	2023	2	45	30	15	0	0	5	40	43	false
6	RM Unlimited	2023	1	19	7	12	1	5	12	1	19	true
7	AUR Zipser	2023	2	28	12	16	1	0	0	27	27	true
8	CC Onthego	2023	2	54	28	26	2	9	30	13	53	false

Note: this is a gross simplification of the real case, which has over 50 fields per record, but it will serve to get the point across.

So this might represent the quarterly monitoring of 4 separate charities (“Org”) collaborating on a single commissioned project. Each charity will enter their own quarterly data into a common database file shared on OneDrive or similar.

But the commissioning authority, for monitoring purposes, wants only totalled data for each quarter, so, for example, qr 1 of 2023 would show 121 clients seen, of which 50 were male, and so on. And, try as I might, I cannot construct an SQL Query for LO Base that produces this (I have HSQLDB as the embedded database).

SQL seems to be language bedevilled by its dialects. I can Google SQL queries that look like they will do the sort of thing I want (using SELECTs within SELECTs), but the syntax won’t work in LO Base, whose debugging facilities are effectively zero.

OK, so I could do the whole thing in a spreadsheet (and I have), but Excel has pretty clunky input facilities — MS Forms is just a toy in this sort of application, and the forms built into Excel have a limit of 30-odd fields and don’t look good — whereas i can make great-looking input form for LO Base. And, as far as output is concerned, I imagine I could create a nice Report in LO Base, whereas with Excel I might have to explore the monster-infested depths of MS Power Query.

So, is it bye-bye LO Base or is there a solution to this sort of thing that I’m missing (probably due to my inexperience)?

[Villeroy]

This is a typical table layout of a spreadsheet user. Databases are not prepared for this.

Database normalization description - Microsoft 365 Apps

[RobertG]

You won’t need “ClientsSeen”, because it will be given by “NrMale” and “NrFemale”

SELECT "NrMale" + "NrFemale" AS "ClientsSeen" FROM "tbl_ClientLog"

will do this for you.
Now you want all for one quarter (and in one year):

SELECT "Year", "Quarter", SUM("NrMale" + "NrFemale") AS "ClientsSeenInQuarter" 
FROM "tbl_ClientLog" GROUP BY "Year", "Quarter"

Don’t know what all the other columns are for …

[Villeroy]

See also yesterday’s topic where someone has a similar but different issue with the “wide table syndrome”. He has one table with clients and payments growing by a pair of date and payment columns every time when some client does his 7th, 8th, 9th payment.

Is there a way to cause a database form to save and reload automatically when a field is changed?

My second attachment tries to do it right in the most simple way. There is a list of clients with an auto-ID and one column for each client property (I reduced it to a name). And there is a list of payments with a date, amount, the id of the client who did the payment and an auto-ID. Each payment is linked to exactly one client by the clients ID number. This is called a one-to-many relation in database terms. The form lets you select one particular client on the left, the subform on the right s…

I split up his table in two and explained the relation between payments and clients.
A relational database (aka SQL database) keeps tables as interrelated lists of items where the columns of each row represent attributes of one item (one person, payment, product, price, whatever with attributes in columns) and where these list are related to each other like “each invoice belongs to one client” (one-to-many relation) or “each invoice has many sold products” (many-to-many relation) or each clent has one complete set of contact data (one-to-one relation). This way you end up with multiple tables having just a few columns, growing vertically row by row. Each new client adds more invoices, each new invoice adds more sold products to a sales table.
All this has nothing to do with Base which is just a tool to work with traditional SQL databases. Base requires a well designed database.

[beemerbiker]

Really helpful. Yep, I think a generalisation of your SQL syntax will do all that I want to do in thermal database. Great! (Villeroy’s more general comments very instructive, too.) Here is an SQL statement that works on the toy table that I presented in this forum (changed boolean last col to TINYINT to make counting easier):

SELECT "Year", "Quarter", SUM( "NrMale" + "NrFemale" ) AS "ClientsSeenInQuarter", 
SUM( "NrMale" ) AS "OfWhichMale", SUM( "NrFemale" ) AS "OfWhichFemale", 
SUM( "Nr<26" ) AS "<26 yrs", SUM( "Nr26-45" )
 AS "26-45 yrs", SUM( "Nr46-65" ) AS "46-65 yrs", SUM( "Nr>65" ) 
AS "over 65 yrs", SUM( "Nr Assessments" ) AS "NrAssessments", 
SUM( "ReportSubmitted?" ) AS "Qr Reports Submitted" 
FROM "tbl_ClientLog" GROUP BY "Year", "Quarter"

[beemerbiker]

Thanks for the pointer. I need to read up on all this.

[beemerbiker]

Thanks, RobertG. That is exactly what I wanted! I can generalise that for the real database.
Well done!!