Question Insert data from one table into a different field in another table (1 Viewer)

Agnister

Registered User.
Local time
Today, 22:16
Joined
Jul 2, 2011
Messages
21
I have a table tbl_TmpTrans_ByDate with the following data
NQCG_Month Category Total_Amout
Jan-19 Credit $220

And I have a master table tbl_SumMonth with fields
NQCG_Month Credit Debit ...........etc

I want to insert into the credit field in tbl_SumMonth the Total_Amount ($220) from tbl_TmpTrans_ByDate. Can anyone please see what is wrong with my code
They are both Currency fields

Private Sub cmd_SumQry_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim strQry As String
Dim strNewTable As String
Dim strSQL As String

strNewTable = "tbl_TmpTrans_ByDate"
strQry = "qry_Income_ByDate"
Set db = CurrentDb

db.Execute "INSERT INTO tbl_SumMonth ([Credit]) SELECT tbl_TmpTrans_ByDate.[Total_Amount]" & _
" FROM tbl_TmpTrans_ByDate;"

Set db = Nothing
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:16
Joined
Feb 28, 2001
Messages
26,996
I'm with Colin. We need to know what you expected and what you got. You don't have to post a LOT of data because you only have one active field in the SQL string.
 

Agnister

Registered User.
Local time
Today, 22:16
Joined
Jul 2, 2011
Messages
21
Gentlemen
I added a WHERE statement and everything worked. Here is my edited code.


db.Execute "INSERT INTO tbl_SumMonth ([Credit]) SELECT tbl_TmpTrans_ByDate.[Total_Amount]" & _
" FROM tbl_TmpTrans_ByDate" & _
" WHERE tbl_TmpTrans_ByDate.[NQCG_Month] = 'Jan-19';"

MsgBox "Table tbl_SumMonth has now been updated"


What would you suggest I do if I also want 'Jan-19' in the same record as the $220.
Ultimately I want the table tbl_SumMonth to look like this


NQCG_Month Credit

Jan-19 $220
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:16
Joined
Sep 21, 2011
Messages
14,038
Try
Code:
db.Execute "INSERT INTO tbl_SumMonth ([NQCG_Month],[Credit]) SELECT 'Jan-19', tbl_TmpTrans_ByDate.[Total_Amount]" & _
" FROM tbl_TmpTrans_ByDate" & _
" WHERE tbl_TmpTrans_ByDate.[NQCG_Month] = 'Jan-19';"

however something like this might be more useful?

Code:
strSQL = "INSERT INTO tbl_SumMonth ([NQCG_Month],[Credit]) SELECT '" & strNQCG_Month & "', tbl_TmpTrans_ByDate.[Total_Amount]" & _
" FROM tbl_TmpTrans_ByDate" & _
" WHERE tbl_TmpTrans_ByDate.[NQCG_Month] = '" & strNQCG_Month & "';"
Debug.Print strSQL
 
Last edited:

Users who are viewing this thread

Top Bottom