Excel Query from Access Query
From
Bruno Campanini@21:1/5 to
All on Tue Nov 24 17:59:16 2020
I have an Access query that looks like this:
SELECT [1_GEN].F1,
Sum([1_GEN].[F2]+[2_FEB].[F2]) AS FF2,
Sum([1_GEN].[F3]+[2_FEB].[F4]) AS FF3,
Sum([1_GEN].[F4]+[2_FEB].[F4]) AS FF4,
Sum([1_GEN].[F5]+[2_FEB].[F5]) AS FF5,
Sum([1_GEN].[F6]+[2_FEB].[F6]) AS FF6,
[FF2]+[FF3]+[FF4]+[FF5]+[FF6] AS S
FROM 1_GEN
INNER JOIN 2_FEB ON [1_GEN].F1 = [2_FEB].F1
GROUP BY [1_GEN].F1;
I have transformed to Excel in this way:
SQL = "SELECT [GEN$A1:A100].F1,"
SQL = SQL & "Sum([GEN$A1:A100].F2 + [FEB$A1:A100].F2) AS FF2, "
SQL = SQL & "Sum([GEN$A1:A100].F3 + [FEB$A1:A100].F3) AS FF3,"
SQL = SQL & "Sum([GEN$A1:A100].F4 + [FEB$A1:A100].F4) AS FF4,"
SQL = SQL & "Sum([GEN$A1:A100].F5 + [FEB$A1:A100].F3) AS FF5,"
SQL = SQL & "Sum([GEN$A1:A100].F6 + [FEB$A1:A100].F4) AS FF6,"
SQL = SQL & "FF2+FF3+FF4+FF5+FF6 AS S "
SQL = SQL & "FROM [GEN$A1:A100] "
SQL = SQL & "INNER JOIN [FEB$A1:A100] ON [GEN$A1:A100].F1 =
[FEB$A1:A100].F1 "
SQL = SQL & "GROUP BY [GEN$A1:A100].F1;"
in order to run it, from Excel, with:
Set RS = xlDB.OpenRecordset(SQL, dbOpenDynaset)
Destination(2, 1).CopyFromRecordset RS
But it does'n work!
Eoor:
Two few parameters, expected 8.
The error seems to be in the four "Sum(... expression.
Any suggestion?
Bruno
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)