比较访问中的两个表值 [英] comparring two table values in access

问题描述

朋友们，
我在C#.net Windows应用程序中将MS-Access用作db
我有两个表作为temp&计数.
temp 表列是
ItemCode作为数字，
ItemName作为文本，
数量，

计数表列是
ItemCode作为数字，
ItemName作为文本，
算作数字，


在临时表中的值是

Hi friends,
I am using MS-Access as db in C# .net Windows Application
I have Two Table as temp & Count.
temp table Column are
ItemCode as Number,
ItemName as text,
Qty as number,

Count table column are
ItemCode as Number,
ItemName as text,
Count as number,


In temp table values are

ItemCode            ItemName             Qty
  5                    fish                2
  2                    rice                2
  3                    chicken             3
  1                    bone less fish      3
  5                    fish                4
  2                    rice                3
  3                    chicken             4
  1                    bone less fish      6

计数表值

Count Table Values

ItemCode            ItemName             Count
  1                    bone less fish      0  
  2                    rice                0
  3                    chicken             0
  5                    fish                0

我想将Count表更新为

I want to Update the Count table as

ItemCode            ItemName             Count
  1                    bone less fish      9  
  2                    rice                5
  3                    chicken             7
  5                    fish                6

我如何更新这样的计数表请帮助我


谢谢问候
Dineshkumar R

How can i update the count table like this Pleas Help me


Thanks With Regards
Dineshkumar R

推荐答案

我认为您已经使用外键概念来链接表...

然后只需传递查询..

使用选择查询

在while(reader.read())
中 {

写您的更新查询以增加计数
}

i think you have used foreign key concept to link the tables ...

then just pass the query ..

use select query

and in while(reader.read())
{

write your update query to increment the count
}

步骤1:使用查询读取临时表:

Step 1: Read in temporary table with query:

SELECT ItemCode, SUM(Qty) as Cnt FROM temp GROUP BY temp.ItemCode

步骤2:从该查询读取值时

Step 2: While reading values from that query

while(reader.read())
{
    //Here build your update query on a new OLEDBCommand
    string sqlString="UPDATE Count1 SET Cnt=? Where ItemCode=?"
    //Build your update command
    myCommand.Parameters.Add(@"Count",/*Put your Item Count which is read from reader*/);
    myCommand.Parameters.Add(@"ItemCode",/*Put your Item Code*/);
    // Other stuff if you want to
    myCommand.ExecuteNonQuery();
}

我希望这可以清除.

[Edit]以下是针对SQL Server的消息，很抱歉，我错过了"Access"标记.

I hope this clears it.

Below is for SQL Server Sorry about that I missed "Access" tag.

UPDATE [Count] set [Count].[Count] = tmp.cnt FROM (SELECT ItemCode, SUM(Qty) as cnt FROM temp GROUP BY temp.ItemCode) as tmp WHERE [Count].ItemCode=tmp.ItemCode

建议:不要在数据库中提供具有其他含义的表/列名称，例如"Count".

Advice: Do not give Table/Column names which has other meanings in the database like "Count".

这篇关于比较访问中的两个表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！