Fill in Blank Spaces between records

[Alfredomulet]

Hello everyone,
My mind doesn't want to work today.. I'm stuck with this: How can I fill up the blank spaces between two records with the value of the first one? That is, I have a table that looks like this (PRO_DESC & PRO_NAME are the column names):

PRO_DESC PRO_NAME
Request # Project 1
A
B
Request # Project 2
A
B
C
Request # Project 3
A
B
Request # Project 4
A

And I need to fill in the blanks so it looks like:

PRO_DESC PRO_NAME
Request # Project 1
A Project 1
B Project 1
Request # Project 2
A Project 2
B Project 2
C Project 2
Request # Project 3
A Project 3
B Project 3
Request # Project 4
A Project 4

How can this be done with a SQL query???
Cheers,
Alfredo

 

[Eupher]

Alfredo,

What you want can be done - probably not with a SQL query, but certainly with VBA code. Can you provide some more info about what you're trying to do? My first impression is that you are asking for problems with your data structure and I am inclined to recommend you normalize your data. But maybe I just don't have a good handle on what your goal is.

Ken S.

 

[Alfredomulet]

Thanks for your quick reply, Eupher. This table is actualy a raw table created from a set of excel spreadsheets which feed the final (normalized) schema of the database. I found in another thread the following code:

Option Compare Database
Option Explicit

Function IMPORTARFASES()

Dim dbs
Dim rst
Dim Fname

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT PRO_NAME from ActualDatebyPhasedeptID118")

rst.MoveFirst

If Not IsNull(rst!PRO_NAME) Then
    Fname = rst!PRO_NAME
    
End If
     

While Not rst.EOF
    rst.Move 1
    
    If IsNull(rst!PRO_NAME) Then
    
        rst.Edit
        rst!PRO_NAME = Fname
        rst.Update
        
   Else: Fname = rst!PRO_NAME
        
    End If
    
Wend

rst.Close
dbs.Close

End Function

Which works fine, but at the end of the recordset, when there are no more PRO_NAME null values gives me the error:

Run-time error '3021'. No current record

at line Else: Fname = rst!Field3

Any ideas???
cheers
Alfredo

 

[Eupher]

Take a look at the placement of your rst.Move statement. When you come to the last record, EOF still tests false, so you loop through again. But the first line in the loop moves to the next record - nonexistent. Then when you try to access the PRO_Name value, you pop an error. I would put the first Move statement outside the loop, then put another as the last action inside the loop.

Ken S.

 

[Alfredomulet]

Thank you very much, Eupher, it worked perfectly moving the move statement as you suggest!

thanks again,
Alfredo