Solved Insert Into - Select

[KitaYama]

This is the relationship between several tables:

tblProducts : List of products.
tblProcesses : Holds 30 records for all possible processes we can handle for manufacturing a product.
tblProducts_Processes : A junction table between these two tables. (Each product needs which process)
tblOrders_Processes : a table to keep track of the date and the person in charge who has worked on the necessary processes for each order.

I can use the following sql to insert all necessary processes from tblProducts_Processses into tblOrders_Processes

INSERT INTO tblOrders_Processes ( OrderFK, ProcessFK )
SELECT 12, ProcessFK
FROM tblProducts_Processes
WHERE ProductFK=2;

Two questions:

1. Is there any way to simplify this query? As OrderPK=12 has only one ProductFK, is there any way to change the sql somehow that works only with OrderPK?
2. This sql inserts only one OrderFK at a time. (above example -->12). Is there any way to make it work for multiple OrderPKs? For example OrderPK from 12 to 15.

Thanks for any kind of advice.

 

[arnelgp]

use a public sub/function and pass ProductFK and the range of OrderFk to the function:

Public Sub subInsertOrderFK(ByVal ProductFK As Long, ParamArray orderPK() As Variant)
 
    Const INS_SQL As String = _
        "INSERT INTO tblOrders_Processes ( OrderFK, ProcessFK ) " & _
        "SELECT @1, tblProducts_Processes.ProcessFK " & _
        "FROM tblProducts_Processes " & _
        "WHERE ProductFK = @2;"
 
    Dim i As Integer
    Dim db As DAO.Database
 
    Set db = CurrentDb
 
    With db.CreateQueryDef("", INS_SQL)
        For i = 0 To UBound(orderPK)
            .Parameters(0) = orderPK(i)
            .Parameters(1) = ProductFK
            .Execute
        Next
    End With

    Set db = Nothing

End Sub

to use:

Call subInsertOrderFK(2, 12, 13, 14, 15)

 

[KitaYama]

@arnelgp
Thanks for your help, but at present I'm using a method less or more like your suggestion.
I was looking for a way to bypass using a loop to see if I can speed up the job.

Thanks again.

 

[arnelgp]

create another table (on my code is is called Numbers) with 1 field OrderPK (Long Integer, indexed, nodups)
add records 1 to 20 or 1 up to 15.

then use this new code, the syntax on calling is the same:

Public Sub subInsertOrderFK(ByVal ProductFK As Long, ParamArray orderPK() As Variant)
 
    Const INS_SQL As String = _
        "INSERT INTO tblOrders_Processes ( OrderFK, ProcessFK ) " & _
        "SELECT B.OrderPK, tblProducts_Processes.ProcessFK " & _
        "FROM tblProducts_Processes, (SELECT  OrderPK From Numbers Where OrderPK In (@1)) AS B " & _
        "WHERE tblProducts_Processes.ProductFK = @2;"
 
    Dim i As Integer
    Dim sOrderPK As String
    Dim sql As String
    Dim db As DAO.Database
 
    For i = 0 To UBound(orderPK)
        sOrderPK = sOrderPK & "," & orderPK(i)
    Next
    sOrderPK = Mid$(sOrderPK, 2)
 
    sql = Replace$(Replace$(INS_SQL, "@1", sOrderPK), "@2", ProductFK)
    'Debug.Print sql
    'Exit Sub
    Set db = CurrentDb
 
    db.Execute sql, dbFailOnError
    Set db = Nothing

End Sub

 

[ebs17]

SELECT 
   O.OrderPK, 
   PP.ProcessFK 
FROM 
   (tblOrder AS O 
      INNER JOIN tblProducts AS P 
      ON O.OrderProductFK = P.ProductPK 
   ) 
   INNER JOIN tblProduct_Processes AS PP 
   ON P.ProductPK = PP.ProductFK

You can certainly add the INSERT INTO line yourself.
This is what your current data model provides. Now you just need to add suitable, targeted filters.

 

[KitaYama]

SELECT
   O.OrderPK,
   PP.ProcessFK
FROM
   (tblOrder AS O
      INNER JOIN tblProducts AS P
      ON O.OrderProductFK = P.ProductPK
   )
   INNER JOIN tblProduct_Processes AS PP
   ON P.ProductPK = PP.ProductFK

You can certainly add the INSERT INTO line yourself.
This is what your current data model provides. Now you just need to add suitable, targeted filters.

Thanks. will give it a try.

 

[ebs17]

Note: I didn't notice the example database, so I wrote the SQL statement by hand. Therefore, two table names are not entirely correct.

 

[KitaYama]

Note: I didn't notice the example database, so I wrote the SQL statement by hand. Therefore, two table names are not entirely correct.

I added the database later in case anyone wants to see.
I don't have Access at home. I'll check your previous sql first thing in the morning.

Thanks again.

 

[KitaYama]

Couldn't wait till morning. The thrill was killing me.
Used AnyDesk to remote control into my PC and tested.
With a little bit modification, the following seems to do what I expect.
And how much faster it is comparing to a loop.

