SQL for INSERT from SELECT with additional values

Question

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

Please see my edits above. This is VBA for Access but I think the SQL is generic, no? — John Lee
– John Lee, Commented Dec 28, 2015 at 22:16
SQL in MS Access is anything BUT ANSI-standard compliant .... — marc_s
– marc_s, Commented Dec 28, 2015 at 22:17
@stidgeon i just cleaned up the sql for brevity sake. i realize the other date thing makes it confusing. I understand how to get the fields from the form, but how would i write those values into the query? — John Lee
– John Lee, Commented Dec 28, 2015 at 22:24

Community · Accepted Answer · 2017-05-23 11:59:05Z

1

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

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Dec 28, 2015 at 23:16

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL for INSERT from SELECT with additional values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related