Dohko


Hi

How can I access the Freq variable

In this code

SELECT State, COUNT(*) as Freq;
FROM sample;
GROUP BY State;
INTO CURSOR Counter

If i write

Freq it prints all 1's.

I would like to have access and be able to do Math with that column

Thanks




Re: Accessing Data from a Query

Alex Feldstein


If you do a BROWSE immediately after the query what do you see Are the values there as expected

Or a test programmatically:

Code Snippet

SELECT State, COUNT(*) as Freq;
FROM sample;
GROUP BY State;
INTO CURSOR Counter

SCAN

State, TRANSFORM(Freq, "999,999")

ENDSCAN






Re: Accessing Data from a Query

Ronald Ricardo Ramirez Moran

Find the record to show:

FOR i = 1 TO 51
  SELECT sample
  LOCATE FOR State = i
     Freq
ENDFOR

*** or using this code:
i = 45 && Any number
SELECT sample
LOCATE FOR State = i
   Freq

Regards,







Re: Accessing Data from a Query

Dohko

Yes when I do Browse I see the numbers there. in a column named Freq.

But that's a temporary column right





Re: Accessing Data from a Query

Naomi Nosonovsky

Code Snippet

LOCAL lcStr

SELECT State, COUNT(*) as Freq;
FROM sample;
GROUP BY State;
INTO CURSOR Counter

lcStr = "State Counter"

#define CRLF chr(13) + CHR(10)

scan

State, Freq

lcStr = m.lcStr + CRLF + State + space(5) + padl(Freq,10)

endscan

_cliptext = m.lcStr && You now have it in a buffer and can paste

strtofile(m.lcStr, 'MyResults.txt')

modify file myResults.txt

Hope this helps.




Re: Accessing Data from a Query

Dohko

Sorry I don't understand it.

I'll post my entire Code.

Code Snippet

CLOSE ALL
DELETE FILE 'G:\sample.DBF'

CREATE TABLE 'G:\sample.DBF' FREE ;
(SORT_ID N(4,0) NOT NULL, ;
STATE N(2,0) NOT NULL, ;

FREQUENCY N(3,0) NOT NULL)
APPEND FROM 'G:\database.xls' TYPE XLS
INDEX ON STATE TAG STATE


SET ALTERNATE TO temp2.txt
SET ALTERNATE ON

SELECT State, COUNT(*) as Freq;
FROM sample;
GROUP BY State;
INTO CURSOR Counter
DIMENSION StateArray(2,51)

StateArray(2,1 )="Alabama"
StateArray(2,2 )="Alaska"
StateArray(2,3 )="Arizona"
StateArray(2,4 )="Arkansas"
StateArray(2,5 )="California"
StateArray(2,6 )="Colorado"
StateArray(2,7 )="Connecticut"
StateArray(2,8 )="Delaware"
StateArray(2,9 )="District of Columbia"
StateArray(2,10)="Florida"
StateArray(2,11)="Georgia"
StateArray(2,12)="Hawaii"
StateArray(2,13)="Idaho"
StateArray(2,14)="Illinois"
StateArray(2,15)="Indiana"
StateArray(2,16)="Iowa"
StateArray(2,17)="Kansas"
StateArray(2,18)="Kentucky"
StateArray(2,19)="Louisiana"
StateArray(2,20)="Maine"
StateArray(2,21)="Maryland"
StateArray(2,22)="Massachusetts"
StateArray(2,23)="Michigan"
StateArray(2,24)="Minnesota"
StateArray(2,25)="Mississippi"
StateArray(2,26)="Missouri"
StateArray(2,27)="Montana"
StateArray(2,28)="Nebraska"
StateArray(2,29)="Nevada"
StateArray(2,30)="New Hampshire"
StateArray(2,31)="New Jersey"
StateArray(2,32)="New Mexico"
StateArray(2,33)="New York"
StateArray(2,34)="North Carolina"
StateArray(2,35)="North Dakota"
StateArray(2,36)="Ohio"
StateArray(2,37)="Oklahoma"
StateArray(2,38)="Oregon"
StateArray(2,39)="Pennsylvania"
StateArray(2,40)="Rhode Island"
StateArray(2,41)="South Carolina"
StateArray(2,42)="South Dakota"
StateArray(2,43)="Tennessee"
StateArray(2,44)="Texas"
StateArray(2,45)="Utah"
StateArray(2,46)="Vermont"
StateArray(2,47)="Virginia"
StateArray(2,48)="Washington"
StateArray(2,49)="West Virginia"
StateArray(2,50)="Wisconsin"
StateArray(2,51)="Wyoming"