INSERT INTO tblOrders_Processes ( OrderFK, ProcessFK )
SELECT
   O.OrderPK,
   PP.ProcessFK
FROM
   (tblOrders AS O
      INNER JOIN tblProducts AS P
      ON O.OrderProductFK = P.ProductPK
   )
   INNER JOIN tblProducts_Processes AS PP
   ON P.ProductPK = PP.ProductFK
WHERE 
   (OrderPK IN (SELECT OrderPK FROM tblOrders WHERE OrderPK Between 5 AND 10));;

I just tested it on a local db, but I expect it has the same lightining speed when imported to our main db with sql server BE and more than a million records in each table.
@ebs17 Just tell me how to thank you. 'cause I really don't know how to.

 

[ebs17]

That's enough to do the same thing.

...
WHERE 
   O.OrderPK Between 5 AND 10

with sql server BE and more than a million records in each table.

Without the brackets in the JOINs, you should have valid T-SQL syntax here. With an ADODB connection to the BE, you could run the query directly in the SQL Server.

 

[cheekybuddha]

With an ADODB connection to the BE, you could run the query directly in the SQL Server.

Also with a DAO pass-through, no?

 

[ebs17]

The connection technology is secondary. So yes.

 

[cheekybuddha]

The connection technology is secondary

I agree.

So it made me wonder why you put it front and centre.

 

[ebs17]

If you ask me: I once perceived that DAO is optimized for Jet/ACE, while ADODB is better for other external databases.

 

[Pat Hartman]

Order processes should be added ONLY once when you add a new order. You should never have a need to add processes for multiple orders at one time unless you are doing some kind of conversion and this would be a one time effort.

 

[KitaYama]

Order processes should be added ONLY once when you add a new order. You should never have a need to add processes for multiple orders at one time unless you are doing some kind of conversion and this would be a one time effort.

It depends on how you think of an order and how the order get saved in the table.

If you think of a bound order form and the user enters one order at a time, yes, you're correct.
But not when the form is unbound and you bulk insert orders.

To be more precise, most of our products are assemblies, each assembly made of several hundred parts. (Each part is a product and can be ordered as a single order)
When we receive an order for such an assembly, it would be a pain for the user to sit and enter several hundred records, one at a time in a bound form.
Our form is unbound and a button, executes a query to bulk insert necessary parts into orders table.
Then we use a parameter query like what arnelgp suggested to loop through each individual order and insert the processes.
The purpose of this question was to find a way to bulk insert the processes too.

And you may think an order is only one order even if it's an assembly. So one record should be enough.
In our case no, it's not enough. Even if the order is only one order, we have to manufacture all parts, and be able to track each process of each part for traceability, in case of any problem in future.

 

[arnelgp]

so everytime there is diferemt orderPK, you also need to modify your query?
what if you need broken orderPK, ie, 5, 6, 11, 15?
i think a func/sub is more suitable for this you just need to plug-in your own sql.

 

[KitaYama]

so everytime there is diferemt orderPK, you also need to modify your query?
what if you need broken orderPK, ie, 5, 6, 11, 15?
i think a func/sub is more suitable for this you just need to plug-in your own sql.

I don't use a querydef for this purpose.
I wrote a function to do this.

Public Function AddProcesses_Select(ByVal Filtr As String)
 
    Dim sql As String
   
    sql = "INSERT INTO tblOrdersProcesses ( OrderFK, ProcessFK ) "
    sql = sql & "SELECT O.OrderPK, PP.ProcessFK "
    sql = sql & "FROM (tblOrders AS O INNER JOIN tblProducts AS P ON O.OrderProductFK = P.ProductPK) "
    sql = sql & "INNER JOIN tblProductsProcesses AS PP ON P.ProductPK = PP.ProductFK "
    sql = sql & "WHERE " & Filtr

    CurrentDb.Execute sql, dbSeeChanges
   
End Function

Then use it as following. I can pass different filters based on the form I'm intending to run it.

AddProcesses_Select "OrderPK IN(1,2,3,4)"
Or
AddProcesses_Select "OrderSerialNo='" Me.OrderSerialNo & "'"

 

[arnelgp]

I don't use a querydef for this purpose.

i don't use querydef on my code.
you can also create a Constant for your SQL string:

Public Function AddProcesses_Select(ByVal Filtr As String)
 
    Const  insert_sql As String = _
    "INSERT INTO tblOrdersProcesses ( OrderFK, ProcessFK ) "
    "SELECT O.OrderPK, PP.ProcessFK "
    "FROM (tblOrders AS O INNER JOIN tblProducts AS P ON O.OrderProductFK = P.ProductPK) "
    "INNER JOIN tblProductsProcesses AS PP ON P.ProductPK = PP.ProductFK "
    "WHERE <Filter>"

    Dim sql As String
   sql = Replace$(insert_sql, "<Filter>", Filtr)

    CurrentDb.Execute sql, dbSeeChanges
 
End Function