Query, modify, and delete access SQL statements

Generate serial number during Query
Select (select count ([xlh]. [AA]) as autonum from xlh where (xlh. aa) <= xlh_alias.aa);) as sequence number, xlh. AA
From xlh as xlh_alias inner join xlh on xlh_alias.aa = xlh. AA
Order by xlh. AA;

Multi-Table SQL query
Select Test. AA as the first field, test1.bb as the second field, test1.cc
From test, test1
Where test. AA = test1.aa;

Multi-Table SQL query 1
Select a. AA, B. BB, B. CC, B. CC * 100 as total
From test as a, test1 as B
Where a. AA = B. AA;

Multi-Table SQL query sorting
Select a. AA, B. BB, B. cc as Third Field
From test as a, test1 as B
Where a. AA = B. AA
Order by B. CC;

Query example
Select a. dhhm
From xl11a as A, xl919 as B
Where a. dhhm = B. dhhm and AA <> "1 ";

The date and time Delimiter is # Instead of quotation marks
Select * From tab1 where [date]> #2002-1-1 #;

Associate two tables to modify Multiple Fields
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 );

If tab2 is not a table, but a query
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 Multiple different access databases-use the in clause in SQL statements. external databases cannot contain passwords.
Select a. *, B. * From tab1 A, tab2 B in 'db2. mdb 'Where a. ID = B. ID;

Leave a field in a table empty.
Update chhl as a set a. fzr = NULL, A. GH = NULL;

Delete records with the same fields in two tables
Delete from xl11 where dhhm in (select a. dhhm
From xl11 as A, xl919 as B
Where a. dhhm = B. dhhm ;);

After the access is complete, the field name index attribute is displayed as -- yes (no duplicates)
Create unique index INAME on tab1 (name );

The following statement deletes the two indexes just created
Drop index idate on tab1;
Drop index INAME on tab1;