access sql语句查询、修改、删除

 

查询时生成序号
SELECT (SELECT Count([xlh].[aa]) AS AutoNum FROM xlh WHERE  (((xlh.aa)<=xlh_Alias.aa));) AS 序号, xlh.aa
FROM xlh AS xlh_Alias INNER JOIN xlh ON xlh_Alias.aa=xlh.aa
ORDER BY xlh.aa;

多表sql查询
SELECT test.aa AS 第一个字段, test1.bb AS 第二个字段, test1.cc
FROM test, test1
WHERE test.aa=test1.aa;

多表sql查询1
SELECT a.aa, b.bb, b.cc, b.cc*100 AS 合计
FROM test AS a, test1 AS b
WHERE a.aa=b.aa;

多表sql查询排序
SELECT a.aa, b.bb, b.cc AS 第三个字段
FROM test AS a, test1 AS b
WHERE a.aa=b.aa
ORDER BY b.cc;

查询例子
SELECT a.dhhm
FROM xl11a AS a, xl919 AS b
WHERE a.dhhm=b.dhhm and aa<>"1";

日期时间分隔符是#而不是引号
Select * From Tab1 Where [Date]>#2002-1-1#; 

两个表关联修改多个字段
UPDATE chhl AS a, jbsj AS b SET a.fzr = b.fzr, a.gh = b.gh
WHERE a.dhhm=b.dhhm;

update  chhl set (fzr,gh)=
(SELECT b.fzr, b.gh
FROM chhl AS a, jbsj AS b
WHERE a.dhhm=b.dhhm);

如果Tab2可以不是一个表,而是一个查询
UPDATE Tab1 a,(Select ID,Name From Tab2) b 
SET a.Name = b.Name 
WHERE a.ID = b.ID;

UPDATE Tab1 a,Tab2 b 
SET a.Name = b.Name 
WHERE a.ID = b.ID;

访问多个不同的ACCESS数据库-在SQL中使用In子句,外部数据库不能带密码
Select a.*,b.* From Tab1 a,Tab2 b In 'db2.mdb' Where a.ID=b.ID;

将一个表的某个字段置空
UPDATE chhl AS a SET a.fzr = null, a.gh = null;

删除两个表中字段一样的记录
delete from xl11 where dhhm in(SELECT a.dhhm
FROM xl11 AS a, xl919 AS b
WHERE a.dhhm=b.dhhm;);

完成后ACCESS中字段Name索引属性显示为--有(无重复)
Create Unique Index iName ON Tab1 (Name);

下面的语句删除刚才建立的两个索引 
Drop Index iDate ON Tab1; 
Drop Index iName ON Tab1;