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 DB : Access DB.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 SELECTSELECT 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
Code Description:
Update 'PD' column based on the values of 'OG','CPTY-BA' in a INPUT table. Need to calculate PD values from other reference tables 'PD','PD_MASTER_CPTY' AND 'PD_MASTER_BG'.
