sum dynamic table column in ms access with vb.net

[rony_78]

Can somene help me with this I'm having difficulty figuring out how to sum column values in dynamic tables. I am using create table statement passing text box input to the query with a column name 'total'. Using another insert query i am saving tables name in another table called table_list. I am looking for a way to get the sum of column 'total' of all created tables .

[davej]

Well, you can use a SQL query to generate a sum, but if you are displaying the table you can also totalize the columns as you are looping and building the HTML table.

[rony_78]

can you show me any sample code or query . I have tried to query the system tables such as

MSysAccessObjects

MSysAccessXML

MSysACEs

MSysObjects

MSysQueries

public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }               private void button2_Click(object sender, EventArgs e)        {                }         private static int getTotal(string connectionString)        {            int total = 0;            using (var connection = new OleDbConnection(""))            {                connection.Open();                var command = new OleDbCommand("SELECT TABLE_NAME FROM MSysAccessObjects.COLUMNS WHERE COLUMN_NAME = 'Total';"                    , connection);                foreach (var table in from IDataRecord row in command.ExecuteReader()                                      select new { name = row.GetValue(row.GetOrdinal("TABLE_NAME")) }                    )                {                    command.CommandText = String.Format("SELECT SUM(Total) AS Total FROM {0}", table.name);                    var tableTotal = command.ExecuteScalar();                    total = total + (tableTotal == DBNull.Value ? 0 : (int)command.ExecuteScalar());                }                connection.Close();            }            return total;        }

I have found a function similer to my problem but I dont know if it will work.

stackoverflow.com/.../how-sum-values-in-dynamic-table-columns-

function sumQty(tableID) {    var total = 0;    var table = document.getElementById(tableID);    var rowCount = table.rows.length;    for (var i = 0; i < rowCount; i++) {        var row = table.rows[i];        var colCount = row.cells.length;        for (var j = 0; j < cellCount; j++) {            var node = row.cells[j].childNodes[0];            if (node.name == "qty[]") {                total += parseInt(node.value);            }        }    }    return total;}

I also tried the the query as a single statement

 CmdStr1 = "SELECT 'SELECT ISNULL(SUM('+COLUMN_NAME + '),0) AS Tot FROM ' + TABLE_NAME FROM MSysObjects .COLUMNS WHERE COLUMN_NAME = 'Total'"

 

[davej]

First of all, do you have a query that is working successfully?

[rony_78]

I could not get any of them to work. I trying to build SQL statement at run time without hard coding table names.

[davej]

Have you looked at available examples such as...

 

http://support.microsoft.com/kb/821765