By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Problem Combining Queries

100+
P: 119
I am trying to combine two queries into one UNION query. The first sub-query works, but the second creates the following error at the end of the first iif statement: "Syntax error in union query"

Does anybody know why?

Expand|Select|Wrap|Line Numbers
  1. Select Symbol, MarketValue AS [Value], Type, RiskType FROM
  2. [SELECT qryReportPositions.Symbol, Sum(qryReportPositions.MV) as MarketValue, qryReportPositions.Type, "Position" AS RiskType
  3. FROM qryReportPositions
  4. GROUP BY qryReportPositions.Symbol, qryReportPositions.Type, "Position"
  5. HAVING (((qryReportPositions.Type)="Common Stock"))
  6. ]. AS tmpVarPositions
  7.  
  8. UNION 
  9. SELECT Symbol, Sum(MarketValue), AlteredType as Type, RiskType
  10. FROM 
  11.  
  12. [SELECT IIf([Type]="Currency",qryReportPositions.Symbol,[PriceCurrency]) AS Symbol, "Currency" AS AlteredType, Sum(qryReportPositions.MV) AS MarketValue, IIf([PriceCurrency]<>"USD","Currency","Position") AS RiskType
  13. FROM qryReportPositions
  14. GROUP BY IIf([Type]="Currency",qryReportPositions.Symbol,[PriceCurrency]), "Currency", IIf([PriceCurrency]<>"USD","Currency","Position"), qryReportPositions.Type, qryReportPositions.PriceCurrency
  15. HAVING (((qryReportPositions.Type)="Currency" Or (qryReportPositions.Type)="Fund")) OR (((qryReportPositions.PriceCurrency)<>"USD"))
  16. ]. AS tmpVarCurrency
  17. GROUP BY Symbol, AlteredType, RiskType
  18. ORDER BY Symbol;
  19.  
The query I was originally using was:

Expand|Select|Wrap|Line Numbers
  1. Select  Symbol, Value, Type, RiskType 
  2. FROM qryVARPositions
  3. UNION SELECT Symbol, Sum(MarketValue), AlteredType as Type, RiskType
  4. FROM qryVARCurrency
  5. GROUP BY Symbol, AlteredType, RiskType
  6. ORDER BY Symbol;
  7.  
I tried using nested queries with the ultimate aim of moving the queries to VBA only. Is this too ambitious?!
May 28 '08 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I think you may be suffering from a problem described in Access QueryDefs Mis-save Subquery SQL.
May 29 '08 #2

100+
P: 119
I think it is along those lines. I replaced references to fields in the form "[FieldName]" to "tblName.FieldName" and it worked after that. So definitely related to the use of square brackets. The following code works:

Expand|Select|Wrap|Line Numbers
  1. Select Symbol, MarketValue AS [Value], Type, RiskType FROM
  2. [SELECT qryReportPositions.Symbol, Sum(qryReportPositions.MV) as MarketValue, qryReportPositions.Type, "Position" AS RiskType
  3. FROM qryReportPositions
  4. GROUP BY qryReportPositions.Symbol, qryReportPositions.Type, "Position"
  5. HAVING (((qryReportPositions.Type)="Common Stock"))
  6. ]. AS tmpVarPositions
  7.  
  8. UNION
  9.  
  10. SELECT Symbol, Sum(MarketValue), AlteredType as Type, RiskType
  11. FROM
  12. [SELECT IIf(qryReportPositions.Type="Currency",qryReportPositions.Symbol,qryReportPositions.PriceCurrency) AS Symbol, "Currency" AS AlteredType, Sum(qryReportPositions.MV) AS MarketValue, IIf(qryReportPositions.PriceCurrency<>"USD","Currency","Position") AS RiskType
  13. FROM qryReportPositions
  14. GROUP BY IIf(qryReportPositions.Type="Currency",qryReportPositions.Symbol,qryReportPositions.PriceCurrency), "Currency", IIf(qryReportPositions.PriceCurrency<>"USD","Currency","Position"), qryReportPositions.Type, qryReportPositions.PriceCurrency
  15. HAVING (((qryReportPositions.Type)="Currency" Or (qryReportPositions.Type)="Fund")) OR (((qryReportPositions.PriceCurrency)<>"USD"))
  16. ]. AS tmpVarCurrency
  17.  
  18. GROUP BY Symbol, AlteredType, RiskType
  19. ORDER BY Symbol;
May 30 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
LOL.

That's not what the article is about - but who cares if it enabled you to find and fix your problem :D

Am I right in thinking you use Access 2003 or later? Those versions are more forgiving of the bug that Access introduces into your SQL. It is present in yours, but obviously not added by you, but by Access itself. The article goes into greater detail if you're interested, but I'm pleased to see that you resolved your problem anyway.

PS. That also means that what I thought was your problem wasn't. You've got to love irony huh?
Jun 2 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.