I hardly ever post for help and try to figure it out on my own, but now I’m stuck. I’m just trying to append data from multiple tables to one table. The source tables are data sets for each American State and the append query is the same for each State, except for a nested select script to pull from each State table. So I want to create a VBA script that references a smaller script for each state, rather than an entire append script for each state. I’m not sure if I should do a SELECT CASE, or FOR TO NEXT or FOR EACH NEXT or DO LOOP or something else.
Here’s what I have so far:
tblLicenses is a table that has the field LicenseState from which I could pull a list of the states.
______________________
Function StateScripts()
Dim rst As DAO.Recordset
Dim qryState As String
Dim StateCode As String
Set rst = CurrentDb.OpenRecordset("SELECT LicenseState FROM tblLicenses GROUP BY LicenseState;")
' qryState = DLookup("LicenseState", "tblLicenses")
' qryState = "SELECT LicenseState INTO Temp FROM tblLicenses GROUP BY LicenseState;"
' DoCmd.RunSQL qryState
Select Case qryState
Case "CT"
StateCode = "CT"
StateScripts = " SELECT [LICENSE NO] AS StateLicense, [EXPIRATION DATE] AS dateexpired FROM CT "
Case "AK"
StateCode = "AK"
StateScripts = " SELECT [LICENSE] AS StateLicense, [EXPIRATION] AS dateexpired FROM AK "
Case "KS"
StateCode = "KS"
StateScripts = " SELECT [LicenseNum] AS StateLicense, [ExpDate] AS dateexpired FROM KS "
End Select
CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( " & StateScripts & " ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like '" & StateCode & "') "
End Function
_____________________________
When combining StateCode and StateScripts into the Append query, it will look like this:
CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LICENSE NO] AS StateLicense, [EXPIRATION DATE] AS dateexpired FROM CT ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'CT') "
CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LICENSE] AS StateLicense, [EXPIRATION] AS dateexpired FROM AK ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'AK') "
CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LicenseNum] AS StateLicense, [ExpDate] AS dateexpired FROM KS ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'KS') "
Here’s what I have so far:
tblLicenses is a table that has the field LicenseState from which I could pull a list of the states.
______________________
Function StateScripts()
Dim rst As DAO.Recordset
Dim qryState As String
Dim StateCode As String
Set rst = CurrentDb.OpenRecordset("SELECT LicenseState FROM tblLicenses GROUP BY LicenseState;")
' qryState = DLookup("LicenseState", "tblLicenses")
' qryState = "SELECT LicenseState INTO Temp FROM tblLicenses GROUP BY LicenseState;"
' DoCmd.RunSQL qryState
Select Case qryState
Case "CT"
StateCode = "CT"
StateScripts = " SELECT [LICENSE NO] AS StateLicense, [EXPIRATION DATE] AS dateexpired FROM CT "
Case "AK"
StateCode = "AK"
StateScripts = " SELECT [LICENSE] AS StateLicense, [EXPIRATION] AS dateexpired FROM AK "
Case "KS"
StateCode = "KS"
StateScripts = " SELECT [LicenseNum] AS StateLicense, [ExpDate] AS dateexpired FROM KS "
End Select
CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( " & StateScripts & " ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like '" & StateCode & "') "
End Function
_____________________________
When combining StateCode and StateScripts into the Append query, it will look like this:
CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LICENSE NO] AS StateLicense, [EXPIRATION DATE] AS dateexpired FROM CT ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'CT') "
CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LICENSE] AS StateLicense, [EXPIRATION] AS dateexpired FROM AK ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'AK') "
CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LicenseNum] AS StateLicense, [ExpDate] AS dateexpired FROM KS ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'KS') "
Last edited: