Problem Combining Queries

[billelev]

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?

Select Symbol, MarketValue AS [Value], Type, RiskType FROM
[SELECT qryReportPositions.Symbol, Sum(qryReportPositions.MV) as MarketValue, qryReportPositions.Type, "Position" AS RiskType
FROM qryReportPositions
GROUP BY qryReportPositions.Symbol, qryReportPositions.Type, "Position"
HAVING (((qryReportPositions.Type)="Common Stock"))
]. AS tmpVarPositions
 
UNION 
SELECT Symbol, Sum(MarketValue), AlteredType as Type, RiskType
FROM 
 
[SELECT IIf([Type]="Currency",qryReportPositions.Symbol,[PriceCurrency]) AS Symbol, "Currency" AS AlteredType, Sum(qryReportPositions.MV) AS MarketValue, IIf([PriceCurrency]<>"USD","Currency","Position") AS RiskType
FROM qryReportPositions
GROUP BY IIf([Type]="Currency",qryReportPositions.Symbol,[PriceCurrency]), "Currency", IIf([PriceCurrency]<>"USD","Currency","Position"), qryReportPositions.Type, qryReportPositions.PriceCurrency
HAVING (((qryReportPositions.Type)="Currency" Or (qryReportPositions.Type)="Fund")) OR (((qryReportPositions.PriceCurrency)<>"USD"))
]. AS tmpVarCurrency
GROUP BY Symbol, AlteredType, RiskType
ORDER BY Symbol;
 

The query I was originally using was:

Select  Symbol, Value, Type, RiskType 
FROM qryVARPositions
UNION SELECT Symbol, Sum(MarketValue), AlteredType as Type, RiskType
FROM qryVARCurrency
GROUP BY Symbol, AlteredType, RiskType
ORDER BY Symbol;
 

I tried using nested queries with the ultimate aim of moving the queries to VBA only. Is this too ambitious?!

[NeoPa]

I think you may be suffering from a problem described in Access QueryDefs Mis-save Subquery SQL.

[billelev]

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:

Select Symbol, MarketValue AS [Value], Type, RiskType FROM
[SELECT qryReportPositions.Symbol, Sum(qryReportPositions.MV) as MarketValue, qryReportPositions.Type, "Position" AS RiskType
FROM qryReportPositions
GROUP BY qryReportPositions.Symbol, qryReportPositions.Type, "Position"
HAVING (((qryReportPositions.Type)="Common Stock"))
]. AS tmpVarPositions
 
UNION
 
SELECT Symbol, Sum(MarketValue), AlteredType as Type, RiskType
FROM
[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
FROM qryReportPositions
GROUP BY IIf(qryReportPositions.Type="Currency",qryReportPositions.Symbol,qryReportPositions.PriceCurrency), "Currency", IIf(qryReportPositions.PriceCurrency<>"USD","Currency","Position"), qryReportPositions.Type, qryReportPositions.PriceCurrency
HAVING (((qryReportPositions.Type)="Currency" Or (qryReportPositions.Type)="Fund")) OR (((qryReportPositions.PriceCurrency)<>"USD"))
]. AS tmpVarCurrency
 
GROUP BY Symbol, AlteredType, RiskType
ORDER BY Symbol;

[NeoPa]

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?