Using Insert into with 'Select' to Supply Some Values But Not Others (Access 2010)

Using INSERT INTO with 'SELECT' to supply some values but not others (Access 2010)

Try doing it in the most standard way in SQL, by using the SELECT as the whole source in the INSERT instead of just a single column sub-query:

INSERT INTO master_eod ( sha1_hex, role )
SELECT 'ef03ff03', role_num FROM role_index WHERE role_name='Follow Up' ;

Note that you must ensure that such a query (the SELECT alone) returns just a single row, otherwise you would end up inserting many rows at once in a single go, one for each role_num found (which may or may not be desirable).

MS Access inserting records using SQL INSERT INTO - SELECT FROM

MS Access requires a non-empty FROM clause, so you can use:

INSERT INTO Documents([DocNo], [DocName], [DeptID], [DocTypeID])
    SELECT @dno, @dname, d.id, dt.id,
    FROM (SELECT ID FROM DocTypes WHERE DocType = 'Some_Doc_Type') as dt,
         (SELECT ID FROM Departments WHERE Dept = 'Some_Dept_Name') as d

insert into query without listing destination fields explicitly

I may be misunderstanding you, but if the tables are in the same access database, it seems you could do the following steps and let the IDE do all of the heavy lifting for you.

1. Right click your massive table and select copy.
2. Right click in the object explorer area and select paste.
3. Optional - rename the copied table.
4. Run a delete query on the copied table, removing all records that you do not want. The delete query would look like the following:

Query Text

DELETE *
FROM MyCopiedTable
WHERE State <> 'ms';

Insert query results into table in ms access 2010

Maybe you have a typo but joining a table to itself you don't need the join, this should work fine. With an insert on a select statement you don't use the values keyword.

INSERT INTO CompaniesTable (CompanyName,Description,Website,Email,TypeNumber) 
    SELECT DISTINCT IIF(FIRM_NAME IS NULL, SUBACCOUNT_COMPANY_NAME, FIRM_NAME), 
                    '','','',0 
    FROM qrySummaryData

How to order the resulting query information being inserting into a table while leaving existing table records on top?

Create a temp table, add the first result set in the desired order. Insert your new values into the table, query the table to return your new results with an order by into your temp table, select your temp table the results will be in the order you added them unless you do another order by.

Don't forget to drop your temp table after displaying the results.

Insert multiple rows using one insert statement in Access 2010

For SQL-Server: Yes, and it can exactly like you write. Just be certain that the column values are in the same order as they appear in the table. Also: you must supply a value for each existing column.

For Access 2010: No. At least not by hardcoded values in the sql, but only by selecting multiple records from a table (in the same or in another database). See also the link in the answer of Khepri.

?? Combine, join, insert in query Access 2007-2010?

Perhaps something like this as MS access doesn't support full outer joins.

What this does is generate two data sets one for wasp_Color1 and those records that match in wasp_Color2 and vice-versa. and then combine's those results into one data set eliminating the duplicates.

For a better understanding of joins: see https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Select WC1.Name, WC1.Color, WC2.Size, WC1.Code
FROM wasp_Color1 wc1
LEFT JOIN wasp_color2 wc2
 on wc1.Name = WC2.Name
and wc1.code = wc2.code

UNION

Select WC2.Name, WC1.Color, WC2.Size, WC2.Code
FROM wasp_Color1 wc1
RIGHT JOIN wasp_color2 wc2
 on wc1.Name = WC2.Name
and wc1.code = wc2.code

MS Access VBA SQL query runs, but does not Insert

After resolving the integrity violation issue from your table definition, please consider parameterized queries using MS Access QueryDefs for a more readable and maintainable workflow.

It helps separate SQL from VBA to avoid messy, hard to read, error-prone concatenation and quote enclosures. Additionally, use pure SQL as your DLookUp is unnecessary with the If block and Now() is available in queries.

SQL Update Query (save as query object, adjust data types as needed)

PARAMETERS txtTranAmountParam Double, txtTranOperationParam Text, txtTranItem365Param Long;
UPDATE [Items] 
SET ItmStock = IIF([txtTranOperationParam] = 'Issue',
                   ItmStock - [txtTranAmountParam],
                   ItmStock + [txtTranAmountParam])
WHERE Itm365 = txtTranItem365Param;

SQL Append Query (save as query object, adjust data types as needed)

PARAMETERS txtTranAmountParam Double, txtTranOperationParam Text, txtTranItem365Param Long;
INSERT INTO Transaction ([TranDate], TranItem365, TranAmount, TranOperation) 
VALUES (Now(), [txtTranItem365Param], [txtTranAmountParam], [txtTranOperationParam]);

SQL Append Query (save as query object, adjust data types as needed)

PARAMETERS SQLParam Long, txtIssuedToDeptParam Long, txtIssuedToParam Long;
INSERT INTO Issueance VALUES ([SQLParam], [txtIssuedToDeptParam], [txtIssuedToParam])

VBA (referencing above query objects)

Private Sub btnApplyTransaction_Click()

   Dim db As Database
   Dim upd_qdef As QueryDef, apn_qdef As QueryDef, iss_qdef As QueryDef
   Dim sql As String, oper As String

   Set db = CurrentDb()

   If txtTranItem365.ListIndex = -1 Then
       MsgBox "Please select an item.", vbCritical
       Exit Sub 
   End If
   If txtTranAmount.Value = "" Then
       MsgBox "Please enter an amount.", vbCritical
       Exit Sub 
   End If
   If txtTranOperation.Value = "Issue" And txtIssuedToDept.ListIndex = -1 Then
       MsgBox "Please select a department to issue to.", vbCritical
       Exit Sub 
   End If

   ' ASSIGN QUERYDEFS, BIND PARAMS, AND EXECUTE ACTION
   ' UPDATE
   Set upd_qdef = db.QueryDefs("mySavedUpdateQuery")
   upd_qdef!txtTranAmountParam = txtTranAmount
   upd_qdef!txtTranOperationParam = txtTranOperation.Value
   upd_qdef!txtTranItem365Param = txtTranItem365.Value

   upd_qdef.Execute dbFailOnError

   ' APPEND
   Set apn_qdef = db.QueryDefs("mySavedAppendQuery")
   apn_qdef!txtTranAmountParam = txtTranAmount
   apn_qdef!txtTranOperationParam = txtTranOperation.Value
   apn_qdef!txtTranItem365Param = txtTranItem365.Value

   apn_qdef.Execute dbFailOnError

   If txtTranOperation.Value = "Issue" Then
        Set iss_qdef = db.QueryDefs("mySavedIssueanceAppendQuery")

        iss_qdef!SQLParam = 32
        iss_qdef!txtIssuedToDeptParam = txtIssuedToDept.Value
        iss_qdef!txtIssuedToDeptParam = txtIssuedTo.Value

        iss_qdef.Execute dbFailOnError
   End If

   txtTranAmount.Value = "": txtTranItem365 = "": txtTranOperation = "Add"
   txtIssuedTo = "": txtIssuedToDept = ""

   DoCmd.RefreshRecord

   Set upd_qdef = Nothing: apn_qdef = Nothing: iss_qdef = Nothing
   Set db = Nothing    
End If

---

Related Topics