• 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)