求助delphi7,Access分类统计问题

你这种情况, 新建一个查询即可(access的查询类似mssql的视图).
--------------------------------
表1数据变化, 查询表数据随动

update + inner join

Quote: 引用楼主 TiaoWuDeWuShi 的回复:

本帖最后由 TiaoWuDeWuShi 于 2013-02-04 08:37:51 编辑
初学实在搞不出来，求源码。Delphi,Access,ADOQuery 表1是明细表，表2是汇总表，怎能将表1的统计数填入表2中，类似表2(结果) 表1，表2都存在，不用创建新表。
表1 ……
表1 、表2分别位于2个库中，做跨库查询

晕你早说是两个库啊 :)
----------------------------
第一个adoquery1, 查询数据 select ItemNo, count(1) as Amount form 表1;
然后遍历adoquery1, 用adoquery2执行语句
sql := format('update 表2 set [all] = %d where ItemNo = %d', [adoquery1.fieldbyname('Amount').asinteger, adoquery1.fieldbyname('ItemNo').asinteger]);

select itemno, count(itemno) into Table2 from Table1 group by itemno

实在不好意思，能给出具体的代码吗？
program Project1;uses
  Forms,
  Unit1 in 'Unit1.pas' {Form1},
  Unit2 in 'Unit2.pas' {DataModule2: TDataModule};{$R *.res}begin
  Application.Initialize;
  Application.CreateForm(TForm1, Form1);
  Application.CreateForm(TDataModule2, DataModule2);
  Application.Run;
end.
{----------}
unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, StdCtrls;type
  TForm1 = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    Button1: TButton;
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;implementationuses Unit2;{$R *.dfm}end.
{----------}
unit Unit2;interfaceuses
  SysUtils, Classes, DB, ADODB;
const
  ADOString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;' +
    ';Persist Security Info=False';
type
  TDataModule2 = class(TDataModule)
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    procedure DataModuleCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  DataModule2: TDataModule2;implementation{$R *.dfm}procedure TDataModule2.DataModuleCreate(Sender: TObject);
begin
  with ADOQuery1 do
  begin
    Close;
    ConnectionString := Format(AdoString, [GetCurrentDir + '\db1.mdb']);
    Active := True;
  end;  with ADOQuery2 do
  begin
    Close;
    ConnectionString := Format(AdoString, [GetCurrentDir + '\db2.mdb']);
    Active := True;
  end;
end;
end.
{----------}
object Form1: TForm1
  Left = 339
  Top = 280
  Width = 783
  Height = 540
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 16
    Top = 16
    Width = 70
    Height = 13
    Caption = #34920'1-'#26126#32454#34920#65306
  end
  object Label2: TLabel
    Left = 12
    Top = 320
    Width = 70
    Height = 13
    Caption = #34920'2-'#27719#24635#34920#65306
  end
  object DBGrid1: TDBGrid
    Left = 16
    Top = 32
    Width = 409
    Height = 273
    DataSource = DataModule2.DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object DBGrid2: TDBGrid
    Left = 16
    Top = 344
    Width = 465
    Height = 153
    DataSource = DataModule2.DataSource2
    TabOrder = 2
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object Button1: TButton
    Left = 520
    Top = 112
    Width = 75
    Height = 25
    Caption = #20998#31867#27719#24635
    TabOrder = 1
  end
end
{----------}
object DataModule2: TDataModule2
  OldCreateOrder = False
  OnCreate = DataModuleCreate
  Left = 555
  Top = 180
  Height = 238
  Width = 215
  object ADOQuery1: TADOQuery
    CursorType = ctStatic
    Parameters = <>
    SQL.Strings = (
      'select * from '#34920'1')
    Left = 24
    Top = 24
  end
  object ADOQuery2: TADOQuery
    CursorType = ctStatic
    Parameters = <>
    SQL.Strings = (
      'select * from '#34920'2')
    Left = 104
    Top = 24
  end
  object DataSource1: TDataSource
    DataSet = ADOQuery1
    Left = 24
    Top = 96
  end
  object DataSource2: TDataSource
    DataSet = ADOQuery2
    Left = 112
    Top = 96
  end
end
{----------}

下边是读取表1, 更新表2的代码, 你放到相应的事件中, 简单调试应该就可以使用.
with ADOQuery1 do
  begin
    //初始化ADOQuery1, 装载数据
    Close;
    ConnectionString := Format(AdoString, [GetCurrentDir + '\db1.mdb']);
    SQL.Text := 'select ItemNo, count(1) as Amount form 表1';
    Open;
    First;    //初始化ADOQuery2
    ADOQuery2.ConnectionString := Format(AdoString, [GetCurrentDir + '\db2.mdb']);    //遍历ADOQuery1, 使用qry2执行更新语句
    while not Eof do
    begin
      ADOQuery2.SQL.Text := format('update 表2 set [all] = %d where ItemNo = %d', [fieldbyname('Amount').asinteger, fieldbyname('ItemNo').asinteger]);
      ADOQuery2.ExecSQL;
      Next;
    end;
  end;

谢谢！with ADOQuery1 do
  begin
    //初始化ADOQuery1, 装载数据
    Close;
    ConnectionString := Format(AdoString, [GetCurrentDir + '\db1.mdb']);
    SQL.Text := 'select ItemNo, count(1) as Amount form 表1';
    Open;
    First;
执行到这里提示：“SELECT 子句中包含一个保留字、拼写错误或丢失的参数，或标点符号不正确。”

'select ItemNo, count(1) as Amount form 表1';
form错了from
运行时错误提示：试图执行的查询中不包含作为合计函数一部分的特定表达式'ItemNo'

哈哈不好意思, 少写了分组语句
用下边这句, 应该就可以了------------------------------------
SQL.Text := 'select ItemNo, count(1) as Amount form 表1 group by ItemNo';

ADOQuery2.SQL.Text := format('update 表2 set [all] = %d where ItemNo = %d', [fieldbyname('Amount').asinteger, fieldbyname('ItemNo').asinteger]);
报错： 'Format '%d' invalid or incompatible with argument'

ADOQuery2.SQL.Text := format('update 表2 set [all]= %d where ItemNo= %s' ,
[fieldbyname('Amount').asinteger, fieldbyname('ItemNo').AsString]);ItemNo为字符型提示"标准表达式中数据类型不匹配"

谢谢simonhehe，问题解决了。
ADOQuery2.SQL.Text := format('update 表2 set [all]= %d where ItemNo= %s' ,[fieldbyname('Amount').asinteger, quotedstr(fieldbyname('ItemNo').AsString)]);

调试易

求助delphi7,Access分类统计问题

解决方案 »