Subqueries cannot be used in the expression....(but I'm not using a subquery)

[Access_Novice]

I had 16 columns in the design grid with one more blank one. In that last column I entered a calculated column. This calculated columns references two other calculated columns and takes the difference between them. When I execute the query, I get an error message stating Subqueries cannot be used in the expression (Sum([calculatedField1] - [calculatedField2])).

Why am I getting an error message about a subquery when I'm not even doing a subquery? Not only that, why does the error message think I'm using the Summing the two other calculated fields when I'm subtracting them?

Also I thought that maybe this error might have something to do with being near the end of all available columns in the design grid. I dragged some new fields into the design grid, then overwrote those fields with the calculated field I am entering. This didn't work. I also deleted the first two fields in the design grid to make room, then entered my calculated field. Still the same error.



I am also using the Group By feature with Sum under the calculated columns since those return numbers.

[orange]

I think it would be helpful if you showed the SQL code or a screen capture. Perhaps someone will see an issue.....

[CJ_London]

you can only sum data in the underlying data - your calculated field is not in the table

so if your first calculated field is a*b and the second is c*d then try

sum((a*b)+(c*d))

I also deleted the first two fields in the design grid

note you can have up to 255 columns in a query

[Access_Novice]

I think it would be helpful if you showed the SQL code or a screen capture. Perhaps someone will see an issue.....

Here is my code:

SELECT [TimesheetDate]+7-Weekday([TimesheetDate],2) AS LockheedWE, DateAdd("m",1,DateSerial(Format([TimesheetDate],"yyyy"),Format([TimesheetDate],"m"),1))-1 AS EOMonth, Unanet.Company, Unanet.EmployeeType, Unanet.BillingAccountNo, [LastName] & ", " & [FirstName] AS FullName, Unanet.ProjectName, Unanet.ServiceItem, Unanet.TaskName, [VIP] & [SCATcode] AS VIPno, Unanet.ExostarLineNo, Unanet.LaborCat, Sum(Unanet.UnanetHours) AS UnanetHours, Unanet.UnanetBillingRate, Sum(Round([UnanetHours]*[UnanetBillingRate],2)) AS UnanetInvoiceAmt
FROM Unanet
GROUP BY [TimesheetDate]+7-Weekday([TimesheetDate],2), DateAdd("m",1,DateSerial(Format([TimesheetDate],"yyyy"),Format([TimesheetDate],"m"),1))-1, Unanet.Company, Unanet.EmployeeType, Unanet.BillingAccountNo, [LastName] & ", " & [FirstName], Unanet.ProjectName, Unanet.ServiceItem, Unanet.TaskName, [VIP] & [SCATcode], Unanet.ExostarLineNo, Unanet.LaborCat, Unanet.UnanetBillingRate
HAVING (((Unanet.ProjectName)="CAMEO LB Option Year 1"));

If I remove the field with the 2nd occurrence of the SUM function, the query will execute. If I leave in both SUM functions as I have in the code above (trying to add hours and invoice amount), the code will execute, but I don't see that the hours and invoice amount have been added in cases where the values of all other fields are the same. For example, I might see 3 particular rows that have:

Name WeekEnding Hours Billing Rate InvoiceAmount

Smith, Steve 05/24/2015 2 $50 $100
Smith, Steve 05/24/2015 5 $50 $250
Smith, Steve 05/24/2015 9 $50 $450

In this example, I am trying to get:

Name WeekEnding Hours Billing Rate InvoiceAmount

Smith, Steve 05/24/2015 16 $50 $800

[orange]

Based on your sample records (I imported them using semicolon delimited, and had to change the date because of regional settings

sName; week ending; hours;Billing Rate; InvoiceAmount
Smith, Steve; 24/05/2015; 2; $50; $100
Smith, Steve; 24/05/2015; 5; $50; $250
Smith, Steve; 24/05/2015; 9; $50; $450


)

SELECT BillingData.sName
	,Sum(BillingData.hours) AS SumOfhours
	,BillingData.[week ending]
	,BillingData.[Billing Rate]
	,Sum(BillingData.InvoiceAmount) AS SumOfInvoiceAmount
FROM BillingData
GROUP BY BillingData.sName
	,BillingData.[week ending]
	,BillingData.[Billing Rate];

returns

sName
SumOfhours
week ending
Billing Rate
SumOfInvoiceAmount

Smith, Steve
16
24/05/2015
$50.00
$800.00

[Access_Novice]

Based on your sample records (I imported them using semicolon delimited, and had to change the date because of regional settings

sName; week ending; hours;Billing Rate; InvoiceAmount
Smith, Steve; 24/05/2015; 2; $50; $100
Smith, Steve; 24/05/2015; 5; $50; $250
Smith, Steve; 24/05/2015; 9; $50; $450


)

SELECT BillingData.sName
    ,Sum(BillingData.hours) AS SumOfhours
    ,BillingData.[week ending]
    ,BillingData.[Billing Rate]
    ,Sum(BillingData.InvoiceAmount) AS SumOfInvoiceAmount
FROM BillingData
GROUP BY BillingData.sName
    ,BillingData.[week ending]
    ,BillingData.[Billing Rate];

returns

sName
SumOfhours
week ending
Billing Rate
SumOfInvoiceAmount

Smith, Steve
16
24/05/2015
$50.00
$800.00

I should have clarified the invoice amounts more. In the example I specified the invoice amounts. I should have said in order to derive the invoice amounts, I am using a calculated field. This appears to be where I am running into problems. This calculated field can be seen in my SQL code in bold. It is the 2nd occurrence of SUM which is the last column in the SELECT statement. To derive the values in this calculated column I have to multiply two other columns together. Then I am applying the SUM aggregate function to this column of invoice amounts, just like I am for the hours.

[June7]

The problem is UnanetHours is name of table field and also name of aggregate calc field. Change the aggregate calc field name to something different from the native field, such as SumUnanetHours.