Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 11th, 2004, 11:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subquery - why won't it work?

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
 
Old August 11th, 2004, 03:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old August 11th, 2004, 05:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 12th, 2004, 07:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old August 13th, 2004, 10:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 13th, 2004, 11:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Subquery debbiecoates SQL Server 2000 4 June 25th, 2008 03:49 AM
Do i have to use a subquery, if yes then how? code_lover SQL Language 2 January 2nd, 2007 02:22 PM
subquery khansa MySQL 1 February 21st, 2006 02:49 AM
Subquery SQLServerCE jfmador SQL Language 1 February 16th, 2006 06:36 AM
Using wildcards in subquery hman SQL Language 2 April 11th, 2005 06:18 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.