Urgently ask, ask VFP editor query summary and printing problems!

szlonely

I have a table called AAA.DBF with the following fields and records, please see:
 A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 A13 A14
1563 1 2 5 9 7 1 1 2 1 152 1 1 268
2123 1 1 5 6 6 1 1 2 2 52 2 1 1168
4212 2 2 5 7 7 2 2 2 1 22 1 2 18
2913 1 2 8 9 6 1 2 1 2 252 1 2 68
9125 2 1 5 7 7 2 2 1 1 52 2 2 158
2823 1 1 5 9 5 3 3 1 1 352 1 3 148
1123 3 2 8 7 5 3 3 3 1 452 3 3 163
9187 3 2 5 6 7 3 2 3 1 212 3 3 156

I want to query and summarize and print 2 tables according to the above library, as follows
first slide
 aa1 (summary of column A1 in the table above)
 AA2 (Here, find the number recorded as 1 in the above table A2)
 aa3 (Here the number of records in the above table A2 is 2)
 aa4 (Here, find the number of records in Table A2 as 3)
 aa5 (Here, find the number recorded as 1 in the above table A3)
 aa8 (Here, find the number recorded as 2 in the above table A3)
  .
  .
  (As above, find the number of records in Table A4-A10, respectively
the second one
Ba111 (Here is the sum of the A11 records recorded as 1 in A12)
Ba112 (Here is the sum of the A11 records recorded as 2 in A12)
Ba113 (Here is the sum of the A11 records recorded as 3 in A12)
Ba131 (Here is the sum of the A14 records recorded as 1 in A13)
Ba132 (Here is the sum of the A14 records recorded as 2 in A13)
Ba133 (Here is the sum of the A14 records in A13 recorded as 3)
 

How to compile Kazakhstan, please write detailed source code, thank you very much!

170385215

The first table:

select'aa1' as field 1, sum(A1) as field 2 from aaa union all
select'aa2' as field 1, count(iif(a2=1,1,0)) as field 2 from aaa union all
select'aa3' as field 1, count(iif(a2=2,1,0)) as field 2 from aaa union all

...


The second table is similar

szlonely

Thank you very much. I executed according to your compilation, but the results cannot be displayed. I still make the library query results of the above table to be printed. I look at the operation. The library name is BBB.DBF The structure is as follows

AA1 AA2 AA3 AA4 AA5...AAX BA111 BA112 BA113 BA131 BA132 BA133
Put the result here, which is the requirement listed above...

Please save the results of the requirements listed above to this table, thank you!

170385215

lcString1='select sum(a1) as aa1'
s=2
for i=2 to 10
n=transform(i)
for j=1 to 3
lcString1=lcString1+',sum(iif(a&n.='+transform(j)+',1,0)) as aa'+transform(s)
s=s+1
endfor
endfor
lcString1=lcString1+' into table table1 from aaa'
&lcString1&&generates table 1 table1
browse&&take a look




lcString2='select '
i=12
n=transform(i)
m=transform(i-1)
for j=1 to 3
lcString2=lcString2+'sum(iif(a&n.='+transform(j)+',a&m.,0)) as ba&m'+transform(j)+','
endfor
i=13
n=transform(i)
m=transform(i+1)
for j=1 to 3
lcString2=lcString2+'sum(iif(a&n.='+transform(j)+',a&m.,0)) as ba&n'+transform(j)+','
endfor
lcString2=left(lcString2,len(lcString2)-1)+' into table table2 from aaa'
&lcString2&&generates table 2 table2
browse&&take a look

select table1.*,table2.* into table total table from table1,table2
browse&&take a look

szlonely

Your source code can be solved compared to the above AAA.DBF library and the generated query library. I also learned how to do it. However, because the field name has a rule rate, your source code is most simplified according to the rule rate, so my method Still not mastering it!
If I change the field name of the AAA library and not write it as A1...A10, I change it to Chinese or alphabetic random field name, the field name in the generated query library is changed to the field name to be queried plus the query condition library, The records in the AAA library field are not only 1-3, there are other 4,5,6,7 in this order, how to write the source code, I changed the library to the following:
There is a table called AAA.DBF. The table has the following fields and records, please see:
abds bse aew esd erw add ghj puuu iuy ki sda hgf iuy iuyy
1563 1 2 1 9 7 1 1 2 1 152 1 1 268
2123 4 1 2 6 6 1 1 2 2 52 2 1 1168
4212 2 3 1 7 7 2 2 2 1 22 4 2 18
2913 4 4 3 9 6 1 2 1 2 252 4 2 68
9125 2 5 5 7 7 2 2 5 1 52 2 2 158
2823 1 1 4 9 5 3 3 5 1 352 1 3 148
1123 3 6 3 7 5 3 3 3 1 452 3 3 163
9187 3 2 5 6 7 3 2 3 1 212 3 3 156


The resulting library is BBB.DBF

asdf ds1 ds2 ds3 aew1 aew2 aew3 aew4 aew5 aew6 ..... XXXX sda1 sda2 sda3 sda4 yuyy1 yuyy2 yuyy3 yuyy4
The result of this line is the requirements of the following column.........

asdf (summary of column A1 in the above table is summarized here)
ds1 (here find the number of bse record 1 in the above table)
ds2 (here find the number of bse recorded as 2 in the above table)
ds3 (here find the number of bse recorded as 3 in the above table)
ds4 (here find the number of bse recorded as 4 in the above table)
aew1 (here find the number of aew recorded as 1 in the above table)
  .
  .

sda1 (here the sum of A11 records recorded as 1 in A12)
sda2 (here the sum of A11 records recorded as 2 in A12)
sda3 (here the sum of A11 records recorded as 3 in A12)
sda4 (here the sum of A11 records recorded as 4 in A12)
yuyy1 (here the sum of A14 records recorded as 1 in A13)
yuyy2 (here the sum of A14 records recorded as 2 in A13)
yuyy3 (here the sum of A14 records recorded as 3 in A13)

Trouble writing the source code according to these conditions teaches me!

szlonely

There are a few places on the upper floor that have not been corrected, and they are reposted

If I change the field name of the AAA library and not write it as A1...A10, I change it to the Chinese or alphabetic random field name, the field name in the generated query library is changed to the field name to be queried, and the query conditions are added, The records in the AAA library field are not only 1-3, there are other 4,5,6,7 in this order, how to write the source code, I changed the library to the following:
There is a table called AAA.DBF. The table has the following fields and records, please see:

abds bse aew esd erw add ghj puuu iuy ki sda hgf hiuy iuyy
1563 1 2 1 1 1 1 1 2 1 152 1 1 268
2123 4 1 2 2 2 1 1 2 2 52 2 1 1168
4212 2 3 1 5 3 2 2 2 3 22 4 2 18
2913 4 4 3 5 3 1 2 1 4 252 4 2 68
9125 2 5 6 4 2 2 2 5 4 52 2 2 158
2823 1 1 4 3 3 4 3 5 5 352 1 3 148
1123 3 6 3 2 2 4 3 3 6 452 3 3 163
9187 3 2 5 1 1 3 2 3 7 212 3 3 156
The following is the BSE-KI record range
     1-4 1-5 1-6 1-5 1-3 1-4 1-3 1-4 1-7 1-4 1-3

The resulting library is BBB.DBF

asdf bse1 bse2 bse3 bse4 aew1 aew2 aew3 aew4 aew5 aew6 .....XXXX sda1 sda2 sda3 sda4 yuyy1 yuyy2 yuyy3 yuyy4
The result of this line is the requirements of the following column.........

asdf (summary of column A1 in the above table is summarized here)
ds1 (here find the number of bse record 1 in the above table)
ds2 (here find the number of bse recorded as 2 in the above table)
ds3 (here find the number of bse recorded as 3 in the above table)
ds4 (here find the number of bse recorded as 4 in the above table)
aew1 (here find the number of aew recorded as 1 in the above table)
  .
  .

sda1 (here the sum of the records of sda items recorded as 1 in hgf)
sda2 (here the sum of the records of sda items recorded as 2 in hgf)
sda3 (here to find the sum of the sda ​​items recorded as 3 in hgf)
sda4 (here the sum of the records of sda items recorded as 4 in hgf)
yuyy1 (here the sum of the records of yuyy items in hiuy is 1)
yuyy2 (here the sum of the records of yuyy items recorded as 2 in hiuy)
yuyy3 (Here the sum of the records of yuyy items in hiuy is 3)

170385215

So you can choose the field name at will, but if you want to change the calculated column, modify the value of the corresponding I in the code

Use aaa Alias ​​MyTest
For I=2 To 13&&to obtain the total number of columns 2 and 13 (except for the 11th)
If I<>11
lcString1='Select Distinct'+Field(I,'MyTest')+' Into Cursor Dist_'+Field(I,'MyTest')+' From aaa'
&lcString1
Endif
Endfor
I=1&&to find the sum of the first column
lcString2='select sum('+Field(I,'MyTest')+') as'+Field(I,'MyTest')+'and'
for I=2 to 10&&Find the number of each number in the second column to the tenth column
Select'Dist_'+Field(I,'MyTest')
Scan
lcString2=lcString2+',sum(iif('+Field(I,'MyTest')+'='+Transform(Evaluate(Field(1)))+',1,0)) as'+Field(I,' MyTest')+Transform(Evaluate(Field(1)))
Endscan
endfor
lcString2=lcString2+' into table table1 from aaa'
&lcString2&&Generate table 1 table1
browse&&take a look

lcString3='select '
I=12&&Find the sum of the 11th column of the numbers in the 12th column
Select'Dist_'+Field(I,'MyTest')
Scan
lcString3=lcString3+'sum(iif('+Field(1)+'='+Transform(Evaluate(Field(1)))+","+Field(I-1,'MyTest')+',0)) as'+Field(I-1,'MyTest')+Transform(Evaluate(Field(1)))+','
EndScan
I=13&&Find the sum of the 14th column of the numbers in the 13th column
Select'Dist_'+Field(I,'MyTest')
Scan
lcString3=lcString3+'sum(iif('+Field(1)+'='+Transform(Evaluate(Field(1)))+","+Field(I+1,'MyTest')+',0)) as'+Field(I+1,'MyTest')+Transform(Evaluate(Field(1)))+','
EndScan
lcString3=left(lcString3,len(lcString3)-1)+' into table table2 from aaa'
&lcString3&&Generate table 2 table2
browse&&take a look

select table1.*,table2.* into table BBB from table1,table2&&to generate your result table BBB.DBF
browse&&take a look

szlonely

Thank you teacher! I learned this method.