How to fill in blanks in staggered index?

[FatherNature]

I have a tricky problem that I am trying to work around. I have a database that needs to create a datafile in a specific format. The keys are in a staggered array, and I need the query to generate the data with the gaps filled in, a simplified example follows:

ID DATA
500 12
501 20
502 100
505 12
506 16
508 30
...
​

after running the query this would become:

ID DATA
500 12
501 20
502 100
503 null
504 null
505 12
506 16
507 null
508 30
...
​

I would like to generate the full sequence for the range of keys in visual basic and then tie my data to this generated field, but since I'm new to database programming I'm open to other suggestions. If I can get this to work properly it will dramatically simplify an existing (broken) query (4 pages of SQL in 11 point font would become less than a single page and would clear up any other ommisions that were overlooked).

The database is a beast, to add in the twelve datapoints (I need to add) using the existing method would be hideous and may have unintended consequences with other database queries.

Any help you can give is greatly appreciated,
David

 

[vbaInet]

Welcome to AWF Mother Nature's husband

Is this going to be used for a report?

If you don't want to INSERT into that table you could create another table (not good practice with regards normalization) and INSERT the new IDs into this new table. Create a UNION between the new table and your current table.

 

[FatherNature]

Welcome to AWF Mother Nature's husband

Is this going to be used for a report?

If you don't want to INSERT into that table you could create another table (not good practice with regards normalization) and INSERT the new IDs into this new table. Create a UNION between the new table and your current table.

It's not a report, it's a dump to a binary file. That part is working (fortunately).

I guess what I was hoping to use a VB query for is to create a table on the fly to UNION to. This would give me the flexibility to reuse the fix for other broken tables, improve the speed (currently it uses other derived queries to get at data - this fix would avoid the need to do all that), and would avoid creating an empty table with a few thousand values in it just for this query.

Is there any way to do that?
David

 

[dkinley]

Here is something, although it is not a query. This method actually creates those records in the tables in question.

In a module, you can create a new function using the following code:

Public Sub FillIndexGaps()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
 
    Dim iIndexMin As Long
    Dim iIndexMax As Long
    Dim iIndexGap As Long
    Dim iNewIndex As Long
    Dim iOldIndex As Long
 
    sSQL = "SELECT IndexNum, DataStuff " & _
                "FROM tTest " & _
                "ORDER BY IndexNum;"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sSQL)
 
    With rst
        'assing last and first index numbers
        .MoveLast
        iIndexMax = !IndexNum.Value
        .MoveFirst
        iIndexMin = !IndexNum.Value
 
        'set initial min value of index gap
        iOldIndex = iIndexMin
        'set initial max value of index gap
        iIndexGap = iIndexMin
 
        'increment recordset from first record
        .MoveNext
 
        'locate index gaps and
        'stop when reaching last of existing index
        While .EOF = False And iIndexGap < iIndexMax
 
            'set max value of possible gap
            iIndexGap = !IndexNum.Value
 
            'increment min value of possible gap
            iNewIndex = iOldIndex + 1
 
            'fill gap intervals with nulls
            Do While iIndexGap - iNewIndex >= 1 And iNewIndex < iIndexMax
 
                'add new record
                .AddNew
                !IndexNum.Value = iNewIndex
                .Update
 
                'increment new index number
                iNewIndex = iNewIndex + 1
            Loop
 
            'set min value of next possible gap
            iOldIndex = iIndexGap
            .MoveNext
        Wend
        .Close
    End With
 
    'notify when complete
    MsgBox "Done"
 
    Set rst = Nothing
    Set dbs = Nothing
End Sub

Then execute the code. It will fill in the gaps that are missed from an index field. So if you are using an autonumbering field as the index, you will need to duplicate that first so the function can create the missing index numbers.

The key aspect here is the sSQL variable. I've set it to a table 'tTest' and ordered it by the index field 'IndexNum'. This allows the function to walk through that field looking for gaps and filling them in appropriately. If the recordset isn't ordered right, it won't work correctly.

You can try this out on a copy of your tables ....

Again, it is not a query, but actually creating those 'empty' records for a quick fix.

HTH,
-dK

 

[boblarson]

You could have a table set up for just the numbering purposes using a single numeric field (long integer).

Then you can use this to generate the initial numbers:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngStart As Long
Dim lngEnd As Long
Dim lngAdd As Long

lngStart = Nz(DMin("IDFieldName", "TableNameHere"),0)
lngEnd = Nz(DMax("IDFieldName", "TableNameHere"),0)

Set db = CurrentDb

db.Execute "Delete * From YourNumberingTableNameHere", dbFailOnError

Set rst = db.OpenRecordset("YourNumberingTableNameHere")
lngAdd = lngStart

Do Until lngAdd = lngEnd + 1
    With rst
       .AddNew
       !IDFieldNameHere = lngAdd
       .Update
       lngAdd = lngAdd + 1
    End With
Loop

rst.Close
Set rst = Nothing

And then you can have that numbering table be in a query with your normal data and use an outer join from that numbering table to your normal data table.

 

[boblarson]

Seems like DKinley and I were going down similar paths.

 

[dkinley]

Haha .. yeah. I hadn't looked at creating a 'dummy' table and doing a union query to fill in the gaps, so to speak - and then using that as the output for the binary dump.

I wasn't sure if all the index's started out at the same number - or if those were used to link all of the tables together. My solution aimed at picking up an existing field starting at whatever number and going to the (max) of that field.

-dK

 

[boblarson]

Haha .. yeah. I hadn't looked at creating a 'dummy' table and doing a union query to fill in the gaps, so to speak - and then using that as the output for the binary dump.

I wasn't sure if all the index's started out at the same number - or if those were used to link all of the tables together.

-dK

You don't use a Union query. You use a regular query but with an outer join. That way all of the numbers occur (from the lowest to highest - which is what my code gets from the data table) and then shows nulls for the numbers that don't have data. I am building a sample.

 

[boblarson]

Here's a sample.

 

[dkinley]

Much cleaner than adding in the empty records.

I have to applaud since I rep spreading is in short supply on this end.

<Applauds>

-dK

 

[FatherNature]

Here's a sample.

Thanks for all the help. I'll try to integrate the different solutions to see what works best and report back on Monday

David

 

[boblarson]

Thanks for all the help. I'll try to integrate the different solutions to see what works best and report back on Monday

David

I think you'll find that just going with my suggestion will be much simpler and easier to implement. I don't think DKinley would have any objection to that, now that he's seen the way it is laid out.

 

[dkinley]

Bob is not only clever in presenting the most elegant solution - he is the most astute in observation as well.

Good luck, David.

-dK

 

[FatherNature]

Thanks for all the help, I implemented Bob's solution and it worked flawlessly. The only tweak I had to throw in was to pass in start and stop indexes since there was some blank space at the end.

Thank you for helping me get a better grip on DB programming, all of your help is very appreciated.
David

 

[boblarson]

Glad we could assist you and help with some learning.