Count records when RecordSource is a Stored Procedure

I have a stored procedure named mySP that looks basically like this:
Select Field1, Field2
From tblMyTable
Where Field 3 = 'xyz'

What I do is to populate an Access form:
DoCmd.Openform "frmMyFormName"
Forms!myFormName.RecordSource = "mySP"

What I want to do in VBA is to open frmContinuous(a datasheet form) if
mySP returns more than one record or open frmDetail if mySP returns
only one record.

I'm stumped as to how to accomplish this, without running mySP twice:
once to count it and once to use it as a recordsource.
Thanks,
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404260611.33a36012@.posting.google.c om...
> I have a stored procedure named mySP that looks basically like this:
> Select Field1, Field2
> From tblMyTable
> Where Field 3 = 'xyz'
> What I do is to populate an Access form:
> DoCmd.Openform "frmMyFormName"
> Forms!myFormName.RecordSource = "mySP"
> What I want to do in VBA is to open frmContinuous(a datasheet form) if
> mySP returns more than one record or open frmDetail if mySP returns
> only one record.
> I'm stumped as to how to accomplish this, without running mySP twice:
> once to count it and once to use it as a recordsource.
> Thanks,
> lq

I don't know much about VBA, but if you return the results of the procedure
in an ADO RecordSet object, then you should be able to count the rows on the
client side, and format the data accordingly. You might get a better answer
in an Access newsgroup, though.

Simon

Count of two fields with reference to someother field

Hi,

I have three fields...field1,field2 and field3.
Data fed into field1 and field2 is numeric and
data fed into field3 is string.

I need to count the number of times each of fieild1 and field2 is fed when field3 is equal to "BA Test". Likewise, I have couple of values defined for field3...I want sum of count of field1 and field2.

In other words, I can call it as conditional counting.

Thanks in advance.

Cheers
Sreenath NookalaIn SQL try:

Select field3, count(field1) field1, count(field2) field2
from
{table}
group by field3

You can then use a function to sum count of field1 and field2|||Actually, I am pulling data from Rational ClearQuest and creating a report in Crystal Reports. MS Access is one of the databases supported by Rational ClearQuest.

I am not sure how SQL commands work in MS Access.

Can we tell me how to do in GUI mode in Crystal Reports.

Thanks
Sreenath Nookala|||In Access write a query and save it. When designing the report, design it based on that query|||well u can use a variable to store count value .. means when ever u get condition true then increment this variable with one ... in this u can get count of flds ...
e.g

numbervar countFld1;
if fld3 = 'BA Test' then
countFld1 = countFld1 + 1

then u can display this variable ... u will have to reset ur variable if want to recalculate against each group ... by using

numbervar countFld1 := 0;

this may solve ur poblem upto the query i understand ...sql

Count for each word in a field

Is it possible to create a SELECT query that will give me the count of all the words in a single field?

SELECT field1, count(field1) FROM table1 GROUP BY field1

won't do it because field1 has multiple words and I want them broken out and counted individually.

Data example:

Field1
-------
dog
dog ate my homework
cat
dog and cat
tail
dog tail
...

I want to get a count of every occurance of each word (e.g. "dog") whether in the field by itself or with other words.

Hope this makes sense.

AlNo, you would require a function that takes a text string as input, and counts the number of occurences of another string in it so that you could write:

select field1, word_count(field1,'dog')
from table1;

Depending on your DBMS's capabilities, such a function may already exist or you may be able to write one for yourself. In Oracle for example, you could certainly write one yourself and maybe one already exists in the Oracle Text tool (I don't know).|||Tony,

Thanks for the response. I forgot to mention the DB engine I'm using: MySQL.

I'm not sure I explained it well. Per your function example you're passing the word "dog" to the function. I need it to count all of the words in each field. Using my original example, the results would look like this (with an ORDER BY added):

field1 qty
-- --
dog 4
cat 3
tail 2
and 1
ate 1
my 1

It probably still requires a function to accomplish this.

Al|||Oh, I see - that's rather different. What you need is first to split out all the words into one per row like this:

word
--
dog
dog
dog
dog
cat
cat
cat
tail
tail
and
ate
my

Then of course it is easy to group and count the words. But how to split them up? If you could be sure there were no more than N words in any sentence then you could use a brute-force approach with a user-defined function like this:

select get_word(field1,1) as word from table1
union all
select get_word(field1,2) as word from table1
union all
...
union all
select get_word(field1,N) as word from table1

However, that probably isn't what you need. I don't know MySQL at all, but in Oracle you could achieve this (without the "N" words limit) by writing a function that returns a collection (like an array), and then selecting from the results of the function - a fairly complex operation.

It may be that this can't be done in MySQL using just a select statement - you may have to write a program that populates a temporary table with all the words, and then select from that.|||I'm thinking I'll create a new table that will house the individual words and after writing the "phrase" to field1, I'll parse the words and write them to the new table for future counting.

Thanks for your feedback!
Al

Could not figure out the profiler meaning for SP

