VBA vs Query: Discrepancy in Result Count

I am implementing a system to convert an existing database to a more efficient layout, as discussed in this question. Applying the union/maketable solution given to that question to a sample database appears at first to work correctly. However, upon attempting to check the number of entries that the new table should have using a VBA function I find that the two methods result in different values, with the query outputting less rows than expected per the VBA test.

VBA to count entries:

Function countStreets()
Dim rst As DAO.Recordset
Dim count As Integer
count = 0
Set rst = CurrentDb.OpenRecordset("Map index")
rst.MoveFirst
While (Not rst.EOF)
    With rst
        If (Not IsNull(!Street)) Then count = count + 1
        If (Not IsNull(!Street_2)) Then count = count + 1
        If (Not IsNull(!Street_3)) Then count = count + 1
        If (Not IsNull(!Street_4)) Then count = count + 1
        If (Not IsNull(!Street_5)) Then count = count + 1
        If (Not IsNull(!Street_6)) Then count = count + 1
        If (Not IsNull(!Street_7)) Then count = count + 1
        If (Not IsNull(!Street_8)) Then count = count + 1
        If (Not IsNull(!Street_9)) Then count = count + 1
        If (Not IsNull(!Street_10)) Then count = count + 1
        If (Not IsNull(!Street_11)) Then count = count + 1
        If (Not IsNull(!Street_12)) Then count = count + 1
        .MoveNext
    End With
Wend
MsgBox (count)
rst.Close
Set rst = Nothing
End Function

Union query:

SELECT fileID, Street FROM [Map index] WHERE Street IS NOT NULL;
UNION
SELECT fileID, Street_2 FROM [Map index] WHERE Street_2 IS NOT NULL;
UNION
SELECT fileID, Street_3 FROM [Map index] WHERE Street_3 IS NOT NULL;
UNION
SELECT fileID, Street_4 FROM [Map index] WHERE Street_4 IS NOT NULL;
UNION
SELECT fileID, Street_5 FROM [Map index] WHERE Street_5 IS NOT NULL;
UNION
SELECT fileID, Street_6 FROM [Map index] WHERE Street_6 IS NOT NULL;
UNION
SELECT fileID, Street_7 FROM [Map index] WHERE Street_7 IS NOT NULL;
UNION
SELECT fileID, Street_8 FROM [Map index] WHERE Street_8 IS NOT NULL;
UNION
SELECT fileID, Street_9 FROM [Map index] WHERE Street_9 IS NOT NULL;
UNION
SELECT fileID, Street_10 FROM [Map index] WHERE Street_10 IS NOT NULL;
UNION
SELECT fileID, Street_11 FROM [Map index] WHERE Street_11 IS NOT NULL;
UNION
SELECT fileID, Street_12 FROM [Map index] WHERE Street_12 IS NOT NULL;

Maketable query:

SELECT * INTO [fileID-Street]
FROM MergeStreetsUnionQry;

What could be causing the discrepancy in result counts between these two methods? The difference is small enough (here 28227 from query vs. 28260 from counter) that I’m certain it is not the result of one method failing completely, but large enough to appear significant.

Answer

UNION removes duplicates, so this would suggest that you have duplicate values in the Street* columns for one or more file ids.

Change the UNION to UNION ALL.

Advertisement

Answer