I'm trying to replace an Append query - which is pretty slow to run.
The query appends data from fields 2 tables (based on a JOIN between the 2 tables) into a 3rd table.
To achieve this, the query needs to review all records on Table 2, and find matches in Table 1.
(After reading around the net), I've tried to achieve this by creating 3 Recordsets (1 for each table), then .Add ing data
from fields in the first 2 recordsets into the 3rd.
Table 1 : "JBADownload"
Fields : "Supplier Reference" (Primary, Text), "SDIV" (Text)
Table 2 : "THLFU_EFP856AYV1"
Fields : "Item#" (Non-Unique, Text), "B_Qty" (Numerical), "GRP_DLVRY" (Text).
Table 3 : "CRP_Loads" (Table that Data needs to be appended into)
Fields : "Item" (Numerical), CRP_Load (Text), Qty (Numerical) and Sub_Division (Text)
Field JBADownload!"Supplier Reference" is RIGHT JOINed to THLFU_EFP856AYV1!Item#
I hope to limit the records in the recordsets to include only those records from THLFU_EFP856AYV1 where (based on the JOIN),
the SDIV field in table JBADownload are equal to the If / Elseif statements I have used.
Additionally, I need Recordset "SubDiv2" (for Table "THLFU_EFP856AYV1") to group the contents by field "GRP_DLVRY",
performing a Sum on field B_Qty (called B_Qty_Total) and a Count on Field ITEM# (called ITEM#_Count).
However, when I try to run this code - I get a Run Time Error (Type 424), saying that an Object is missing on Recordset
"SubDiv2".. however I can't figure it out.
Any ideas gratefuly received.
(Apologies for the lack of line breaks in the OpenRecordset lines - I have tried to add these in without success).
Dim DBS As DAO.Database
Dim SubDiv1 As DAO.Recordset
Dim SubDiv2 As DAO.Recordset
Dim SubDiv3 As DAO.Recordset
Set DBS = CurrentDb
Set SubDiv1 = db.OpenRecordset("SELECT * FROM [JBADownload RIGHT JOIN THLFU_EFP856AYV1 ON JBADownload.[SUPPLIER REFERENCE] =
Set SubDiv2 = db.OpenRecordset("SELECT [GRP_DLVRY], Count([Item#].Quantity) AS Item_Count, Sum([B_Qty].Quantity) As
B_Qty_Total FROM [THLFU_EFP856AYV1] LEFT JOIN JBADownload ON THLFU_EFP856AYV1.[ITEM#]= JBADownload.[SUPPLIER REFERENCE]GROUP
BY THLFU_EFP856AYV1_1.GRP_DLVRY]", dbOpenDynaset)
Set SubDiv3 = db.OpenRecordset("CRP_Loads", dbOpenDynaset)
If JBADownload!SDiv = "EC" Then
ElseIf JBADownload!SDiv = "EF" Then
ElseIf JBADownload!SDiv = "EQ" Then
ElseIf JBADownload!SDiv = "1C" Then
ElseIf JBADownload!SDiv = "1E" Then
Subdiv3!Item = SubDiv2!Item#_Count
Subdiv3!CRP_Load = SubDiv2!GRP_DLVRY
Subdiv3!Qty = SubDiv2!B_Qty_Total
Subdiv3!Sub_Division = SubDiv1!SDiv
Set SubDiv1 = Nothing
Set SubDiv2 = Nothing
Set Subdiv3 = Nothing
Set db = Nothing
MsgBox "Finished", vbInformation, "Recordset_856_by_Sub_Div"