How to calculate the third column in that table

Question

0.00/5 (No votes)

See more:

i have table in access like that

i need to calculate third column to be like that

Column 1  Column 2
75        150
400       600
150       300
200       400
562.5     750
300       600
350       700

Column 1 Column 2 Column 3
75	 150	  0
400	 600   	  550
150	 300	  900
200	 400	  1250
562.5	 750	  2012.5
300	 600	  2500
350	 700	  3150

column 3 calculate like that
400+150=550
150+600+150=900
150+600+300+200=1250
150+600+300+400+562.5=2012.5

i hope some one to help me in solve this by code c# or access query
thanks

What I have tried:

I can't figure out how to solve this. I wanna calculate the sum of 2 cells

i tried this code to make table in run time but i used var sum1 to accumulated column 1 but i didn't work

C#

DataTable workTable = new DataTable("Customers");
DataColumn workCol = workTable.Columns.Add("id", typeof(Int32));
workCol.AllowDBNull = true;
workCol.Unique = false;

DataRow workRow;
workTable.Columns.Add("Column 1", typeof(Double));
workTable.Columns.Add("Column 2", typeof(Double));
workRow = workTable.NewRow();

workRow[0] = Convert.ToInt32(dr[7].ToString());
workRow[1] = Convert.ToDouble(Column 1.ToString());
workRow[2] = Convert.ToDouble(Column 2.ToString());

workTable.Rows.Add(workRow);
int sum1 = 0;
int sum2 = 0;
foreach (DataRow dr1 in workRow.Table.Rows)
{

sum1 += Convert.ToInt32(dr1["Column 1"]);
sum2 += Convert.ToInt32(dr1["Column 2"]);

sum1.ToString();
sum2.ToString();
}

Posted 7-Apr-18 5:42am

michael nabil

Updated 7-Apr-18 11:20am

PIEBALDconsult

v4

Add a Solution

Comments

Richard MacCutchan 7-Apr-18 11:47am

You seem to be making random selections between column1 and column2. And you are certainly using more than 2 cells.

PIEBALDconsult 7-Apr-18 12:33pm

I could do that in SQL Server, not sure about Access.
One thing to bear in mind about databases is that the records don't truly have an innate order -- you need to provide something to give them order.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Answer 1 · 2018-04-07T07:26:00

Here's how I would do it with a Recursive Common Table Expression in SQL Server:

SQL

DROP TABLE #data 
;
CREATE TABLE #data 
( [ID]       INTEGER NULL 
, [Column 1] REAL    NULL
, [Column 2] REAL    NULL
)
;
INSERT INTO #data VALUES
  ( 1 , 75    , 150 )
, ( 2 , 400   , 600 )
, ( 3 , 150   , 300 )
, ( 4 , 200   , 400 )
, ( 5 , 562.5 , 750 )
, ( 6 , 300   , 600 )
, ( 7 , 350   , 700 )
;
WITH [work] AS
(
  SELECT * , CAST ( 0.0 AS REAL ) [Column 3] , [Column 2] [Sum 2] FROM [#data] WHERE [ID] = 1
UNION ALL
  SELECT A.[ID] , A.[Column 1] , A.[Column 2] , A.[Column 1] + B.[Sum 2] [Column 3] , A.[Column 2] + B.[Sum 2] [Sum 2]
  FROM [#data] A
  CROSS APPLY [work] B
  WHERE B.[ID] = A.[ID] - 1
)
SELECT [ID]
, [Column 1]
, [Column 2]
, [Column 3]
-- , [Sum 2]
FROM [work]

But you must have something by which you can order the records.

Wendelius · Answer 2 · 2018-04-07T07:45:00

As far as I know you cannot use Common Table Expression with Access. So the only option seems to be to fetch the data from the database to client side and do the calculation over there.

One quite easy way would be to fetch the data for example:
- Fetch the data into a data table
- Order the data based on your requirements to ensure that the rows are calculated in correct order
- During each iteration use a variable to store the sum from previous rows update the target column based on current data and the sum from previous rows

ADDITION:

Consider the following example for running total calculation

C#

public static void RunningTotalExample() {
   System.Data.DataTable dataTable;
   System.Data.DataRow row;
   System.Data.DataView sortedView;
   int runningValue;

   // Cretae test table
   dataTable = new System.Data.DataTable("SampleTable");
   dataTable.Columns.Add("Ordinal", typeof(int));
   dataTable.Columns.Add("Amount", typeof(int));

   // Add some data in random order

   row = dataTable.NewRow();
   row["Ordinal"] = 2;
   row["Amount"] = 3;
   dataTable.Rows.Add(row);

   row = dataTable.NewRow();
   row["Ordinal"] = 1;
   row["Amount"] = 5;
   dataTable.Rows.Add(row);

   row = dataTable.NewRow();
   row["Ordinal"] = 3;
   row["Amount"] = 12;
   dataTable.Rows.Add(row);

   // Add a column for running total
   dataTable.Columns.Add("RunningTotal", typeof(int));

   // Sort the data and calculate the running total
   runningValue = 0;
   sortedView = new System.Data.DataView(dataTable);
   sortedView.Sort = "Ordinal ASC";
   foreach (System.Data.DataRowView singleRow in sortedView) {
      runningValue += (int)singleRow["Amount"];
      singleRow["RunningTotal"] = runningValue;
   }
   dataTable.AcceptChanges();

   // Print the values
   foreach (System.Data.DataRow singleRow in dataTable.Rows) {
      System.Diagnostics.Debug.WriteLine($"{singleRow["Ordinal"]} {singleRow["Amount"]} {singleRow["RunningTotal"]}");
   }
}

The output would be (ordinal, amount, running total)

2 3 8
1 5 5
3 12 20

How to calculate the third column in that table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0