StateArray(1,1 )="1"
StateArray(1,2 )="2"
StateArray(1,3 )="3"
StateArray(1,4 )="4"
StateArray(1,5 )="5"
StateArray(1,6 )="6"
StateArray(1,7 )="7"
StateArray(1,8 )="8"
StateArray(1,9 )="9"
StateArray(1,10)="10"
StateArray(1,11)="11"
StateArray(1,12)="12"
StateArray(1,13)="13"
StateArray(1,14)="14"
StateArray(1,15)="15"
StateArray(1,16)="16"
StateArray(1,17)="17"
StateArray(1,18)="18"
StateArray(1,19)="19"
StateArray(1,20)="20"
StateArray(1,21)="21"
StateArray(1,22)="22"
StateArray(1,23)="23"
StateArray(1,24)="24"
StateArray(1,25)="25"
StateArray(1,26)="26"
StateArray(1,27)="27"
StateArray(1,28)="28"
StateArray(1,29)="29"
StateArray(1,30)="30"
StateArray(1,31)="31"
StateArray(1,32)="32"
StateArray(1,33)="33"
StateArray(1,34)="34"
StateArray(1,35)="35"
StateArray(1,36)="36"
StateArray(1,37)="37"
StateArray(1,38)="38"
StateArray(1,39)="39"
StateArray(1,40)="40"
StateArray(1,41)="41"
StateArray(1,42)="42"
StateArray(1,43)="43"
StateArray(1,44)="44"
StateArray(1,45)="45"
StateArray(1,46)="46"
StateArray(1,47)="47"
StateArray(1,48)="48"
StateArray(1,49)="49"
StateArray(1,50)="50"
StateArray(1,51)="51"


n=1
FOR n=1 to 51
SELECT Counter


["]+StateArray(1,n)+["]
","
["]+StateArray(2,n)+["]
","
Freq

ENDFOR

What I need is to be able to display

1,Alabama,23

2,Alaska,43

And so on.

I Also need to do some Math with the numbers like calculate percentages but I need to know how to have access to frequencies. Right now I only get 1's .

Thanks






Re: Accessing Data from a Query

Ronald Ricardo Ramirez Moran

The field "State" type is character, then you need to transform the for..endfor counter as character. Here is a example:

Code Snippet


n=1
FOR n=1 to 51
SELECT Counter
Locate for State = Transform(n)

["]+StateArray(1,n)+["]
","
["]+StateArray(2,n)+["]
","
Freq

ENDFOR

Regards,




Re: Accessing Data from a Query

Dohko

It says operator mismatch



Re: Accessing Data from a Query

Naomi Nosonovsky

It would be much easier to use cursors instead of arrays, if you need to do some data manipulations.

In other words, let's try to add to your code:

Code Snippet

create cursor curStates (StateID C(3), State C(50))

append from array StateArray

scan

StateID, State

** Save info into variable as I've shown in my sample above

endscan

********************

Where do the other numbers come from





Re: Accessing Data from a Query

Dohko

Would this work in Fox Pro 8



Re: Accessing Data from a Query

Naomi Nosonovsky

Yes, it would work in any VFP version starting from VFP6.