The following query works well at summing the columns of qryTest11082004:

SELECT Sum(AAA.Total) AS SumOfTotal, Sum(AAA.CostH) AS SumOfCostH, Sum(AAA.RetailH) AS SumOfRetailH
FROM qryTest11082004 AS AAA;

qryTest11082004 is a standard, although complex, SELECT query. However, if I try replacing the word qryTest11082004 in the FROM clause with its SQL statement I get the error message "syntax error in FROM clause".

Any ideas please?



Clive Astley

__________________
Clive Astley

What is the SQL statement for qryTest11082004? I suspect that if it has criteria and is also complex that the SECOND SQL statement's single and double quotes may be confusing this FIRST query statement.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hello Greg,

Thanks for the reply. I am going back over some of my earlier work and trying to replace "chained queries" with one SQL statement. Following is the SQL statement for the other query:

SELECT InputData.EntryNumber, InputData.ReturnID, InputData.RecordType, InputData.PaymentType, InputData.Supplier, InputData.Description, InputData.Total, (SELECT CMKPDATA.Markup
FROM CMKPDATA
WHERE (((CMKPDATA.ClientRefNo)=[Forms]![frmCombinedForm]![text39]) AND ((CMKPDATA.ActiveFrom)<[Forms]![frmCombinedForm].[Text37] Or (CMKPDATA.ActiveFrom)<[Forms]![frmCombinedForm].[Text37]) AND ((CMKPDATA.CeasedAt)>[Forms]![frmCombinedForm].[Text37] Or (CMKPDATA.CeasedAt) Is Null)) AND (CMKPDATA.DescriptionLookupIDCode=CInt(InputData.D escription))) AS Markup, [StandardHigh]+[VATHigh] AS CostH, [StandardLow]+[VatLow] AS CostL, InputData.Zero AS CostZ, InputData.Exempt AS CostE, [CostH]*[Markup] AS RetailH, [CostL]*[Markup] AS RetailL, [CostZ]*[Markup] AS RetailZ, [CostE]*[Markup] AS RetailE
FROM InputData
WHERE (((InputData.ReturnID)=[Forms]![frmCombinedForm]![Text33]) AND ((InputData.RecordType)="1"));


Clive Astley

1) Does the second query work well alone (any errors)?
2) Show us how you integrated it into the first. Let's see your exact coding.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for the reply.

Both queries work perfectly well standalone. qryTest11082004 produces records I want. But then I want to sum some of its columns into a one-record sum result. This works perfectly in the first example I gave; ie a query based on qryTest11082004. But what I want is just ONE query. So I tried replacing the word qryTest11082004 in the first example with its SQL statement minus the final ; But that combined query gives the syntax error.

Clive Astley

Well, I still don't know why the original method didn't work.

But I have now solved the problem by re-writing the SQL from scratch.

Many thanks for Greg's help and to everyone else who read the post.

Clive Astley