Convert Access query to Sql Server

Copper Contributor
Spoiler
I can't get two Max(IIf statement form an Access query to work in a Sql Server view. I have tried everything that I can think of and can't get it to work. Could someone help me out. The highlighted in green code is the Access code that needs to added to Sql Server or something like it. Bob

SQLServer Design for dbo.TemporaryTransactionsQuery3

 

Bob_AubryOPAanalytics_0-1716656731409.png

 

Sql Server code that can be copied,  pasted and edited.

SELECT        TOP (100) PERCENT dbo.TemporaryTransactionsQuery2.Account, dbo.TemporaryTransactionsQuery2.ChkRef, dbo.TemporaryTransactionsQuery2.Debit, dbo.TemporaryTransactionsQuery2.Credit,

                         dbo.TemporaryTransactionsQuery2.Balance, dbo.TemporaryTransactionsQuery2.Date, dbo.TemporaryTransactionsQuery2.Description, dbo.TemporaryTransactionsQuery2.AccountName,

                         dbo.TemporaryTransactionsQuery2.Description16, dbo.TemporaryTransactionsQuery2.PayeePayor, dbo.TemporaryTransactionsQuery2.CombinedCategory, dbo.TemporaryTransactionsQuery2.Multiple,

                         dbo.TemporaryTransactionsQuery2.ID, dbo.tblTransactions.TranDate

FROM            dbo.tblTemporaryTransactions INNER JOIN

                         dbo.TemporaryTransactionsQuery2 ON dbo.tblTemporaryTransactions.ID = dbo.TemporaryTransactionsQuery2.ID INNER JOIN

                         dbo.tblTransactions ON dbo.TemporaryTransactionsQuery2.AccountName = dbo.tblTransactions.Account

GROUP BY dbo.TemporaryTransactionsQuery2.Account, dbo.TemporaryTransactionsQuery2.ChkRef, dbo.TemporaryTransactionsQuery2.Debit, dbo.TemporaryTransactionsQuery2.Credit, dbo.TemporaryTransactionsQuery2.Balance,

                         dbo.TemporaryTransactionsQuery2.Date, dbo.TemporaryTransactionsQuery2.Description, dbo.TemporaryTransactionsQuery2.AccountName, dbo.TemporaryTransactionsQuery2.Description16,

                         dbo.TemporaryTransactionsQuery2.PayeePayor, dbo.TemporaryTransactionsQuery2.CombinedCategory, dbo.TemporaryTransactionsQuery2.ID, dbo.tblTransactions.TranDate, dbo.TemporaryTransactionsQuery2.Multiple

ORDER BY dbo.TemporaryTransactionsQuery2.Date DESC

MS Access Sql code

SELECT TemporaryTransactionsQuery2.Account, TemporaryTransactionsQuery2.ChkRef, TemporaryTransactionsQuery2.Debit, TemporaryTransactionsQuery2.Credit, TemporaryTransactionsQuery2.Balance, TemporaryTransactionsQuery2.Date, TemporaryTransactionsQuery2.Description, TemporaryTransactionsQuery2.AccountName, TemporaryTransactionsQuery2.Description16, TemporaryTransactionsQuery2.PayeePayor, TemporaryTransactionsQuery2.CombinedCategory, TemporaryTransactionsQuery2.Multiple, TemporaryTransactionsQuery2.ID, Max(IIf([tblTransactions].[Account]=[TemporaryTransactionsQuery2].[AccountName] And [tblTransactions].[PayeePayor]=[TemporaryTransactionsQuery2].[PayeePayor] And [tblTransactions].[ConbinedCategory]=[TemporaryTransactionsQuery2].[CombinedCategory] And [tblTemporaryTransactions].[Date]-10>=[tblTransactions].[TranDate],1,0)) AS Expr2, Max(IIf(IsNull([tblTemporaryTransactions].[PayeePayor])=True And IsNull([tblTemporaryTransactions].[CombinedCategory])=True,1,0)) AS Expr3, TemporaryTransactionsQuery2.Type, TemporaryTransactionsQuery2.Shares, TemporaryTransactionsQuery2.Price, TemporaryTransactionsQuery2.CommFees

FROM (TemporaryTransactionsQuery2 INNER JOIN tblTemporaryTransactions ON TemporaryTransactionsQuery2.ID = tblTemporaryTransactions.ID) INNER JOIN tblTransactions ON TemporaryTransactionsQuery2.AccountName = tblTransactions.Account

