Simulate the compute by category aggregation function for access

Recently, I used the Access database to create a micro-application. I needed to implement the effect similar to the compute by syntax in SQL Server. I Googled it online and learned that jet does not support it, so I had to use it myself. net. Currently, only one field can be used to sum another field, that is, sum (fielda) Compute by (filedb ). Note that fielda must be of the decimal type. If it is of the double type, you may need to modify it.Source code.

 

1 /**/ ///   <Summary>
2 /// Simulate the computeby statement in SQL Server
3 ///   </Summary>
4 ///   <Param name = "sourcetable"> Source data </Param>
5 ///   <Param name = "sumfield"> Sum Field </Param>
6 ///   <Param name = "byfield"> Category field </Param>
7 ///   <Param name = "needtotolsum"> Aggregate required? </Param>
8 ///   <Returns> Target data source, which takes effect after the datasource attribute of the control is rebound. </Returns>
9 Public   Static Datatable computeby (datatable sourcetable, String Sumfield, String Byfield, Bool Needtolsum)
10 {
11 Int Rowcount = Sourcetable. Rows. count;
12 If (Rowcount =   0 ) Return Sourcetable; // The data volume is 0, and the calculation Category summary is not performed.
13 Datatable desttable = Sourcetable. Copy (); // Deep Copy Data Source
14 Int Inserttimes =   0 ; // Number of inserts, used to record the relative offset between the data source and its copy
15 Int I =   0 ;
16 Int J =   0 ;
17 Decimal Sum =   0 ; // Category summary
18 Decimal Totolsum =   0 ; // Total
19 Datarow Dr;
20 While (I < Rowcount)
21 {
22 Sum + = ( Decimal ) Sourcetable. Rows [I] [sumfield];
23 J = I +   1 ;
24 If (J < Rowcount && Sourcetable. Rows [I] [byfield]. tostring () ! = Sourcetable. Rows [J] [byfield]. tostring ())
25 {
26 Dr = Desttable. newrow ();
27 Dr [sumfield] = SUM;
28 Totolsum + = SUM;
29 Sum =   0 ;
30 Desttable. Rows. insertat (DR, J + Inserttimes ); // Insert Category summary
31 Inserttimes ++ ;
32 }
33 I ++ ;
34 }
35 Dr = Desttable. newrow ();
36 Dr [sumfield] = SUM;
37 Desttable. Rows. insertat (DR, J + Inserttimes );
38 // Category summary inserted, total inserted
39 If (Needtolsum)
40 {
41 Totolsum + = SUM;
42 Dr = Desttable. newrow ();
43 Dr [sumfield] = Totolsum;
44 Desttable. Rows. Add (DR );
45 }
46 Return Desttable;
47 }