Solved different aggregates from the same column (1 Viewer)

[access2007query]

I'm stuck! on a really basic problem, helping a friend in a small local club who uses French-language Access2007 to track contacts/members, payments, and payment-details (such as subscriptions, donations, sales of documents). Neither of us is really familiar with Access although my friend uses it daily to keep track of things. We have both tried queries and variations on SQL but so far to no avail.
I have simplified the lengthy French table- and column-names for the three tables we are working with :

Contacts table (call it C) contains [C].id , [C].name, ..
Payments table (P) contains [P].id , [P].date , ..
Details (D) contains [D].code , [D].value , ..

Contacts link [C].id to zero or one or more [P].id payments
Payments link [P].id to one or more [D].id details; there may be zero or one or several D lines for each [D].code, per payment.

We need to create a table for 2022 with the following columns on one line per [C].id :
[C.id] , sum ( [D].value) for [D].code = DON* , sum ( [D].value) for [D].code = SUB* , ...

What is/are the easy (easiest) way to do this (it seems a trivial enough problem) for simple users using the query wizards (with which my friend is familiar) or using SQL in Access2007?

 

[CJ_London]

Always better to show some data and the result required rather than describing it. So my guess is for each column

sum(iif(code =‘don’,value))

change don to sub etc for other columns

or you can use a crosstab query with code as the column header

 

[Pat Hartman]

Don't hard code this, use a crosstab. You'll get a separate column for each value of Code.

 

[access2007query]

Thank you so far ... if possible, want to stay on familiar territory ie. query wizard, query design, or very simple SQL.
I do not wish to introduce the person I am helpîng to new ideas, crosstab, etc. as this would introduce additional noise to the process:
I have to do this in French, and I do not know all the relevant and precise Access-vocabulary in French.

Here are some sample data and the result we are looking for -

C contact name id Bob c1 Anne c2 Cath c3 Dave c4		P payment cid dd/mm/yyyy method € id c1 02/02/2021 cheque 20 p1 c2 01/01/2022 transfer 20 p2 c4 30/12/2021 cheque 25 p3 c3 01/01/2022 transfer 25 p4 c4 15/01/2022 cash 20 p5 c2 03/03/2022 cheque 34 p6 c4 04/03/2022 cheque 50 p7 c3 31/12/2022 transfer 25 p8		D detail pid € code p1 20 SUBSCRIPTION p2 20 SUB p3 25 SUB2022 p4 25 SUB p5 17 BOOK p5 03 DONATION p6 34 BOOKS (2) p7 06 DON p7 25 SUB2022 p7 19 BOOK2 p8 25 SUB2023

Result (payments in 2022)	--------------------------------------------------	--------------------------------------------------
contact subscriptions donations other Anne 20 00 34 Bob 00 00 00 Cath 50 00 00 Dave 25 09 36

 

[June7]

Nothing new about CROSSTAB. Query designer has a wizard for that. However, can emulate CROSSTAB with expressions in a basic aggregate query.

Start with a query that JOINs the 3 tables then calculate 3 fields:
SELECT [name], Sum(IIf(Code="subscription",Detail.€,Null)) AS subscriptions, Sum(IIf(Code="donation",Detail.€,Null)) AS donations, Sum(IIf(Code<>"subscription" AND Code<>"donation",Detail.€,Null)) AS other
FROM <JOIN ON clauses here>
GROUP BY [name];

Name is a reserved word and should avoid using reserved words as names. Also advise not to use exact same field name in multiple tables nor symbols in or as name, better would be PayAmount, DetAmount.

 

[MsAccessNL]

I think you have to look at building a form, there are form wizards to start with. Building a form where you select a contact and a subform will show all his payments and payment details (subscriptions) is easier then building a complex pivot query.

 

[access2007query]

Thank you all very much AWF VIP , MsAccessNL, Pat Hartman, CJ_London, for your suggestions and reactions. These are my responses:

