Convert Access query to Sql Server
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
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;
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. BobSQLServer Design for dbo.TemporaryTransactionsQuery3 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.TranDateFROM dbo.tblTemporaryTransactions INNER JOIN dbo.TemporaryTransactionsQuery2 ON dbo.tblTemporaryTransactions.ID = dbo.TemporaryTransactionsQuery2.ID INNER JOIN dbo.tblTransactions ON dbo.TemporaryTransactionsQuery2.AccountName = dbo.tblTransactions.AccountGROUP 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.MultipleORDER BY dbo.TemporaryTransactionsQuery2.Date DESCMS Access Sql codeSELECT 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.CommFeesFROM (TemporaryTransactionsQuery2 INNER JOIN tblTemporaryTransactions ON TemporaryTransactionsQuery2.ID = tblTemporaryTransactions.ID) INNER JOIN tblTransactions ON TemporaryTransactionsQuery2.AccountName = tblTransactions.AccountGROUP 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.CommFeesHAVING (((Max(IIf(IsNull([tblTemporaryTransactions].[PayeePayor])=True And IsNull([tblTemporaryTransactions].[CombinedCategory])=True,1,0)))=1))ORDER BY TemporaryTransactionsQuery2.Date DESC; Read More
