show count

G

Guest

hi,

I have the following query:

strSQL2 = "SELECT [1calltotals2RJUNE130].ResolvedStatus, " _
& "[1calltotals2RJUNE130].ResolvedDt,
Sum([1calltotals2RJUNE130].[SumOfSumOfnumber of calls]) " _
& "AS [SumOfSumOfSumOfnumber of calls],
Sum([1calltotals2RJUNE130].[SumOfCountOfLoan Acct #]) " _
& "AS [SumOfSumOfCountOfLoan Acct #],
Sum([1calltotals2RJUNE130].average_no_calls_to_be_resolved) " _
& "AS SumOfaverage_no_calls_to_be_resolved, " _
& "[SumOfSumOfSumOfnumber of calls]/[SumOfSumOfCountOfLoan Acct #] AS Expr1
" _
& "INTO 1calltotals2Rjune130_finaltemp " _
& "FROM 1calltotals2RJUNE130 " _
& "WHERE ((([1calltotals2RJUNE130].ResolvedStatus) =
[Forms]![frm_criteria]![tester1])) " _
& "GROUP BY [1calltotals2RJUNE130].ResolvedStatus,
[1calltotals2RJUNE130].ResolvedDt; "
'make a table showing average number of calls for resolvedstatus selected at
runtime
DoCmd.RunSQL strSQL2

I would like to show a messagebox that lists how many records the preceding
pasted...perhaps a count of how many records there are in the new table. I
do not want to use the default "is about to paste N records into a new
table... do you want to continue?" because I turned the warnings off.

How can I achieve this?

Thanks in advance,
geebee
 
M

Michel Walsh

Hi,


A SELECT ... INTO creates a table, you can thus count the number of records
in the table: SELECT COUNT(*) FROM 1calltotals2Rjune130_finaltemp .



Hoping it may help,
Vanderghast, Access MVP
 
J

John Spencer

If you change your procedure to use the Execute method, you can use the
recordsAffected property to get a count.

Dim dbAny as DAO.Database

strSQL2 = "...

dbAny.Execute strSQL2, dbFailOnError
MsgBox dbAny.RecordsAffected & " records created"

RecordsAffected works with any action query.
 
G

Guest

hi,

I am getting no messagebox. here is what I have now:

Private Sub Label149_Click()

Dim dbAny As Database
Dim strSQL28 As String

'this is the first half of the graph source. intended to build graphs
dynamically
strSQL28 = "SELECT [1calltotals2RJUNE130].ResolvedStatus, " _
& "[1calltotals2RJUNE130].ResolvedDt,
Sum([1calltotals2RJUNE130].[SumOfSumOfnumber of calls]) " _
& "AS [SumOfSumOfSumOfnumber of calls],
Sum([1calltotals2RJUNE130].[SumOfCountOfLoan Acct #]) " _
& "AS [SumOfSumOfCountOfLoan Acct #],
Sum([1calltotals2RJUNE130].average_no_calls_to_be_resolved) " _
& "AS SumOfaverage_no_calls_to_be_resolved, " _
& "[SumOfSumOfSumOfnumber of calls]/[SumOfSumOfCountOfLoan Acct #] AS Expr1
" _
& "INTO 1calltotals2Rjune130_finaltemp " _
& "FROM 1calltotals2RJUNE130 " _
& "WHERE ((([1calltotals2RJUNE130].ResolvedStatus) =
[Forms]![frm_criteria]![tester1])) " _
& "GROUP BY [1calltotals2RJUNE130].ResolvedStatus,
[1calltotals2RJUNE130].ResolvedDt; "
'make a table showing average number of calls for resolvedstatus selected at
runtime
DoCmd.RunSQL strSQL28

dbAny.Execute strSQL28, dbFailOnError
MsgBox dbAny.RecordsAffected & " records created"


End Sub



John Spencer said:
If you change your procedure to use the Execute method, you can use the
recordsAffected property to get a count.

Dim dbAny as DAO.Database

strSQL2 = "...

dbAny.Execute strSQL2, dbFailOnError
MsgBox dbAny.RecordsAffected & " records created"

RecordsAffected works with any action query.

geebee said:
hi,

I have the following query:

strSQL2 = "SELECT [1calltotals2RJUNE130].ResolvedStatus, " _
& "[1calltotals2RJUNE130].ResolvedDt,
Sum([1calltotals2RJUNE130].[SumOfSumOfnumber of calls]) " _
& "AS [SumOfSumOfSumOfnumber of calls],
Sum([1calltotals2RJUNE130].[SumOfCountOfLoan Acct #]) " _
& "AS [SumOfSumOfCountOfLoan Acct #],
Sum([1calltotals2RJUNE130].average_no_calls_to_be_resolved) " _
& "AS SumOfaverage_no_calls_to_be_resolved, " _
& "[SumOfSumOfSumOfnumber of calls]/[SumOfSumOfCountOfLoan Acct #] AS
Expr1
" _
& "INTO 1calltotals2Rjune130_finaltemp " _
& "FROM 1calltotals2RJUNE130 " _
& "WHERE ((([1calltotals2RJUNE130].ResolvedStatus) =
[Forms]![frm_criteria]![tester1])) " _
& "GROUP BY [1calltotals2RJUNE130].ResolvedStatus,
[1calltotals2RJUNE130].ResolvedDt; "
'make a table showing average number of calls for resolvedstatus selected
at
runtime
DoCmd.RunSQL strSQL2

