思路如此，方法自我感觉很笨，希望高手有更好的方法。作者: Henry D. Sy 时间: 2012-4-26 10:05
每个记录能不能保证只有一个字段有值？其他字段均为空？作者: todaynew 时间: 2012-4-26 11:37
很简单嘛。
1、在aa表中建立一个自动编号的ID字段。
2、建立三个查询：
a查询：
SELECT aa.ID, aa.a, DCount("*","aa","ID<=" & [ID] & " and a is not null") AS num
FROM aa
WHERE aa.a Is Not Null
b查询：
SELECT aa.ID, aa.b, DCount("*","aa","ID<=" & [ID] & " and b is not null") AS num
FROM aa
WHERE aa.b Is Not Null
c查询：
SELECT aa.ID, aa.c, DCount("*","aa","ID<=" & [ID] & " and c is not null") AS num
FROM aa
WHERE aa.c Is Not Null
3、将三个查询用num字段联接起来写一个查询：
SELECT a查询.num, a查询.a, b查询.b, c查询.c
FROM (a查询 LEFT JOIN b查询 ON a查询.num = b查询.num) LEFT JOIN c查询 ON a查询.num = c查询.num作者: 咱家是猫 时间: 2012-4-28 13:21
查询有难度.不如就建个bb表吧.用代码解决
Private Sub Command0_Click()
Dim myArray()
Dim rs As New ADODB.Recordset
Dim ia As Integer
Dim ib As Integer
Dim ic As Integer
Dim strSQL As String
rs.Open "aa", CurrentProject.Connection, 3, 1
If rs.RecordCount > 0 Then
ReDim myArray(rs.RecordCount, 1 To 3)
Do While rs.EOF = False
If Nz(rs("a")) <> "" Then
myArray(ia, 1) = rs("a")
ia = ia + 1
End If
If Nz(rs("b")) <> "" Then
myArray(ib, 2) = rs("b")
ib = ib + 1
End If
If Nz(rs("c")) <> "" Then
myArray(ic, 3) = rs("c")
ic = ic + 1
End If
rs.MoveNext
Loop
CurrentDb.Execute "Delete * From bb"
For i = 0 To rs.RecordCount
If myArray(i, 1) <> "" Or myArray(i, 2) <> "" Or myArray(i, 3) <> "" Then
strSQL = "INSERT INTO bb ( a, b, c ) Values ('" & myArray(i, 1) & "','" & myArray(i, 2) & "','" & myArray(i, 3) & "')"
CurrentDb.Execute strSQL
End If
Next i
End If
rs.Close
Set rs = Nothing
DoCmd.OpenTable "bb"
End Sub 作者: Henry D. Sy 时间: 2012-4-29 11:53
猫猫好！作者: xdcow 时间: 2012-5-5 14:35
咱家是猫 发表于 2012-4-28 13:21
查询有难度.不如就建个bb表吧.用代码解决
Private Sub Command0_Click()
