CODEWITHSUNDEEP
sqlms-accessselectinsert
John Lee
John Lee

Reputation: 1387

SQL for INSERT from SELECT with additional values

I'm not sure if this is possible but I'm trying the run the following SQL from a VBA application in ACCESS DB. I'd like to INSERT a record into 'outputTable' with most of the data coming from a record in 'stagingTable'. However, two fields need to come from a form and I cannot figure out how to include those values into the INSERT statement.

sql = "INSERT INTO " & outputTable ([Date],[Carrier],[Division],[Code],[Status],[Total]) 
SELECT [Division],[Code],[Status],Sum([Claim]) AS [SumOfClaim] 
FROM " & stagingTable & "
GROUP BY [Division],[Code],[Status];"

On the outputTable, the [Date] and [Carrier] values are missing. These would come from user input on a form. Is there a way I could add these values into to SQL statement?

Thank you

Upvotes: 0

Views: 327

Answers (1)

Andre
Andre

Reputation: 27634

The best solution is to create a parameterized query as shown here: https://stackoverflow.com/a/2317225/3820271

It would e.g. look like this:

Dim DB As Database
Dim QD As QueryDef
Dim S As String

Set DB = CurrentDb
S = "PARAMETERS parDate DateTime, parCarrier Text(255); " & _
    "INSERT INTO " & outputTable & "([Date], [Carrier], [Division], [Code], [Status], [Total]) " & _
    " SELECT [parDate], [parCarrier], [Division],[Code],[Status],Sum([Claim]) AS [SumOfClaim] " & _
    " FROM " & stagingTable & _
    " GROUP BY [Division],[Code],[Status];"

' Create a temporary query object
Set QD = DB.CreateQueryDef("", S)
' Set parameter values from your input form
QD.Parameters!parDate = Forms!myForm!myDateField
QD.Parameters!parCarrier = Forms!myForm!Carrier
' Run the query
QD.Execute

Set QD = Nothing

Upvotes: 1

Related Questions