• Regarding the novelty of CROSSTAB, I feel that that in any situation "novelty" is relative to each player and their own knowledge experience and permeability to change The intersection of three sets [my mastery of Access], [my own mastery of Access in French including general vocabulary as well as reserved words], and [my friend's potential to handle "new" notions while using Access in native French] is very small.
• I used 'name' as a translation for the actual data-item-name in the table we're using ... I'm pretty sure it's easier for English-speakers to handle the avoidable [C].name or cname rather than [Liste des CONTACTS].NumContact, [REGLEMENTS].DateReglement and so on; at least it is for me!

For the moment I think we are going to try on Monday or Tuesday with the following query

SELECT [Name],
Sum(IIf(Code="SUB*",Detail.€,Null)) AS subscriptions,
Sum(IIf(Code="DON*",Detail.€,Null)) AS donations,
Sum(IIf(Code<>"subscription" AND Code<>"donation",Detail.€,Null)) AS other
FROM <JOIN ON clauses here>
GROUP BY [cname];

my three questions

1. Is the above syntax OK (give or take the naming issue) ?
2. The three tables are already linked (C to P using contact-id ; P to D using payment-id; do we need to join them in the query too?
3. Where should the 2022 payment-date selection be placed in this query? in each sum iif ? or somewhere else ?

 

[ebs17]

Code="SUB*"

If * is a wildcard for a pattern search, you must use LIKE as operator.

2. If you need fields of the left table and the right table, you must link them, including the intermediate table. So yes.

3.

...
FROM ...
WHERE [payment-date] BETWEEN #1/1/2022# AND #12/31/2022#
GROUP BY ...

 

[access2007query]

Thank you ebs17 - so I think I now have :

q4. Please confirm,

SELECT [cname],
Sum ( IIf ( Code LIKE "SUB*" , Detail.€ , Null ) ) AS subscriptions,
Sum ( IIf ( Code LIKE "DON*" , Detail.€ , Null ) ) AS donations,
Sum ( IIf ( Code NOT ( LIKE "SUB*" OR LIKE <>"DON*" ) , Detail.€ , Null ) ) AS other
FROM <JOIN ON clauses here>
WHERE [payment-date] BETWEEN #1/1/2022# AND #12/31/2022#
GROUP BY [cname]
;

one more confirmatory question regarding JOIN !
Our database tables are already very simply joined with one or more (or potentially zero*) payments for each contact, and one or more (or potentially zero*) details for each payment.
* possible missing records are not enforced by our database design as far as I know; if they occur it would be the result of a human mistake.
I had understood that if it is not specified, a SELECT will automatically create a JOIN per the data description, but I seem to be wrong. I do also understand the need for explicit statements in order to be well-reminded of what is being requested, for future, maintenance, next minute, etc.

q5. Please confirm whether one obliged to include explicit JOINs? - for this SELECT i our example? and in general?

 

[CJ_London]

if you set relationships, then when creating a query in the QBE, those relationships will automatically create a join based on those relationships. Depending on the requirements of the query you may need to modify the join.

From your comments it does not look like you have specified relationships so you will need to create the joins yourself. Or if you have, at least use the QBE to create the joins for you.

Each table should have a primary key - yours have not.
Using non alpha numeric characters as field names can lead to problems (you have € and dd/mm/yyyy)
from the example data provided, it looks like the code field in your details table is free typed (i.e. not selected from a list) - this means that typos may give you a wrong result.

Impact of these are that you are gradually painting yourself into a corner requiring overly complex code to get out of it.

 

[access2007query]

thank you CJ_London.
- and my apologies if I'm not being clear enough, or using plain English instead of appropriate Access-speak: I am learning -

Yes, they do have a relationship, which I called a link, (contact id) from contacts to payments, and (payment id) from payments to details.

No, they do not have non-alphanumeric key and field names, it is an error on my part to have written € in a text table, instead of 'amount' or the data-item-name '[REGLEMENTS].Montant' when I was trying to explain the situation in plain English above. sorry

so I think I am now here:

SELECT [cname],
Sum ( IIf ( Code LIKE "SUB*" , Detail.amount , Null ) ) AS subscriptions,
Sum ( IIf ( Code LIKE "DON*" , Detail.amount , Null ) ) AS donations,
Sum ( IIf ( Code NOT ( LIKE "SUB*" OR LIKE <>"DON*" ) , Detail.amount , Null ) ) AS other
FROM <JOIN ON clauses here>
WHERE [payment-date] BETWEEN #1/1/2022# AND #12/31/2022#
GROUP BY [cname]
;
q6. Please confirm or criticise ?

q7. Out of curiosity, would we get the same result if we put sum ( iif ( ( <condition> ) , Detail.amount , 0 ) ) ?

/ thanks in advance

 

[Pat Hartman]

Please confirm whether one obliged to include explicit JOINs?

If you don't use explicit joins, you will create Cartesian Products. Every row in tbl1 will be joined with every row in tbl2. So, the answer in general is yes.

When the data on the many side of the join is optional, use a Left Join rather than an Inner Join. That will return all rows of tbl1 even when there is no match in tbl2.

Out of curiosity, would we get the same result if we put sum ( iif ( ( <condition> ) , Detail.amount , 0 ) ) ?

Yes, and the expression would make more sense. The expression as written only works because of a trick. The trick being that the aggregate functions ignore null.

The crosstab is the simplest way to the solution. Dumbing down the application to use something more complicated and inferior doesn't make the app better or easier to understand.

 

[ebs17]

' this will never work
Code NOT ( LIKE "SUB*" OR LIKE <>"DON*" )

' better
Code NOT LIKE "SUB*" OR Code NOT LIKE "DON*"

' or
Left(Code, 3) <> "SUB" OR Left(Code, 3) <> "DON"

' or
Code < "DOM"

Overall, the problem begins with the content in the code. A comparison for equality is more efficient than a pattern comparison or necessary string processing using Left/Mid/Instr & Co.

Attempting a complete statement (I'm not good at copying pictures), based on the data shown:

SELECT
   [cname],
   SUM(IIf(Code LIKE "SUB*", Detail.amount, 0)) AS subscriptions,
   SUM(IIf(Code LIKE "DON*", Detail.amount, 0)) AS donations,
   SUM(IIf(Code < "DOM", Detail.amount, 0)) AS other
FROM
   (contact
      INNER JOIN payment
      ON contact.id = payment.cid
   )
   INNER JOIN detail
   ON payment.id = detail.pid
WHERE
   [payment-date] BETWEEN #1/1/2022# AND #12/31/2022#
GROUP BY
   [cname]

You could also use a cross table query, you just have to work up the data in an additional query beforehand.

 

[June7]

I don't think would need an additional query to accomplish CROSSTAB.

Start with a SELECT query in the designer to get JOINs correct. Then click CROSSTAB on ribbon. Pull down name field as RowHeading, Amount as Value with Sum, and calculate a field for ColumnHeader: Switch(Code LIKE "Sub*", "subscription", Code LIKE "Don*", "donation", True, "Other")

 

[ebs17]

calculate a field for ColumnHeader

Yes, that's what I meant by the extra effort. By additional query I mean higher level processing in the existing query or possibly building in a subquery that does something like that. I think it's possible that there can be even more surprises in the "Code" field, for example ...

 

[June7]

Sum(IIf(Code NOT LIKE "SUB*" AND Code NOT LIKE "DON*", Detail.amount, Null)) AS other

 

[access2007query]

thank you all so much for your timely help
we shall try both paths, -1-SQL query and -2- crosstab, and report back to you
[NB. for various reasons -1-may well have to wait until Thursday -2- will be later: please be patient, I too will have to wait probably]

 

[access2007query]

Thank you! @June7 @ebs17 @Pat Hartman @CJ_London @MsAccessNL
We have learned a lot! We tried your suggestion(s) and it(they) worked ! after some struggles with square brackets*.
This is the result per our database field-names, and also expressed with my 'simplified' field-names. I hope you now can see why I prefer simpler names, as I do.

One last question for you all -
* What are the reasons for needing square brackets in some places and not in others? Can this be simplified?
NB. We may have included extraneous un-necessary ones; I am not certain.

in our database
SELECT [Liste des CONTACTS].NumContact,
Sum(IIf([DETAIL REGLEMENT].CodeP Like "ADH*",Montant,0)) AS adhésions,
Sum(IIf(CodeP Like "DON*",Montant,0)) AS don,
Sum(IIf((CodeP<"DOM" AND blabla ),Montant,0)) AS autre
FROM ([Liste des CONTACTS] INNER JOIN REGLEMENTS ON [Liste des CONTACTS].[NumContact] = REGLEMENTS.[NumContact])
INNER JOIN [DETAIL REGLEMENT] ON REGLEMENTS.[NumReglement] = [DETAIL REGLEMENT].[NumReglement]
WHERE (((REGLEMENTS.DateReglement) Between #1/1/2022# And #12/31/2022#))
GROUP BY [Liste des CONTACTS].NumContact;
- - - -
in simple English
SELECT id , cname ,
Sum(IIf(code Like "ADH*",Montant,0)) AS adhésions ,
Sum(IIf(code Like "DON*",Montant,0)) AS don
FROM (Contacts INNER JOIN REGLEMENTS ON Contacts.id= Payments.cid)
INNER JOIN Details ON REGLEMENTS.[id] = Details.pid
WHERE (((REGLEMENTS.DateReglement) Between #1/1/2022# And #12/31/2022#))
GROUP BY id ;

 

[June7]

Don't make names with spaces and punctuation/special characters (underscore would be only exception) nor use reserved words as names.

In your examples it is use of spaces that requires use of brackets to define object names otherwise the SQL would not know where names begin and end.

 

[access2007query]

My list of things to do in reorganising this context now includes:

1. attempt to introduce the concept and use of CROSSTAB in French with my friend
2. modify table names and field names (database has been in for many years), shortening them and removing spaces
3. eliminate use of Excel, except in urgent situations or one-off outside the office, by creating new queries
4. generalise/parametrise some existing queries