I have a SP which is as simple as
create procedure SPName @.Para
-- Query 1
select field1, ...
from view1
where field1 = @.para
-- Query 2
select field1, ...
from view2
where field1 = @.para
Go
But the view1 is a little bit complex. It nests 3 sub-views.
Usually SP takes 1s to execute. But one day the end user reports the SP was
slow. Then I run the SP on QA. And I found strange things.
For the first time to execute, it takes longer time to execute, like 7s.
Then if I continue to execute the SP(change the parameter or not) it runs
fast, 1s. Then I use the profiler check the details. I traced the
SP:completed, SQL:stmtcompleted, etc.
Duration:
in event SP:StmtCompleted, the query 1: 1688, query 2: 234
SP:Completed 1922. That makes sense since it's 1688+ 234
But following up the SQL:StmtCompleted : 5625, SQL:BatchComplted:5626. All
the text data is executing the SP, like exec SPName @.para.
When the SP execute 1s, I see the duration of SQL:StmtCompleted and
SQL:BatchComplted is very close to SP:Completed. So total time is close to
1s. But for long run SP, I don't kown what else the SQL Engine have to do
under the hood. And why it's different for the execution time.
Additionaly, in the trace file, I saw a lot of SQLTransaction going on. The
objectName is sort_init, TextData: begin, commit. In the SP, only has
query. I don't know why there is SQLTransaction and what the transaction
does. Can any body explain what does that mean?
I saw the trace start with SQL:BatchStarting; -->SQL:StmtStarting;
-->SP:Starting with same Textdata ane end with SP:Completed -->
SQL:StmtCompleted-->SQL:BatchComplted with same Textdata. The duration of
SP:Completed is always correct. But duration of SQL:StmtCompleted and
SQL:BatchComplted are various. what's the meaning of duration of
SQL:BatchCompleted?
Any help?
Robertrobert
When you first time run the SP , query optimizer is created an execution
plan to produce the query and cached the plan into the memory (buffer pool).
It does not happen (it might recompile under some circumstances) when you
run the SP for the second,third times . The execution plan is taken from the
cache which seep up the query
Try investigate an execution plan , see any differences. It is possible
that a 'bad' query plan is chosen.
Also run SET STATISTICS IO ON to see what is going on
"robert" <robert@.discussions.microsoft.com> wrote in message
news:962BE2E5-1B80-4470-BB95-D28846F55B8A@.microsoft.com...
> I have a SP which is as simple as
> create procedure SPName @.Para
> -- Query 1
> select field1, ...
> from view1
> where field1 = @.para
> -- Query 2
> select field1, ...
> from view2
> where field1 = @.para
> Go
> But the view1 is a little bit complex. It nests 3 sub-views.
> Usually SP takes 1s to execute. But one day the end user reports the SP
was
> slow. Then I run the SP on QA. And I found strange things.
> For the first time to execute, it takes longer time to execute, like 7s.
> Then if I continue to execute the SP(change the parameter or not) it runs
> fast, 1s. Then I use the profiler check the details. I traced the
> SP:completed, SQL:stmtcompleted, etc.
> Duration:
> in event SP:StmtCompleted, the query 1: 1688, query 2: 234
> SP:Completed 1922. That makes sense since it's 1688+ 234
> But following up the SQL:StmtCompleted : 5625, SQL:BatchComplted:5626. All
> the text data is executing the SP, like exec SPName @.para.
> When the SP execute 1s, I see the duration of SQL:StmtCompleted and
> SQL:BatchComplted is very close to SP:Completed. So total time is close to
> 1s. But for long run SP, I don't kown what else the SQL Engine have to do
> under the hood. And why it's different for the execution time.
> Additionaly, in the trace file, I saw a lot of SQLTransaction going on.
The
> objectName is sort_init, TextData: begin, commit. In the SP, only has
> query. I don't know why there is SQLTransaction and what the transaction
> does. Can any body explain what does that mean?
> I saw the trace start with SQL:BatchStarting; -->SQL:StmtStarting;
> -->SP:Starting with same Textdata ane end with SP:Completed -->
> SQL:StmtCompleted-->SQL:BatchComplted with same Textdata. The duration of
> SP:Completed is always correct. But duration of SQL:StmtCompleted and
> SQL:BatchComplted are various. what's the meaning of duration of
> SQL:BatchCompleted?
> Any help?
> Robert
>
>
>
>
>|||Thx for reply.
But I'm not sure it's the problem related with execution plan. Even in the
bad case, the duration of SP:Complated is 1s from the trace. So you mean the
extra time SQL engine use to generate the execution plan?
Also it's difficult to reproduct the bad case. If I run the SP after10
mins/20 mins later, It might happen. But not always.
Any other suggestion?
"Uri Dimant" wrote:

