query won't import to excel

  • Thread starter Thread starter Thos @ fodc
  • Start date Start date
T

Thos @ fodc

I have a few queries that I have imported to excel using its "get external
data"
method, but when I try to set up a link to a new query, I get an error when
I select "return data to excel":

Too few parameters. Expected 11.

This is with a large query that has quite a few sub selects. Is there
something in particular I'm doing wrong? The query works fine in Access.


This is my query:

SELECT qMacroCountData.MacroSessID, qMacroCountData.SampleLocation,
qMacroCountData.Date, qMacroCountData.SampleCode,
qMacroCountData.DataEntryDateTime, (SELECT Count(Ab.Name) as AbCount FROM
qMacroCountData as Ab WHERE (Ab.Name NOT LIKE "extras" and Ab.MacroSessID =
qMacroCountData.MacroSessID) GROUP BY Ab.MacroSessID) AS [Taxa Richness],
(SELECT Count(t1.Name) FROM qMacroCountData as t1 WHERE ((
(t1.UniqueName)="Eph") and (t1.MacroSessID = qMacroCountData.MacroSessID))
GROUP BY t1.MacroSessID) AS [Eph Taxa], (SELECT Count(t1.Name) FROM
qMacroCountData as t1 WHERE (( (t1.UniqueName)="Ple") and (t1.MacroSessID =
qMacroCountData.MacroSessID)) GROUP BY t1.MacroSessID) AS [Ple Taxa],
(SELECT Count(t1.Name) FROM qMacroCountData as t1 WHERE ((
(t1.UniqueName)="Tri") and (t1.MacroSessID = qMacroCountData.MacroSessID))
GROUP BY t1.MacroSessID) AS [Tri Taxa], (SELECT Count(t1.Name) AS EPTCount
FROM qMacroCountData as t1 WHERE ((t1.UniqueName="Tri" Or
t1.UniqueName="Ple" Or t1.UniqueName="Eph") AND t1.MacroSessID =
qMacroCountData.MacroSessID) GROUP BY t1.MacroSessID) AS [EPT Taxa], (SELECT
Sum(Ab.Count) as AbSum FROM qMacroCountData as Ab WHERE (Ab.Name NOT LIKE
"extras" and Ab.MacroSessID = qMacroCountData.MacroSessID) GROUP BY
Ab.MacroSessID) AS AllSum, (SELECT Sum(t1.Count) FROM qMacroCountData as t1
WHERE (((t1.UniqueName)="Tri" Or (t1.UniqueName)="Ple" Or
(t1.UniqueName)="Eph") and (t1.MacroSessID = qMacroCountData.MacroSessID))
GROUP BY t1.MacroSessID)/[AllSum]*100 AS [EPT Index], (SELECT Sum(t1.Count)
FROM qMacroCountData as t1 WHERE (((t1.UniqueName)="Tri" Or
(t1.UniqueName)="Ple" Or (t1.UniqueName)="Eph") AND (t1.TValue <= 3) AND
(t1.MacroSessID = qMacroCountData.MacroSessID)) GROUP BY
t1.MacroSessID)/[AllSum]*100 AS [Sens EPT Index], (SELECT Sum(t1.Count)
FROM qMacroCountData as t1 WHERE (((t1.Name)="hydropsychidae") and
(t1.MacroSessID = qMacroCountData.MacroSessID)) GROUP BY
t1.MacroSessID)/[AllSum]*100 AS [HyPsy Index], (SELECT Sum(t1.Count) FROM
qMacroCountData as t1 WHERE (((t1.Name)="baetidae") and (t1.MacroSessID =
qMacroCountData.MacroSessID)) GROUP BY t1.MacroSessID)/[AllSum]*100 AS
[Baetidae Index], (SELECT Sum(t1.Count * t1.TValue) as wt FROM
qMacroCountData as t1 WHERE (t1.MacroSessID = qMacroCountData.MacroSessID)
GROUP BY t1.MacroSessID)/[AllSum] AS [Tolerance Value], (SELECT
Sum(t1.Count) FROM qMacroCountData as t1 WHERE ( (t1.TValue <= 3) AND
(t1.MacroSessID = qMacroCountData.MacroSessID)) GROUP BY
t1.MacroSessID)/[AllSum]*100 AS [Intolerant Index], (SELECT Sum(t1.Count)
FROM qMacroCountData as t1 WHERE ( (t1.TValue >= 8) AND (t1.MacroSessID =
qMacroCountData.MacroSessID)) GROUP BY t1.MacroSessID)/[AllSum]*100 AS
[Tolerant Index], (SELECT Max(t1.Count) FROM qMacroCountData as t1 WHERE
( (t1.MacroSessID = qMacroCountData.MacroSessID)) GROUP BY
t1.MacroSessID)/[AllSum]*100 AS [Dominant Index], (SELECT Sum(t1.Count)
FROM qMacroCountData as t1 WHERE (((t1.FDesign)="CG") and (t1.MacroSessID =
qMacroCountData.MacroSessID)) GROUP BY t1.MacroSessID)/[AllSum]*100 AS [CG
Index], (SELECT Sum(t1.Count) FROM qMacroCountData as t1 WHERE
(((t1.FDesign)="FC") and (t1.MacroSessID = qMacroCountData.MacroSessID))
GROUP BY t1.MacroSessID)/[AllSum]*100 AS [FC Index], (SELECT (0 +
Sum(t1.Count)) FROM qMacroCountData as t1 WHERE (((t1.FDesign)="SC") and
(t1.MacroSessID = qMacroCountData.MacroSessID)) GROUP BY
t1.MacroSessID)/[AllSum]*100 AS [SC Index], (SELECT Sum(t1.Count) FROM
qMacroCountData as t1 WHERE (((t1.FDesign)="P") and (t1.MacroSessID =
qMacroCountData.MacroSessID)) GROUP BY t1.MacroSessID)/[AllSum]*100 AS [P
Index], (SELECT Sum(t1.Count) FROM qMacroCountData as t1 WHERE
(((t1.FDesign)="SH") and (t1.MacroSessID = qMacroCountData.MacroSessID))
GROUP BY t1.MacroSessID)/[AllSum]*100 AS [SH Index], (SELECT Sum(Ac.Count)
as AcSum FROM qMacroCountData as Ac WHERE Ac.MacroSessID =
qMacroCountData.MacroSessID GROUP BY Ac.MacroSessID) AS GrandTotal
FROM qMacroCountData
GROUP BY qMacroCountData.MacroSessID, qMacroCountData.SampleLocation,
qMacroCountData.Date, qMacroCountData.SampleCode,
qMacroCountData.DataEntryDateTime, qMacroCountData.MacroSessID;

T
 
Back
Top