Simulate the compute by category aggregation function for access

Source: Internet
Author: User

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 }

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.