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 records manually

Hi everyone, I'm a newbie with this Crystal Reports thing and I was wondering if there is anyone who can help me.

My table has a field named obsolete, depending on the type of "obsolete" I would like to count the number of ocurrencies in my table.

I know I could do this by grouping the field but, I need to group with other 4 fields and I've tried it like that but I guess that the only way that I'll be able to retrieve the data the way I want it is manually. So, is there any if statement or while statement that I could use?

Thanx for any repliesI guess you could try to use if statements in suppressed formulas (look up information on running totals in CR).

I had a similar requirement and found it easier to decode the values in my view (I'm using Oracle PL/SQL).

decode(classId,1,trafficcount,0) class_1,
decode(classId,2,trafficcount,0) class_2,

Ie. If the classId is 1, put the trafficcount in column called class_1, else put 0.|||Thanx for your reply kristyw. Im using informix, I'm not quite sure I can use the decode function, or is it a CR function?

Count Query Question

I have a table that I am trying to do a query on.

Table is named GPFCount2.

CREATE TABLE [GPFCount2] (
[WeekID] [int] NULL ,
[BeginDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Region] [int] NULL ,
[Unit] [int] NULL ,
[GPFCount] [int] NULL
) ON [PRIMARY]

For example:
30 ,'11/11/2006 15:00:00','11/18/2006 14:59:59', 8000 , 192 , 14

The above says that unit 92 had 14 GPFs during the week of 11/11/2006
3PM to 11/18/2006 2:59:59 PM. Unit 192 is part of region 8000. The
time period covered was week 30.

What I want to see is the number of times the unit has been in the top
25 list over the last 5 weeks. Unit 192 is in the top 25 list for
Weeks, 30, 29, 28, and 26.

So my result set for this unit should be:
30 ,'11/11/2006 15:00:00','11/18/2006 14:59:59', 8000 , 192 , 14, 4

The 4 being the number of times in the last 5 weeks that unit 192 was
in the top 25.

And then for Week 29, assuming unit 192 is in the top 25 for weeks
29,28 and 26 (and not 27 or 25), then it would be 3. And the results
from the query would be:
29 ,'11/04/2006 15:00:00','11/11/2006 14:59:59', 8000 , 192 , 14, 3

This is the query I was working with, but it's not working. I'm not
too sure how to make this work.

SelectA.weekid,
A.begindate,
A.EndDate,
A.region,
A.unit,
A.gpfcount,
B.UnitCount

Quote:

Originally Posted by

>From gpfcount2 A

Join
(SelectWeekID,
Unit,
Count(Unit) UnitCount
From gpfcount2
Where WeekID Between WeekID - 4 and WeekID
Group By Unit,WeekID
) B
On A.Unit = B.Unit

Thanks,
Jennifer

INSERTS FOR TABLE (There are inserts only for weeks 30 through 20 for
brevity's sake):

insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 898 , 22
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 777 , 21
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 846 , 21
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 907 , 20
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 608 , 18
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 40 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 107 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 723 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 60 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 78 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 300 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 317 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 658 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 719 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 782 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 2 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 192 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 362 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 456 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 607 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 609 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 7000 , 715 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 182 , 13
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 712 , 13
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 588 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 191 , 19
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 450 , 17
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 498 , 17
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 192 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 445 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 742 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 532 , 15
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 540 , 14
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 715 , 14
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 184 , 13
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 288 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 313 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 78 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 598 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 610 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 840 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 918 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 221 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 452 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 594 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 608 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 706 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 35 , 8
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 112 , 8
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 218 , 8
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 542 , 30
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 35 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 695 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 924 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 533 , 25
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 878 , 18
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 12 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 139 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 698 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 458 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 528 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 740 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 911 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 778 , 14
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 192 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 550 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 738 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 2 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 176 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 450 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 571 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 7000 , 715 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 840 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 875 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 925 , 12
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 123 , 34
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 192 , 32
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 264 , 19
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 601 , 18
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 875 , 17
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 550 , 16
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 7000 , 761 , 15
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 141 , 14
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 3 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 745 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 750 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 816 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 190 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 506 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 533 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 899 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 903 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 175 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 300 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 311 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 397 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 450 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 597 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 743 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 878 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 782 , 20
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 192 , 19
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 317 , 18
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 60 , 16
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 695 , 16
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 85 , 15
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 190 , 14
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 592 , 13
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 439 , 12
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 576 , 12
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 349 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 509 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 563 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 816 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 280 , 10
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 123 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 337 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 388 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 601 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 698 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 7000 , 715 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 812 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 832 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 368 , 8
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 490 , 8
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 777 , 26
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 907 , 22
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 597 , 18
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 285 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 396 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 439 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 450 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 781 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 898 , 13
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 906 , 13
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 12 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 745 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 748 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 840 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 875 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 889 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 192 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 749 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 755 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 107 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 443 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 540 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 595 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 839 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 190 , 9
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 907 , 29
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 12 , 25
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 695 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 777 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 778 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 788 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 439 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 566 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 723 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 7000 , 774 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 40 , 15
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 396 , 14
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 607 , 14
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 175 , 13
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 336 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 498 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 781 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 829 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 140 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 311 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 448 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 514 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 791 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 139 , 10
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 551 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 788 , 33
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 723 , 24
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 192 , 18
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 397 , 15
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 166 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 498 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 695 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 898 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 264 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 601 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 694 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 396 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 708 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 733 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 439 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 527 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 550 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 190 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 7000 , 217 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 399 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 425 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 609 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 728 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 787 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 131 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 604 , 28
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 223 , 18
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 723 , 18
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 724 , 17
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 598 , 15
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 3 , 14
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 550 , 13
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 619 , 13
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 397 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 540 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 601 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 490 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 498 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 658 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 782 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 823 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 334 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 774 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 870 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 43 , 9
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 549 , 9
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 192 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 443 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 527 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 566 , 8
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 407 , 21
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 7000 , 451 , 20
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 723 , 19
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 755 , 17
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 286 , 14
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 336 , 14
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 285 , 13
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 778 , 13
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 89 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 264 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 445 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 176 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 292 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 324 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 349 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 480 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 7000 , 715 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 201 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 396 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 469 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 578 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 724 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 132 , 9
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 262 , 9
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 288 , 9
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 723 , 33
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 550 , 27
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 2 , 25
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 349 , 20
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 911 , 20
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 829 , 18
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 396 , 17
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 782 , 17
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 60 , 16
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 320 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 7000 , 587 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 788 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 796 , 14
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 81 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 285 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 501 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 292 , 12
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 9000 , 799 , 12
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 9000 , 430 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 450 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 790 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 898 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 399 , 10
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 745 , 10
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 750 , 10Count Query Question|||Count Query Question|||Roy Harvey wrote:

Quote:

Originally Posted by

AND B.WeekID BETWEEN A.WeekID - 4 and B.WeekID

Did you mean A.WeekID there at the end?

Count

Hello,

I need to retrieve all records from a table named Blogs and the number
of Posts associated with which Blog giving the name NumberOfPosts to
that extra column.

I have the following:

SELECT b.*, p.COUNT(*) AS NumberOfPosts
FROM dbo.Blogs b
LEFT JOIN dbo.Posts p
ON b.BlogId = p.BlogId

I get the error:
Incorrect syntax near '*'.

Could someone, please, help me out?

Thanks,
Miguel

Try this:

SELECT b.*, COUNT(p.*) AS NumberOfPosts
FROM dbo.Blogs b
LEFT JOIN dbo.Posts p
ON b.BlogId = p.BlogId

|||

I think you need agroup by clause to use the Count(*), do you have to return all the columns in dbo.Blogs?

|||

And change the top of your query to...

SELECT b.*, COUNT(*) AS NumberOfPosts

Could not Create Publication

Hi,
I created 2 windows user named PublishUser and ReplSnapAgent with less
previllege. Ran SSMS using PublishUser and created snapshot publication.
Used RepSnapAgent user as Snapshot Agent. I got an error saying
SQL Server could not create publication 'Product'. (New Publication Wizard)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Proxy "Computer-Name\ReplSnapAgent" is not a valid Windows user.
Changed database context to 'ReplTesting'. (.Net SqlClient Data Provider)
Is anybody else help me out in solving this issue?
Is ReplSnapAgent a local user account on the computer Computer-Name? Is it
in the dbo_role on the distribution database and the publication?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bharathi" <NoSpam_BB@.hotmail.com> wrote in message
news:Of7JjnPKHHA.2632@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I created 2 windows user named PublishUser and ReplSnapAgent with less
> previllege. Ran SSMS using PublishUser and created snapshot publication.
> Used RepSnapAgent user as Snapshot Agent. I got an error saying
> SQL Server could not create publication 'Product'. (New Publication
> Wizard)
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> Proxy "Computer-Name\ReplSnapAgent" is not a valid Windows user.
> Changed database context to 'ReplTesting'. (.Net SqlClient Data Provider)
> Is anybody else help me out in solving this issue?
>

Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in SQL 2

Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in SQL 2005 Express?
I DownLoad the tool named XM.exe,but It's only a Query tool and I have to do everything using scripts.I realy want to know Is there a tool that I can use to Create db and restore db and backup db and create table etc. like the Admination tools in SQL 2000 ?

Thanks for u kindness.

Tonny.

You can use the tools that come with the June CTP Developer Edition.

|||

Thanks for ur kindness ,and Is it a single tool or integrated with the SQL2005 TCP Developer Version?Is it free to download?If yes, Could u tell me where Can I Download the tool or Where Can I DownLoad the SQL 2005 TCP Developer Version?

Thank u again.

Tonny.

|||

SQL Server Express Edition did not originally ship with any type of Management Tools. After SQL Server 2005 Service Pack 1, you have the option to download SQL Server Management Studio Express with SQL Server Express Edition with Advanced Services.

Compare the new offerings: http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in S

Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in SQL 2005 Express?
I DownLoad the tool named XM.exe,but It's only a Query tool and I have to do everything using scripts.I realy want to know Is there a tool that I can use to Create db and restore db and backup db and create table etc. like the Admination tools in SQL 2000 ?

Thanks for u kindness.

Tonny.

You can use the tools that come with the June CTP Developer Edition.

|||

Thanks for ur kindness ,and Is it a single tool or integrated with the SQL2005 TCP Developer Version?Is it free to download?If yes, Could u tell me where Can I Download the tool or Where Can I DownLoad the SQL 2005 TCP Developer Version?

Thank u again.

Tonny.

|||

SQL Server Express Edition did not originally ship with any type of Management Tools. After SQL Server 2005 Service Pack 1, you have the option to download SQL Server Management Studio Express with SQL Server Express Edition with Advanced Services.

Compare the new offerings: http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/