I would like to show a messagebox that lists how many records the
preceding
pasted...perhaps a count of how many records there are in the new table.
I
do not want to use the default "is about to paste N records into a new
table... do you want to continue?" because I turned the warnings off.

How can I achieve this?

Thanks in advance,
geebee
Click to expand...
Click to expand...
 
J

John Spencer

One: Don't use both execute and RUNSQL methods. You can't use a make table
twice in a row like that without causing a warning message/error. The
second time, the table already exists.

Two: DIM dbAny as DAO.Database Not completely needed, but it is safest to
do this

Three: Add error trapping in case your SQL is failing. (see one)


geebee said:
hi,

I am getting no messagebox. here is what I have now:

Private Sub Label149_Click()

Dim dbAny As Database
Dim strSQL28 As String

'this is the first half of the graph source. intended to build graphs
dynamically
strSQL28 = "SELECT [1calltotals2RJUNE130].ResolvedStatus, " _
& "[1calltotals2RJUNE130].ResolvedDt,
Sum([1calltotals2RJUNE130].[SumOfSumOfnumber of calls]) " _
& "AS [SumOfSumOfSumOfnumber of calls],
Sum([1calltotals2RJUNE130].[SumOfCountOfLoan Acct #]) " _
& "AS [SumOfSumOfCountOfLoan Acct #],
Sum([1calltotals2RJUNE130].average_no_calls_to_be_resolved) " _
& "AS SumOfaverage_no_calls_to_be_resolved, " _
& "[SumOfSumOfSumOfnumber of calls]/[SumOfSumOfCountOfLoan Acct #] AS
Expr1
" _
& "INTO 1calltotals2Rjune130_finaltemp " _
& "FROM 1calltotals2RJUNE130 " _
& "WHERE ((([1calltotals2RJUNE130].ResolvedStatus) =
[Forms]![frm_criteria]![tester1])) " _
& "GROUP BY [1calltotals2RJUNE130].ResolvedStatus,
[1calltotals2RJUNE130].ResolvedDt; "
'make a table showing average number of calls for resolvedstatus selected
at
runtime
DoCmd.RunSQL strSQL28

dbAny.Execute strSQL28, dbFailOnError
MsgBox dbAny.RecordsAffected & " records created"


End Sub



John Spencer said:
If you change your procedure to use the Execute method, you can use the
recordsAffected property to get a count.

Dim dbAny as DAO.Database

strSQL2 = "...

dbAny.Execute strSQL2, dbFailOnError
MsgBox dbAny.RecordsAffected & " records created"

RecordsAffected works with any action query.

geebee said:
hi,

I have the following query:

strSQL2 = "SELECT [1calltotals2RJUNE130].ResolvedStatus, " _
& "[1calltotals2RJUNE130].ResolvedDt,
Sum([1calltotals2RJUNE130].[SumOfSumOfnumber of calls]) " _
& "AS [SumOfSumOfSumOfnumber of calls],
Sum([1calltotals2RJUNE130].[SumOfCountOfLoan Acct #]) " _
& "AS [SumOfSumOfCountOfLoan Acct #],
Sum([1calltotals2RJUNE130].average_no_calls_to_be_resolved) " _
& "AS SumOfaverage_no_calls_to_be_resolved, " _
& "[SumOfSumOfSumOfnumber of calls]/[SumOfSumOfCountOfLoan Acct #] AS
Expr1
" _
& "INTO 1calltotals2Rjune130_finaltemp " _
& "FROM 1calltotals2RJUNE130 " _
& "WHERE ((([1calltotals2RJUNE130].ResolvedStatus) =
[Forms]![frm_criteria]![tester1])) " _
& "GROUP BY [1calltotals2RJUNE130].ResolvedStatus,
[1calltotals2RJUNE130].ResolvedDt; "
'make a table showing average number of calls for resolvedstatus
selected
at
runtime
DoCmd.RunSQL strSQL2

I would like to show a messagebox that lists how many records the
preceding
pasted...perhaps a count of how many records there are in the new
table.
I
do not want to use the default "is about to paste N records into a new
table... do you want to continue?" because I turned the warnings off.

How can I achieve this?

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

New to queries 3
Last Question on Select Query 3
Export count(*), then the records to a txt file 1
Count function 1
Count Question 1
Specifying a date range in a crosstab/pivot query 2
Excel Pivot table calculation 2
Why cant Access count correctly?? 8

Top