Aggregate query calculations

[ficmessenger]

I have an aggregate query that totals fields in Table A, grouped by the primary key. The query has an expression (Expr 1) which sums a couple of the totals fields and works fine.

Table B uses the same primary key and has an integer field (AMOUNT)

I want a further field (Expr 2) to deduct Expr 1 from the integer field, viz:

Expr 2: [Table A]![AMOUNT]-Expr 1 - and choosing Expression in the Total field.

However it does not work, coming up with error: "Your query does not include the specified expression 'Expr 2: [Table A]![AMOUNT]-Expr 1' as part of an aggregate function.

Is there any way of getting this to work?

[xps35]

I have an aggregate query that totals fields in Table A, grouped by the primary key.

I don't understand this. There is only one record per primary key value. So no much to total there.
Please explain and show the query.

[Welshgasman]

Refer to the query and bring in Expr1 with a join, but do give it some sort of meaningful name?

[orange]

I recommend that you copy and paste the SQL of your query in the forum. Readers need some context for responding to your issue.

[June7]

Not much of your narrative makes sense. GROUP BY on primary key does not aggregate anything. How does TableB 'uses the same primary key' - do you mean it is foreign key field holding PK from TableA?

Suggest you provide sample tables of raw data and desired output. Build tables in post or attach file.

[ficmessenger]

OK, some more detail then. Sorry, you will need to explain how I get SQL. I am only an intermediate user. I would prefer to not attach the file.

Table B holds student data, plus a field called AMOUNT which is an integer. Each Student has an ID, a primary key.

Table A holds register data linked to Table B by the primary key. One of the fields has dates. Other fields have attendance details. In the aggregate query, the fields are grouped by ("Group By") the primary key, totalling the attendance details (Sum). When the query is run, the user defines the start and end date to be used to group the totals.

Two of the Table A fields have a pupil present and a pupil absent field and these two totals are added together into Expr 1 (Expression). So there may be data like:
Month Attendances / Absences
31/10/22 2 / 2
30/11/22 4 / 0

So when the query is run, with user input of 01/10/22 and 30/11/22, it will show totals of 6 Attendance and 2 Absences. I also have an Expr1 field that totals the two, and this will come up with a total of 8 with the same input. This I have got working fine.

However, I want to deduct Expr1 from the integer that is held in Table B, this I am unable to get to work, getting the error message in my OP.

[June7]

So you are grouping by the foreign key field in TableA, not a primary key field. This is the StudentID? You want to aggregate by student?

Switch query object to SQLView to get the SQL statement.

Can use Advanced post editor to build tables or for attaching file follow instructions at bottom of my post.

[ficmessenger]

SELECT Students.AccID, Students.StudentSur, Sum(RegisterData.[Catch Ups (Mins)]) AS [SumOfCatch Ups (Mins)], Sum(RegisterData.PP) AS SumOfPP, Sum(RegisterData.PC) AS SumOfPC, Sum(RegisterData.PH) AS SumOfPH, Sum(RegisterData.TC) AS SumOfTC, [SumOfPC]+[SumOfPP] AS Expr1, [Students]![Gross Quarterly Lesson Ent]-[Expr1] AS Expr2
FROM Students INNER JOIN RegisterData ON Students.AccID = RegisterData.AccRegID
WHERE (((RegisterData.RegMonthEnd) Between [A] And [B] And (RegisterData.RegMonthEnd)=[Latest Register Entered must not be before]) AND ((Students.StudentStatus)="MEMBDM")) OR (((Students.StudentStatus)="MEMBDQ"))
GROUP BY Students.AccID, Students.StudentSur;

Bolded bit is the problem

[June7]

Does the Expr1 calculate properly? Okay, you say that part does work.

I need to work with data to analyze this. I built some tables. Cannot reference [Gross Quarterly Lesson Ent] field in expression unless it is also brought separately into query as a grouping field or wrap field in an aggregate function.

First([Gross Quarterly Lesson Ent])-[Expr1] AS Expr2

[xps35]

Try

[Students]![Gross Quarterly Lesson Ent]-[SumOfPC]+[SumOfPP] AS Expr2

[June7]

@xps35, nope, doesn't work - same error. See post #9.

[CJ_London]

I don't see how [SumOfPC]+[SumOfPP] AS Expr1 could work - if grouping, the name of the sum column is not available at that level

Try

SELECT Students.AccID, Students.StudentSur, Sum(RegisterData.[Catch Ups (Mins)]) AS [SumOfCatch Ups (Mins)], Sum(RegisterData.PP) AS SumOfPP, Sum(RegisterData.PC) AS SumOfPC, Sum(RegisterData.PH) AS SumOfPH, Sum(RegisterData.TC) AS SumOfTC, Sum(RegisterData.PC)+Sum(RegisterData.PP) AS Expr1, [Students]![Gross Quarterly Lesson Ent]-(Sum(RegisterData.PC)+Sum(RegisterData.PP)) AS Expr2
FROM Students INNER JOIN RegisterData ON Students.AccID = RegisterData.AccRegID
WHERE (((RegisterData.RegMonthEnd) Between [A] And [B] And (RegisterData.RegMonthEnd)=[Latest Register Entered must not be before]) AND ((Students.StudentStatus)="MEMBDM")) OR (((Students.StudentStatus)="MEMBDQ"))
GROUP BY Students.AccID, Students.StudentSur;

alternatively if your PC and PP columns do not contain nulls try

SELECT Students.AccID, Students.StudentSur, Sum(RegisterData.[Catch Ups (Mins)]) AS [SumOfCatch Ups (Mins)], Sum(RegisterData.PP) AS SumOfPP, Sum(RegisterData.PC) AS SumOfPC, Sum(RegisterData.PH) AS SumOfPH, Sum(RegisterData.TC) AS SumOfTC, Sum(RegisterData.PC+RegisterData.PP) AS Expr1, [Students]![Gross Quarterly Lesson Ent]-Sum(RegisterData.PC+RegisterData.PP) AS Expr2
FROM Students INNER JOIN RegisterData ON Students.AccID = RegisterData.AccRegID
WHERE (((RegisterData.RegMonthEnd) Between [A] And [B] And (RegisterData.RegMonthEnd)=[Latest Register Entered must not be before]) AND ((Students.StudentStatus)="MEMBDM")) OR (((Students.StudentStatus)="MEMBDQ"))
GROUP BY Students.AccID, Students.StudentSur;

[June7]

Well, I tested, the Expr1 calc works. Did you see post #9?

[ficmessenger]

Does the Expr1 calculate properly? Okay, you say that part does work.

I need to work with data to analyze this. I built some tables. Cannot reference [Gross Quarterly Lesson Ent] field in expression unless it is also brought separately into query as a grouping field or wrap field in an aggregate function.

First([Gross Quarterly Lesson Ent])-[Expr1] AS Expr2

Thank you - that works!

In simple terms so I can break down the logic - why does FIRST make it work?

[June7]

Every field referenced in aggregate query must be in GROUP BY clause or an aggregate function or WHERE clause to filter but not display.

The JOIN to RegisterData causes Student records to replicate so they show with each associated RegisterData record. First() 'aggregates' those multiple instances of [Gross Quarterly Lesson Ent] to one for each student.