Help with SQL code within Excel

[victorsales]

Hi guys,

I hope you can help me with the SQL query below that I am running in Excel via the Data->Connection, error: Syntax error in FROM clause.

Long story short, I used to do a data dump from our database into MS Access and then run the SQL code below from within Excel. It still works nicely when querying Access so I am uncertain as to why it doesn't work in Excel.

The Access data dump however is too big and time consuming to run and update so the aim was to use the 3 tables now downloaded directly in Excel as opposed to query the Access Database.

I have tested parts of the code and they work in isolation but when joining the tables I get the error: Syntax error in FROM clause

Full Code (working when pointing to Access) but not within Excel:

SELECT 
'GTECH' AS COMPANY,
T3.*,
T3.[AMTS] + IIF(T1.[AMOUNT] IS NULL, 0, T1.[AMOUNT]) AS BALANCE
FROM
(
SELECT [GTECH AUDIT SPLIT$A:BT].[ACCOUNT_REF],
[GTECH AUDIT SPLIT$A:BT].[TRAN_NUMBER],
[GTECH AUDIT SPLIT$A:BT].[INV_REF],
[GTECH AUDIT SPLIT$A:BT].[DATE],
[GTECH AUDIT SPLIT$A:BT].[SPLIT_NUMBER],
[GTECH AUDIT SPLIT$A:BT].[TYPE],
T2.[DUE_DATES],
[GTECH AUDIT SPLIT$A:BT].[GROSS_AMOUNT] AS AMTS
FROM
[GTECH AUDIT SPLIT$A:BT]
LEFT JOIN (
SELECT [HEADER_NUMBER], MAX([DUE_DATE]) AS DUE_DATES
FROM [GTECH AUDIT HEADER$A:CG]
GROUP BY [HEADER_NUMBER]
) AS T2
ON [GTECH AUDIT SPLIT$A:BT].[HEADER_NUMBER] = T2.[HEADER_NUMBER]
WHERE ([GTECH AUDIT SPLIT$A:BT].[TYPE] = 'SI' OR [GTECH AUDIT SPLIT$A:BT].[TYPE] = 'SC' OR [GTECH AUDIT SPLIT$A:BT].[TYPE] = 'SA' OR [GTECH AUDIT SPLIT$A:BT].[TYPE] = 'SP') AND [GTECH AUDIT SPLIT$A:BT].[DELETED_FLAG] = 0 AND [GTECH AUDIT SPLIT$A:BT].[DATE] < #2019-03-23#
) AS T3
LEFT JOIN (
SELECT [SPLIT_NUMBER], SUM([AMT3]) AS AMOUNT
FROM
(
SELECT [SPLIT_NUMBER], [AMOUNT] * IIF([TYPE] = 'SI' OR [TYPE] = 'SP',1,-1) AS AMT3
FROM [GTECH AUDIT USAGE$A:O]
WHERE [GTECH AUDIT USAGE$A:O].[DELETED_FLAG] = 0 AND [GTECH AUDIT USAGE$A:O].[DATE] < #2019-03-23#
)
GROUP BY [SPLIT_NUMBER]) AS T1
ON T3.[SPLIT_NUMBER] = T1.[SPLIT_NUMBER]
WHERE ABS(T3.[AMOUNT] + IIF(T1.[AMOUNT] IS NULL, 0, T1.[AMOUNT])) > 0.01)

Now this bit here works in isolation

SELECT 
'GTECH' AS COMPANY,
T3.*,
T3.[AMTS]               [B]PLEASE NOTE THAT I HAVE REMOVED THE IF STATEMENT LINKING TO T1 AS IT WASN'T DEFINED ON THIS BIT OF CODE[/B]
FROM
(
SELECT [GTECH AUDIT SPLIT$A:BT].[ACCOUNT_REF],
[GTECH AUDIT SPLIT$A:BT].[TRAN_NUMBER],
[GTECH AUDIT SPLIT$A:BT].[INV_REF],
[GTECH AUDIT SPLIT$A:BT].[DATE],
[GTECH AUDIT SPLIT$A:BT].[SPLIT_NUMBER],
[GTECH AUDIT SPLIT$A:BT].[TYPE],
T2.[DUE_DATES],
[GTECH AUDIT SPLIT$A:BT].[GROSS_AMOUNT] AS AMTS
FROM
[GTECH AUDIT SPLIT$A:BT]
LEFT JOIN (
SELECT [HEADER_NUMBER], MAX([DUE_DATE]) AS DUE_DATES
FROM [GTECH AUDIT HEADER$A:CG]
GROUP BY [HEADER_NUMBER]
) AS T2
ON [GTECH AUDIT SPLIT$A:BT].[HEADER_NUMBER] = T2.[HEADER_NUMBER]
WHERE ([GTECH AUDIT SPLIT$A:BT].[TYPE] = 'SI' OR [GTECH AUDIT SPLIT$A:BT].[TYPE] = 'SC' OR [GTECH AUDIT SPLIT$A:BT].[TYPE] = 'SA' OR [GTECH AUDIT SPLIT$A:BT].[TYPE] = 'SP') AND [GTECH AUDIT SPLIT$A:BT].[DELETED_FLAG] = 0 AND [GTECH AUDIT SPLIT$A:BT].[DATE] < #2019-03-23#
) AS T3

The bit below - soon after the LEFT JOIN - also works in isolation:

SELECT [SPLIT_NUMBER], SUM([AMT3]) AS AMOUNT
FROM
(SELECT [SPLIT_NUMBER], [AMOUNT] * IIF([TYPE] = 'SI' OR [TYPE] = 'SP',1,-1) AS AMT3
FROM [RESELLERS AUDIT USAGE$A:O]
WHERE [RESELLERS AUDIT USAGE$A:O].[DELETED_FLAG] = 0 AND [RESELLERS AUDIT USAGE$A:O].[DATE] < #2019-03-23#)
GROUP BY [SPLIT_NUMBER]

But when I copy the whole code, including the LEFT JOIN between the bits of code shown above I get the error: Syntax error in FROM clause.

I have noticed that the SQL inside Excel is particularly sensitive to [], (), commas, ' instead of " etc. so I think I am missing a () somewhere but can't figure out where. Tried in multiple places.

Any help will be appreciated.
Vic
PS: I know the code is convoluted but I had to replace all the initial shortcuts with the full table names and ranges to get the bits of code to work, sorry.

 

[bobsan42]

You certainly have 1 ) too many. But at this time I cannot wrap my head around it really.

 

[bobsan42]

I think it is the last one you have to remove. or add one between WHERE and ABS on the last line.

 

[victorsales]

It was indeed the last b*****d of a ).
How small details can change your life!
Many Thanks bobsan42!!!!

 

[bobsan42]

you are welcome. I am glad I was right 'cos it took ages to break it in blocks that made sense to me

 

[victorsales]

Appreciate your help. I looked at that code for so long that I couldn't see the ) anymore...
You spotted it first time so well done.