发几条常用的维护用sql语句

chenchenjie

812Sql总帐清除期初语句
UPDATE GL_accass
SET mb = 0, md = 0, mc = 0 ,mb_f=0,md_f=0,mc_f=0,me_f=0,cendd_c='ƽ',cbegind_c='ƽ'
WHERE (ccode LIKE '102%')

delete from gl_accvouch
WHERE (ccode LIKE '102%')

UPDATE GL_accsum
SET mb = 0, md = 0, mc = 0, mb_f = 0, md_f = 0, mc_f = 0, me_f = 0, cendd_c = 'ƽ',
      cbegind_c = 'ƽ'
WHERE (ccode LIKE '102%')

Accsum空值语句
SELECT * into gl_accsum1
FROM GL_accsum where not isnull(iperiod);


更新存货明细帐负数问题的SQL语句
UPDATE IA_Subsidiary
SET iAInQuantity = ABS(iAInQuantity)
WHERE cvoutype = 30
UPDATE IA_Subsidiary
SET iInCost = ABS(iInCost)
WHERE cvoutype = 30

解决Sql总帐与明细帐不平的语句
update gl_accass set me=mb where md=0 and mc=0

解决应收明细帐翻倍的语句
Option Compare Database
Option Explicit

Sub main()
    Dim Db As Database
    Dim Rec As Recordset
    Dim Sql As String
    Dim str As Long
    Dim str1 As String
    Set Db = OpenDatabase("c:\ufsoft80\zt400\2000\ufdata.mdb")
    Set Rec = Db.OpenRecordset("select * from ap_detail order by ibvid,cVouchType")
    While Not Rec.EOF
          str = Rec("iBVid")
          str1 = Rec("cVouchType")
          Rec.MoveNext
          If Not Rec.EOF Then
             If Rec("iBVid") = str And Rec("cVouchType") = str1 Then
                Rec.Delete
                Rec.MoveNext
             End If
          End If
    Wend
    MsgBox "OK!"
End Sub


连接mdf文件
sp_detach_db 'ufdata_436_2000'

sp_attach_db @dbname='ufdata_436_2000','f:\数据\珠海光盘\2T003\2000\UFDATA.MDF','f:\数据\珠海光盘\2T003\2000\UFDATA.lDF'

应收付翻倍语句
update gl_accvouch set coutno_id=null where doutbilldate <#2000-1-1# and coutno_id like 'A*'

该贴已经同步到 chenchenjie的微博

lanyu424

学习了 呵呵