> robert
> When you first time run the SP , query optimizer is created an execution
> plan to produce the query and cached the plan into the memory (buffer pool
).
> It does not happen (it might recompile under some circumstances) when you
> run the SP for the second,third times . The execution plan is taken from t
he
> cache which seep up the query
> Try investigate an execution plan , see any differences. It is possible
> that a 'bad' query plan is chosen.
> Also run SET STATISTICS IO ON to see what is going on
>
>
> "robert" <robert@.discussions.microsoft.com> wrote in message
> news:962BE2E5-1B80-4470-BB95-D28846F55B8A@.microsoft.com...
> was
> The
>
>|||Hi
You may want to profile SP:CacheMiss and SP:Recompile. You have not posted
your code or DDL, but if it contains conditional execution or statements
then you may want to separate it into smaller sub-procedures.
John
"robert" <robert@.discussions.microsoft.com> wrote in message
news:C8A4EE2A-3CFD-4FC7-BED7-21AD0302728D@.microsoft.com...
> Thx for reply.
> But I'm not sure it's the problem related with execution plan. Even in the
> bad case, the duration of SP:Complated is 1s from the trace. So you mean
> the
> extra time SQL engine use to generate the execution plan?
> Also it's difficult to reproduct the bad case. If I run the SP after10
> mins/20 mins later, It might happen. But not always.
> Any other suggestion?
> "Uri Dimant" wrote:
>|||No SP:Recompile. And all CacheMiss columns are blank. My code is
View1 is like select field11, field12, ... from view11
View2 is like select field21, field22, ... from view21
And view1, view2 nested 3 level sub views.
"John Bell" wrote:
> Hi
> You may want to profile SP:CacheMiss and SP:Recompile. You have not posted
> your code or DDL, but if it contains conditional execution or statements
> then you may want to separate it into smaller sub-procedures.
> John
> "robert" <robert@.discussions.microsoft.com> wrote in message
> news:C8A4EE2A-3CFD-4FC7-BED7-21AD0302728D@.microsoft.com...
>
>|||Hi
If the code is:
create procedure SPName @.Para
-- Query 1
IF @.para = 1
select field1, ...
from view1
where field1 = @.para
ELSE
-- Query 2
select field1, ...
from view2
where field1 = @.para
Go
Then try to split each of the queries into two separate procedures. If you
mainly use one query then the execution plan may not be optimal for the
second query.
create procedure SPName_1 @.Para
select field1, ...
from view1
where field1 = @.para
create procedure SPName_2 @.Para
select field1, ...
from view2
where field1 = @.para
create procedure SPName @.Para
IF @.para = 1
EXEC spName_1 @.Para
ELSE
EXEC spName_2 @.Para
John
"robert" <robert@.discussions.microsoft.com> wrote in message
news:5CA451CB-4956-4DA1-9466-85C0854506C8@.microsoft.com...
> No SP:Recompile. And all CacheMiss columns are blank. My code is
> View1 is like select field11, field12, ... from view11
> View2 is like select field21, field22, ... from view21
> And view1, view2 nested 3 level sub views.
>
> "John Bell" wrote:
>|||The SP have to execute two querys. I think the SQL engine use the correct
execution plan from the duration of the SP:Completed event. But the SQL
engine takes additional times to do sth else.
Robert
"John Bell" wrote:

> Hi
> If the code is:
> create procedure SPName @.Para
> -- Query 1
> IF @.para = 1
> select field1, ...
> from view1
> where field1 = @.para
> ELSE
> -- Query 2
> select field1, ...
> from view2
> where field1 = @.para
> Go
> Then try to split each of the queries into two separate procedures. If you
> mainly use one query then the execution plan may not be optimal for the
> second query.
> create procedure SPName_1 @.Para
> select field1, ...
> from view1
> where field1 = @.para
> create procedure SPName_2 @.Para
> select field1, ...
> from view2
> where field1 = @.para
> create procedure SPName @.Para
> IF @.para = 1
> EXEC spName_1 @.Para
> ELSE
> EXEC spName_2 @.Para
> John
> "robert" <robert@.discussions.microsoft.com> wrote in message
> news:5CA451CB-4956-4DA1-9466-85C0854506C8@.microsoft.com...
>
>|||Could a reason for this be that the additional time is taken to load an
index on the first execution and later runs use the loaded index ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2315qfq6UFHA.628@.tk2msftngp13.phx.gbl...
> robert
> When you first time run the SP , query optimizer is created an execution
> plan to produce the query and cached the plan into the memory (buffer
> pool).
> It does not happen (it might recompile under some circumstances) when you
> run the SP for the second,third times . The execution plan is taken from
> the
> cache which seep up the query
> Try investigate an execution plan , see any differences. It is possible
> that a 'bad' query plan is chosen.
> Also run SET STATISTICS IO ON to see what is going on
>
>
> "robert" <robert@.discussions.microsoft.com> wrote in message
> news:962BE2E5-1B80-4470-BB95-D28846F55B8A@.microsoft.com...
> was
> The
>|||Hi
If you have conditional execution then it is most likely that the plan
is inappropriate. You would be able work out which path was being
executed by analysing the parameters. Splitting the procedure into
sub-procedures means that a plan for each sub-procedure can be created
and therefore the situation would not arise.
John|||Hi
You don't say if there is any other activity going on on the server at
this time?
What is the RPC:Completed value?
Are you logging to a database table/screen/file?
You may want to monitor the Lock events and Database Events!
John