Problem
I am a beginner in Access SQL. I am in need of writing a parameter query for Access SQL and found a few tutorials and links for that but that wouldn’t satisfy my requirement.
DB: Access DB
This updates a ‘PD’ column based on the values of ‘OG’, ‘CPTY-BA’ in an INPUT table. I need to calculate PD values from other reference tables ‘PD’, ‘PD_MASTER_CPTY’ and ‘PD_MASTER_BG’.
Below is the VBA code with Access SQL with provided parameters. Is there any other way to write it in a single SELECT statement?
ElseIf (outputColumn = "PD") Then
strSQL = "SELECT [OG], CIF,[CPTY-BA], CIF_2 FROM INPUT WHERE RUN_NUMBER='" + runNumber + "' ORDER BY CIF"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF
strOrigGroup = rs![Original Group]
strCIF = rs![CIF]
strCIF2 = rs![CIF_2]
strBaselAsset = rs![CPTY-BA]
If (strOrigGroup = "CB" Or strOrigGroup = "FID" Or strOrigGroup = "PB") Then
strSQL1 = " SELECT DISTINCT [INPUT].RUN_NUMBER, [INPUT].CIF, INPUT.CIF_2,[PD].[MID_PD] FROM [PD] INNER JOIN [INPUT] " + _
"ON [PD].CIF=[INPUT].CIF_2 WHERE [PD].[CIF]='" + strCIF2 + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL1
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
If Not (rs1.EOF And rs1.BOF) Then
strPD = CDbl(rs1![MID_PD])
Else
strPD = "0.0229"
End If
strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "' WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL
CurrentDb.Execute (strSQL)
ElseIf (strBaselAsset = "BK" Or strBaselAsset = "BO" Or strBaselAsset = "SK") Then
strSQL1 = "SELECT DISTINCT [INPUT].RUN_NUMBER, [INPUT].CIF, [PD_MASTER_CPTY].[PD] FROM PD_MASTER_CPTY INNER JOIN INPUT ON [PD_MASTER_CPTY].COUNTERPARTY=INPUT.[CPTY-BA] WHERE [PD_MASTER_CPTY].[COUNTERPARTY]='" + strBaselAsset + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL1
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
If Not (rs1.EOF And rs1.BOF) Then
strPD = CDbl(rs1![PD])
Else
strPD = "0.0229"
End If
strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "' WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL
CurrentDb.Execute (strSQL)
Else
strSQL1 = "SELECT [INPUT].RUN_NUMBER, [INPUT].CIF,[PD_MASTER_BG].[PD] FROM [PD_MASTER_BG] INNER JOIN INPUT ON [PD_MASTER_BG].[BUSINESS_GROUP]=INPUT.[ORIGINAL GROUP] WHERE [PD_MASTER_BG].[BUSINESS_GROUP]='" + strOrigGroup + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL1
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
If Not (rs1.EOF And rs1.BOF) Then
strPD = CDbl(rs1![PD])
Else
strPD = "0.0229"
End If
strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "' WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL
CurrentDb.Execute (strSQL)
End If
rs.MoveNext
Loop
End If
Solution
Before we can do anything here really, we need to clean this up so we can understand exactly what’s going on.
To start with, let’s remove some duplication. This code is copy/pasted all over the place, but no matter what happens, it executes.
If Not (rs1.EOF And rs1.BOF) Then
strPD = CDbl(rs1![PD])
Else
strPD = "0.0229"
End If
strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "' WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL
CurrentDb.Execute (strSQL)
If (strOrigGroup = "CB" Or strOrigGroup = "FID" Or strOrigGroup = "PB") Then
strSQL1 = " SELECT DISTINCT [INPUT].RUN_NUMBER, [INPUT].CIF, INPUT.CIF_2,[PD].[MID_PD] FROM [PD] INNER JOIN [INPUT] " + _
"ON [PD].CIF=[INPUT].CIF_2 WHERE [PD].[CIF]='" + strCIF2 + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL1
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
ElseIf (strBaselAsset = "BK" Or strBaselAsset = "BO" Or strBaselAsset = "SK") Then
strSQL1 = "SELECT DISTINCT [INPUT].RUN_NUMBER, [INPUT].CIF, [PD_MASTER_CPTY].[PD] FROM PD_MASTER_CPTY INNER JOIN INPUT ON [PD_MASTER_CPTY].COUNTERPARTY=INPUT.[CPTY-BA] WHERE [PD_MASTER_CPTY].[COUNTERPARTY]='" + strBaselAsset + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL1
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
Else
strSQL1 = "SELECT [INPUT].RUN_NUMBER, [INPUT].CIF,[PD_MASTER_BG].[PD] FROM [PD_MASTER_BG] INNER JOIN INPUT ON [PD_MASTER_BG].[BUSINESS_GROUP]=INPUT.[ORIGINAL GROUP] WHERE [PD_MASTER_BG].[BUSINESS_GROUP]='" + strOrigGroup + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL1
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
End If
If Not (rs1.EOF And rs1.BOF) Then
strPD = CDbl(rs1![PD])
Else
strPD = "0.0229"
End If
strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "' WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL
CurrentDb.Execute (strSQL)
rs.MoveNext
Loop
Next, give your SQL statement variables some meaningful names. I shouldn’t have to keep reminding myself that strSQL1 is the select statement and strSQL is the update. Try selectSQL and updateSQL respectively.
Next, I would separate the messy business of creating the string to be executed from the business of actually executing.
Private Function BuildSelectStatement(byval cif as string, byval runNumber as integer, Optional byval baselAsset as String) As String
...
End Function
....
selectSQL = BuildSelectStatement(strCIF2, runNumber)
Which brings me to recommending that you use ADODB for this. The only time it’s really important to stick with DAO is when you intend on binding a form to the resulting Recordset. Instead of concatenating all of these parameters into strings, you could instead be passing parameter into parameterized queries. It looks and reads much better than this.