counting the same records and get the value to a field 3

[aimbots27]

Hi!
I would like to ask some help please. I'am a newbie in here. I am using visual foxpro 9 and I would like to use count, to count the same records on the database and put the results to a field so that I can used as variable in a report.
Sample database:

acct |name | gender
---------------------
001 |henry | male
---------------------
001 |carl | male
---------------------
002 |john | male
---------------------
002 |kirk | male
---------------------
002 |love | female


I would like to get the result as:

acct |name | gender | Results
---------------------
001 |henry | male | 2
---------------------
001 |carl | male | 2
---------------------
002 |john | male | 3
---------------------
002 |kirk | male | 3
---------------------
002 |love | female | 3

Just need help for visual foxpro 9 commands. Thank you!

 

[GriffMG]

Are you trying to get the total count (number of records) for each account to be stored in the results column?

You are after a SQL statement or something like that?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[aimbots27]

Nope. I'm not after a SQL statement. Just visual foxpro commands, because the output will be used in reports

 

[Olaf Doschke]

>so that I can used as variable in a report
A report variable is defined in the report, it is a variable, not a field of the report driving table or cursor.

You can count similar records if you order by acct and also defeine a report group by the acct field. You define a report variable named groupcount and set the calculation to count, initial and reset value can stay at 0, you let the variable value be reset based on the group. Youll see what I mean if you go into the variables dialog via the report menu.

You can print the count in the group footer, for example, by adding a report control printing the variable name. There is no need to put this count into the data.

Bye, Olaf.

 

[aimbots27]

I am just trying to get the results. that's it. Please help me how

 

[Olaf Doschke]

You want the results in a report. I helped you to do it the way it's intended instead of the way you want to do it. It's not needing any table modifications and is faster done than explained. So why don't you give it a try, first?

Bye, Olaf.

 

[aimbots27]

Forget the report. Just in the database alone..

 

[vgulielmus]

Possible SQL solution.

CREATE CURSOR cdemo (acct C(3),name C(25), gender C(6))
INSERT INTO cdemo VALUES ('001','henry',' male')
INSERT INTO cdemo VALUES ('001','carl',' male')
INSERT INTO cdemo VALUES ('002','john',' male')
INSERT INTO cdemo VALUES ('002','kirk',' male')
INSERT INTO cdemo VALUES ('002','love',' female')

SELECT cdemo.*,ccount.result ;
	FROM cdemo,;
	(SELECT acct,COUNT(acct) as result FROM cdemo GROUP BY acct) ccount;
	WHERE cdemo.acct=ccount.acct;
	INTO CURSOR crepo

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[GriffMG]

If you get the count per acct into a table/cursor, you can do loads of stuff not just reporting.

Assuming you have added your column 'results' as a numeric field to your chose table already:

SELECT TABLE1
INDEX ON ACCT TAG ACCT
GO TOP
DO WHILE .NOT. EOF()
	m.ACCT = ACCT
	m.OLDREC = RECNO()
	COUNT FOR ACCT=m.ACCT TO m.COUNTER
	REPLACE RESULTS WITH m.COUNTER FOR ACCT=m.ACCT
	GOTO (m.OLDREC)
	DO WHILE .NOT. EOF() .AND. ACCT=m.ACCT
		SKIP
	ENDDO
ENDDO

The SQL equivalent would be much shorter and maybe quicker...

SELECT TABLE1.*,CCOUNT.RESULT FROM TABLE1, (SELECT ACCT,COUNT(ACCT) AS RESULT FROM TABLE1 GROUP BY ACCT) CCOUNT	WHERE TABLE1.ACCT=CCOUNT.ACCT INTO CURSOR CRESULT

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[aimbots27]

Thank you Griff!! This is what I am looking for. Thank you so much..thanks also guys to those who reply.

 

[GriffMG]

I think vgulielmus got there first.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[GriffMG]

Thinking about it - this would be faster, because it results in just two complete passes through the table (plus the index).

PRIVATE M.ACCT, M.OLDREC, M.COUNTER
SELECT TABLE1
INDEX ON ACCT TAG ACCT
GO TOP
DO WHILE .NOT. EOF()
	m.ACCT = ACCT
	m.OLDREC = RECNO()
	m.COUNTER = 0
	DO WHILE .NOT. EOF() .AND. ACCT=m.ACCT
		m.COUNTER = m.COUNTER +1
		SKIP
	ENDDO
	GOTO (m.OLDREC)
	DO WHILE .NOT. EOF() .AND. ACCT=m.ACCT
		REPLACE RESULTS WITH m.COUNTER 
		SKIP
	ENDDO
ENDDO

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Olaf Doschke]

Well, SQL would be quite simple (and is part of VFP itself quite long), but if you need legacy code, you could rather ask in forum182 or at least tell you need code compatible to your VFP version.

Anyway, even in legacy reports you can work it out without touching the table or data at all, compute the count on the fly within a report.

Bye, Olaf.