Append Data from 2 Tables (w/ matching fields) into a 3rd Table - with DAO Recordset

[Cosmonaut_99]

Hi,

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).

C99


=+=+=+=+=+=+=+=+=+=+=+=+=+=++=+=+=+=+=+=+=+=+=+=+=+
Function Recordset_856_by_Sub_Div2()

Dim DBS As DAO.Database
Dim SubDiv1 As DAO.Recordset
Dim SubDiv2 As DAO.Recordset
Dim SubDiv3 As DAO.Recordset
Set DBS = CurrentDb

'OpenRecordsets......

Set SubDiv1 = db.OpenRecordset("SELECT * FROM [JBADownload RIGHT JOIN THLFU_EFP856AYV1 ON JBADownload.[SUPPLIER REFERENCE] = THLFU_EFP856AYV1.[ITEM#]]", dbOpenDynaset)


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

Else

Subdiv3.AddNew

Subdiv3!Item = SubDiv2!Item#_Count
Subdiv3!CRP_Load = SubDiv2!GRP_DLVRY
Subdiv3!Qty = SubDiv2!B_Qty_Total
Subdiv3!Sub_Division = SubDiv1!SDiv

Subdiv3.Update

End If


SubDiv1.Close
SubDiv2.Close
Subdiv3.Close


Set SubDiv1 = Nothing
Set SubDiv2 = Nothing
Set Subdiv3 = Nothing

Set db = Nothing


MsgBox "Finished", vbInformation, "Recordset_856_by_Sub_Div"
End Function

 

[JHB]

It is missing a "[" -> "THLFU_EFP856AYV1_1.GRP_DLVRY]"
Correct is: THLFU_EFP856AYV1_1.[GRP_DLVRY]

From where comes "Quantity"? -> "Count([Item#].Quantity)" and "Sum([B_Qty].Quantity)"
[FONT=&quot]Should it not be: [/FONT]Count([Item#]) and Sum([B_Qty])?

Not good to use "#" in fieldnames. -> "Item#"

 

[Cosmonaut_99]

Thanks JHB.

I've removed the unnecessary ".Quantity"s, added an "]" for "[GRP_DLVRY]".

The "#" in Item# - comes from a system table field taken from a pass-through query, (hence not there by my choice).

Here's the revised code.
I am still getting Run-Time 424 errors "Object Required".

====================================================
Function Recordset_856_by_Sub_Div2()

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] = THLFU_EFP856AYV1.[ITEM#]]", dbOpenDynaset)


Set SubDiv2 = db.OpenRecordset("SELECT [GRP_DLVRY], Count([Item#]) AS Item_Count, Sum([B_Qty]) 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

Else


SubDiv3.AddNew
SubDiv3!Item = SubDiv2!Item_Count
SubDiv3!CRP_Load = SubDiv2!GRP_DLVRY
SubDiv3!Qty = SubDiv2!B_Qty_Total
SubDiv3!Sub_Division = SubDiv1!SDiv

SubDiv3.Update

End If

SubDiv1.Close
SubDiv2.Close
SubDiv3.Close

Set SubDiv1 = Nothing
Set SubDiv2 = Nothing
Set SubDiv3 = Nothing

Set db = Nothing


MsgBox "Finished", vbInformation, "Recordset_856_by_Sub_Div"
End Function

 

[JHB]

You've declared: "DBS As DAO.Database" and set "Set DBS = CurrentDb", but you use "db.OpenRecordset", where is db declared?

 

[Cosmonaut_99]

Thanks.

I've amended the DBS declaration, and a few other issues I've spotted.
However, I am still getting run time errors - this time '3141' errors, stating

"the Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect".
The problem with the "SELECT" SQL is in Recordset SBDiv2 - but I'm not clear on eactly what the problem is.

If you have any further pointers, that would be really helpful.

Cheers,
C99.

==========================================
Function Recordset_856_by_Sub_Div2()

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 = DBS.OpenRecordset("SELECT * FROM JBADownload RIGHT JOIN THLFU_EFP856AYV1 ON JBADownload.[SUPPLIER REFERENCE] = THLFU_EFP856AYV1.[ITEM#]", dbOpenDynaset)


Set SubDiv2 = DBS.OpenRecordset("SELECT Count([Item#]) AS Item_Count, Sum([B_Qty]) 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 = DBS.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


Else


SubDiv3.AddNew
SubDiv3!Item = SubDiv2!Item_Count
SubDiv3!CRP_Load = SubDiv2!GRP_DLVRY
SubDiv3!Qty = SubDiv2!B_Qty_Total
SubDiv3!Sub_Division = SubDiv1!SDiv

SubDiv3.Update


End If


SubDiv1.Close
SubDiv2.Close
SubDiv3.Close


Set SubDiv1 = Nothing
Set SubDiv2 = Nothing
Set SubDiv3 = Nothing

Set db = Nothing

MsgBox "Finished", vbInformation, "Recordset_856_by_Sub_Div"
End Function

 

[JHB]

You have a "_1" to much here:
"THLFU_EFP856AYV1_1.[GRP_DLVRY] "

If it not helps, the put the line into to a QBE grid:

SELECT Count([Item#]) AS Item_Count, Sum([B_Qty]) As B_Qty_Total, FROM THLFU_EFP856AYV1 LEFT JOIN JBADownload ON THLFU_EFP856AYV1.[ITEM#]= JBADownload.[SUPPLIER REFERENCE]GROUP BY THLFU_EFP856AYV1_1.[GRP_DLVRY]