GROUP BY TemporaryTransactionsQuery2.Account, TemporaryTransactionsQuery2.ChkRef, TemporaryTransactionsQuery2.Debit, TemporaryTransactionsQuery2.Credit, TemporaryTransactionsQuery2.Balance, TemporaryTransactionsQuery2.Date, TemporaryTransactionsQuery2.Description, TemporaryTransactionsQuery2.AccountName, TemporaryTransactionsQuery2.Description16, TemporaryTransactionsQuery2.PayeePayor, TemporaryTransactionsQuery2.CombinedCategory, TemporaryTransactionsQuery2.Multiple, TemporaryTransactionsQuery2.ID, TemporaryTransactionsQuery2.Type, TemporaryTransactionsQuery2.Shares, TemporaryTransactionsQuery2.Price, TemporaryTransactionsQuery2.CommFees

HAVING (((Max(IIf(IsNull([tblTemporaryTransactions].[PayeePayor])=True And IsNull([tblTemporaryTransactions].[CombinedCategory])=True,1,0)))=1))

ORDER BY TemporaryTransactionsQuery2.Date DESC;

 

3 Replies

@Bob_AubryOPAanalytics 

 

That's a fairly massive SQL statement, which involves subqueries (not present, unfortunately) and no formatting. Asking someone -- who has no knowledge of the subject matter involved -- to scan and find a problem in that SQL is pretty big ask.

 

That said, you might have some luck using the Access to SQL Server converter on this page. 

@George: reading between the lines I think his challenge is with the HAVING clause.
But overall I agree with you: very cryptic, and lots of non-essential information.

@Tom_van_Stiphout 

 

This is the way the SQL Converter rendered the Access SQL into TSQL.

 

SELECT TemporaryTransactionsQuery2.Account
,TemporaryTransactionsQuery2.ChkRef
,TemporaryTransactionsQuery2.Debit
,TemporaryTransactionsQuery2.Credit
,TemporaryTransactionsQuery2.Balance
,TemporaryTransactionsQuery2.[Date]
,TemporaryTransactionsQuery2.Description
,TemporaryTransactionsQuery2.AccountName
,TemporaryTransactionsQuery2.Description16
,TemporaryTransactionsQuery2.PayeePayor
,TemporaryTransactionsQuery2.CombinedCategory
,TemporaryTransactionsQuery2.Multiple
,TemporaryTransactionsQuery2.ID
,Max(IIf([tblTransactions].[Account]=[TemporaryTransactionsQuery2].[AccountName]
AND [tblTransactions].[PayeePayor]=[TemporaryTransactionsQuery2].[PayeePayor]
AND [tblTransactions].[ConbinedCategory]=[TemporaryTransactionsQuery2].[CombinedCategory]
AND [tblTemporaryTransactions].[Date]-10>=[tblTransactions].[TranDate],1,0)) AS Expr2
,Max(IIf(([tblTemporaryTransactions].[PayeePayor] IS NULL)=1
AND ([tblTemporaryTransactions].[CombinedCategory] IS NULL)=1,1,0)) AS Expr3
,TemporaryTransactionsQuery2.Type
,TemporaryTransactionsQuery2.Shares
,TemporaryTransactionsQuery2.Price
,TemporaryTransactionsQuery2.CommFees
FROM (TemporaryTransactionsQuery2
INNER JOIN tblTemporaryTransactions ON TemporaryTransactionsQuery2.ID = tblTemporaryTransactions.ID)
INNER JOIN tblTransactions ON TemporaryTransactionsQuery2.AccountName = tblTransactions.Account
GROUP BY TemporaryTransactionsQuery2.Account
,TemporaryTransactionsQuery2.ChkRef
,TemporaryTransactionsQuery2.Debit
,TemporaryTransactionsQuery2.Credit
,TemporaryTransactionsQuery2.Balance
,TemporaryTransactionsQuery2.[Date]
,TemporaryTransactionsQuery2.Description
,TemporaryTransactionsQuery2.AccountName
,TemporaryTransactionsQuery2.Description16
,TemporaryTransactionsQuery2.PayeePayor
,TemporaryTransactionsQuery2.CombinedCategory
,TemporaryTransactionsQuery2.Multiple
,TemporaryTransactionsQuery2.ID
,TemporaryTransactionsQuery2.Type
,TemporaryTransactionsQuery2.Shares
,TemporaryTransactionsQuery2.Price
,TemporaryTransactionsQuery2.CommFees
HAVING (((Max(IIf(([tblTemporaryTransactions].[PayeePayor] IS NULL)=1
AND ([tblTemporaryTransactions].[CombinedCategory] IS NULL)=1,1,0)))=1))
ORDER BY TemporaryTransactionsQuery2.[Date] DESC

 

The HAVING Clause was converted by the tool. The logic escapes me, though, in both versions.