INSERT INTO tbl_act_feeX ( c_type, c_act_code, u_fee ) SELECT tbl_act_fee.c_type, tbl_act_fee.c_act_code, tbl_act_fee.u_fee FROM tbl_act_fee WHERE (((tbl_act_fee.c_type)="CSSA"))
DELETE tbl_act_fee.c_type FROM tbl_act_fee WHERE (((tbl_act_fee.c_type)="CSSA"))
INSERT INTO tbl_act_fee SELECT tbl_act_feeX.* FROM tbl_act_feeX
UPDATE tbl_mem set d_expired_1 = #31/3/2021# WHERE ((tbl_mem.d_expired_1)=#31/3/2020#)
DELETE FROM tbl_dropIn WHERE  (tbl_dropIn.d_datetime) Is Null

0.Insert tbl_account from tbl_account_detail  NO DATA
INSERT INTO tbl_account ( c_receipt_no, c_type, c_desc, u_price_after_discount, u_amt1, u_amt2, c_user_id, c_cash_type, m_remark )
SELECT tbl_account_detail.c_receipt_no, tbl_account_detail.c_type, tbl_account_detail.c_type_desc, tbl_account_detail.u_unit, tbl_account_detail.u_unit, 0 AS Expr1, "wing" AS Expr2, "Cash" AS Expr3, "XXX" AS Expr4
FROM tbl_account_detail LEFT JOIN tbl_account ON tbl_account_detail.c_receipt_no = tbl_account.c_receipt_no
WHERE (((tbl_account.c_receipt_no) Is Null));

UPDATE tbl_account INNER JOIN tbl_act_reg ON tbl_account.c_receipt_no = tbl_act_reg.c_receipt_no SET tbl_account.c_act_code = tbl_act_reg.c_act_code, tbl_account.c_mem_id = tbl_act_reg.c_mem_id, tbl_account.c_name = tbl_act_reg.c_name, tbl_account.d_create = tbl_act_reg.d_reg, tbl_account.i_receipt_type = 2
WHERE (((tbl_account.m_remark)="XXX"));

UPDATE tbl_account INNER JOIN tbl_membership_records ON tbl_account.c_receipt_no = tbl_membership_records.c_receipt_no SET tbl_account.d_create = tbl_membership_records.d_datetime , tbl_account.c_mem_id = tbl_membership_records.c_mem_id, tbl_account.c_name =  tbl_membership_records.c_name, tbl_account.i_receipt_type = 1
WHERE (((tbl_account.m_remark)="XXX"));

UPDATE tbl_account INNER JOIN tbl_canteen ON tbl_account.c_receipt_no = tbl_canteen.c_receipt_no SET tbl_account.d_create = tbl_canteen.d_receive, tbl_account.i_receipt_type = 3, tbl_account.c_mem_id =  tbl_canteen.c_mem_id , tbl_account.c_name = tbl_canteen.c_name 
WHERE (((tbl_account.m_remark)="XXX"));

?H?a¡±i i_receipt_type   b_cleaar  d_cleaar

1.update d_create+1
UPDATE tbl_account SET d_create = d_create+1 WHERE c_receipt_no>="PI0042472" And c_receipt_no<="PI0042473"

2.insert no account_detail
INSERT INTO tbl_account_detail ( c_receipt_no, c_type, c_type_desc, i_qty, u_unit )
SELECT tbl_account.c_receipt_no, tbl_account.c_type, tbl_account.c_desc, 1 AS Expr1, tbl_account.u_price_after_discount
FROM tbl_account_detail RIGHT JOIN tbl_account ON tbl_account_detail.c_receipt_no = tbl_account.c_receipt_no
WHERE (((tbl_account_detail.c_receipt_no) Is Null));

3.insert no act_reg 
INSERT INTO tbl_act_reg ( c_receipt_no, c_act_code, c_mem_id, c_type, d_reg, c_user_id, c_name, c_tel )
SELECT tbl_account.c_receipt_no, tbl_account.c_act_code, tbl_account.c_mem_id, tbl_account.c_type, tbl_account.d_create, tbl_account.c_user_id, tbl_account.c_name, tbl_mem.c_tel
FROM (tbl_account LEFT JOIN tbl_act_reg ON tbl_account.c_receipt_no = tbl_act_reg.c_receipt_no) INNER JOIN tbl_mem ON tbl_account.c_mem_id = tbl_mem.c_mem_id
WHERE (((tbl_account.c_act_code)<>"") AND ((tbl_account.c_type)<>"?!¢XE¢Xh¡¦U") AND ((tbl_account.d_create)>=#1/1/2017#) AND ((tbl_act_reg.c_receipt_no) Is Null));

4. delete
DELETE tbl_dropIn.d_datetime FROM tbl_dropIn WHERE ((tbl_dropIn.d_datetime)<#4/1/2016#)
DELETE tbl_mem.d_expired_1 FROM tbl_mem WHERE ((tbl_mem.d_expired_1)<#4/1/2016#)
DELETE tbl_act_reg.d_reg FROM tbl_act_reg WHERE ((tbl_act_reg.d_reg)<#4/1/2016#)
DELETE tbl_act.d_date_from FROM tbl_act WHERE ((tbl_act.d_date_from)<#4/1/2016#)
DELETE d_create FROM tbl_account WHERE ((d_create)<#4/1/2016#)

5. c_cash_type = "Cash"
UPDATE tbl_account SET tbl_account.c_cash_type = "Cash"
WHERE (((tbl_account.c_cash_type) Is Null Or (tbl_account.c_cash_type)=""))

6. output act_reg member record
SELECT tbl_mem.c_mem_id, tbl_mem.c_HKID, tbl_mem.c_name, tbl_mem.c_name_other, tbl_mem.c_group, tbl_mem.c_sex, tbl_mem.d_birth, tbl_mem.c_tel, tbl_mem.c_mobile, tbl_mem.m_res_stat, tbl_mem.c_res_nat, tbl_mem.c_res_class, tbl_act_reg.c_act_code
FROM tbl_mem INNER JOIN tbl_act_reg ON tbl_mem.c_mem_id = tbl_act_reg.c_mem_id
WHERE (((tbl_act_reg.c_act_code)="P-1M-1/18"));


UPDATE tbl_account SET tbl_account.c_desc =   tbl_account.m_remark 
WHERE (((tbl_account.c_desc) Is Null Or (tbl_account.c_desc)=""))

UPDATE tbl_account_detail SET tbl_account_detail.c_type_desc =   tbl_account_detail.c_type 
WHERE (((tbl_account_detail.c_type_desc) Is Null Or (tbl_account_detail.c_type_desc)=""))

UPDATE tbl_account SET tbl_account.u_amt1 =  tbl_account.u_price_after_discount