Sum Total data and Save to a table with VBA

[Agnister]

This is my test data (qry_Income_ByDate)
NQCG_Month Category Amount
Jan-19 Credit $70
Jan-19 Credit $20
Jan-19 Debit $245
Jan-19 Credit $60

I want to perform the following:
1. DSum the [Amount] according to the Category [Credit]

2. Save the results INTO a new table (tbl_TmpTrans_ByDate)
This is my code which will not work. Can anyone help please.
Set db = CurrentDb()

DoCmd.RunSQL "SELECT qry_Income_ByDate.[Category],DSum(qry_Income_ByDate.[Amount], "qry_Income_ByDate", "[Category] = 'Credit'")," & _
" qry_Income_ByDate.[NQCG_Month]" & _
" INTO tbl_TmpTrans_ByDate FROM qry_Income_ByDate" & _
" GROUP BY qry_Income_ByDate.[Category], qry_Income_ByDate.[NQCG_Month];

Set db = Nothing

 

[ypma]

Angister, I am a user not a professional and my solution would be to create a query to group and sum the credit category.Then change the query into a make table type query . Hope example helps


Regards Ypma

 

[Agnister]

ypma
I made an attempt at your suggestion but crashes on the Set rs = db.OpenRecordset(strSQL). The make Table works but the first section crashes. What do you think is wrong with my code.


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"

DoCmd.OpenQuery strQry, acViewNormal

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

strSQL = "SELECT qry_Income_ByDate.[Category],Sum(qry_Income_ByDate.Amount)" & _
" qry_Income_ByDate.[NQCG_Month]" & _
" FROM qry_Income_ByDate" & _
" WHERE qry_Income_ByDate.[Category] = 'Credit'" & _
" GROUP BY qry_Income_ByDate.[Category], qry_Income_ByDate.[NQCG_Month];"

DoCmd.RunSQL "SELECT qry_Income_ByDate.[NQCG_Month],qry_Income_ByDate.[Category],qry_Income_ByDate.[Amount]" & _
" INTO tbl_TmpTrans_ByDate FROM qry_Income_ByDate;"

DoCmd.OpenTable strNewTable, acViewNormal, acEdit
Set db = Nothing

End Sub

 

[Gasman]

You have to set strSQL before you can use it.?

 

[ypma]

Agnister . If you open my demo the result in the new table would be :

~DateLastOfNQCG_Month Category TOT
09/01/2019 Credit £150.00~

If you require a command button ,open the create table query .

If the desired result of my example is not what you require ,what is the desired outcome based on your data provide .

Ypma

 

[The_Doc_Man]

Gasman tells you WHY you had the error, but I'll be more explicit.

Set rs = db.OpenRecordset(strSQL)

strSQL = "SELECT qry_Income_ByDate.[Category],Sum(qry_Income_ByDate.Amount)" & _
" qry_Income_ByDate.[NQCG_Month]" & _
" FROM qry_Income_ByDate" & _
" WHERE qry_Income_ByDate.[Category] = 'Credit'" & _
" GROUP BY qry_Income_ByDate.[Category], qry_Income_ByDate.[NQCG_Month];"

Given these two statements, you are opening the recordset with an empty string and THEN defining the string. That recordset operation should give you an error since at the time you issue it, you have no valid definition for the proposed recordset.

OpenRecordset(string) COPIES the string definition into the internal recordset structure during the process of opening. And in your case it copied an empty string.

As a side note, creating a particular table via the SELECT... INTO .... FROM syntax doesn't need an ORDER BY. Tables are stored with no particular order anyway. The trick with Access is that when you READ the table, you impose the order you want. There is no particular guarantee that your created table will retain the order in which you created it. Even ONE LITTLE EDIT after table creation and you run the risk of totally re-ordering the table anyway.

 

[Agnister]

ypma
My apologies. I did not see the attachment NewTest.accdb. This does what I want.
Thank you

 

[ypma]

Agnister. You are welcome , don't forget that each time you click your command button the existing table will be over written.


Regards Ypma

 

[Agnister]

ypma
It works exactly the way I wanted to. Thanks again