In a relational database tables are sets of rows.  By definition sets do not
have any intrinsic order, so the statement "The records are separated by 2
empty rows" does not have any real meaning in terms of the set of rows.  So,
just iterating through the table and creating a row in a new table from each
'block' of rows is not necessarily going to work.  However, you might well be
able to do so by first adding another column of autonumber data type to the
table, ColKey say.   Do this and then check to see that the rows are
numbered, including the empty rows, so that the sequence follows the order in
which you'll need to iterate through the table.

Assuming that's the case then the next thing to check is that the empty rows
have Null in ColA, rather than a zero-length string.  If the column's Allow
Zero Length property is False (No) in table design and its Required property
is also False then its reasonable to assume that they are Null.  The code
below assumes this to be the case, but can be easily amended if they contain
zero-length strings.

The next step is to add another column to the table, RecNo say, of long
integer number data type into which values will be inserted to identify each
'record', i.e. each block of rows which will be inserted into a single row in
the new table.

Create the new empty table, Books, with columns Title, Author, Publisher
etc.  Make them all text data type for the moment.  Add an autonumber column
BookID as the table's primary key.

The first stage of the process is to iterate through the original table,
which I'll call BookList, in ColKey order and insert values into the RecNo
column.  This will require a little VBA function in a standard module like so:

Public Function InsertRecNo()

   Dim rst As ADODB.Recordset
   Dim strSQL As String
   Dim lngRecNo As Long
   
   strSQL = _
      "SELECT ColA,RecNo " & _
      "FROM BookList " & _
      "ORDER BY ColKey"
       
   Set rst = New ADODB.Recordset
   rst.ActiveConnection = CurrentProject.Connection
   rst.Open _
       Source:=strSQL, _
       CursorType:=adOpenForwardOnly, _
       LockType:=adLockOptimistic
       
   With rst
       Do While Not .EOF
           If Not IsNull(.Fields("ColA")) Then
               lngRecNo = lngRecNo + 1
           End If
           
           Do While Not IsNull(.Fields("ColA"))
               On Error Resume Next
               .Fields("RecNo") = lngRecNo
               If Err.Number = 3021 Then
                   Exit Function
               Else
                   If Err.Number <> 0 Then
                       MsgBox Err.Description
                       Exit Function
                   End If
               End If
               On Error GoTo 0
               .MoveNext
           Loop
       .MoveNext
       Loop
   End With
   
End Function

After you call this function the RecNo column should contain sets of
identical numbers from 1 onwards each number identifying a block of rows
making up a book 'record'.

Next you need to call another function to append one row to the new Books
table for each block of rows in BookList:

Public Function AppendBooks()

   Dim cmd As ADODB.Command
   Dim strSQL As String
   Dim n As Integer
   Dim lngTotalBooks As Long
   Dim varTitle As Variant
   Dim varAuthor As Variant
   Dim varPublisher As Variant
   Dim varTotalPage As Variant
   Dim varISBN As Variant

   Set cmd = New ADODB.Command
   cmd.ActiveConnection = CurrentProject.Connection
   cmd.CommandType = adCmdText
   
   lngTotalBooks = DMax("RecNo", "BookList")
   
   For n = 1 To lngTotalBooks
       varTitle = DLookup("ColB", "BookList", "ColA = ""Title"" AND RecNo =
" & n)
       varAuthor = DLookup("ColB", "BookList", "ColA = ""Author"" AND RecNo
= " & n)
       varPublisher = DLookup("ColB", "BookList", "ColA = ""Publisher"" AND
RecNo = " & n)
       varTotalPage = DLookup("ColB", "BookList", "ColA = ""Totalpage"" AND
RecNo = " & n)
       varISBN = DLookup("ColB", "BookList", "ColA = ""ISBN"" AND RecNo = "
& n)
       
       strSQL = _
           "INSERT INTO Books" & _
           "(Title,Author,Publisher,TotalPage,ISBN) " & _
           "VALUES(" & _
           IIf(IsNull(varTitle), "NULL", """" & varTitle & """") & "," & _
           IIf(IsNull(varAuthor), "NULL", """" & varAuthor & """") & "," & _
           IIf(IsNull(varPublisher), "NULL", """" & varPublisher & """") &
"," & _
           IIf(IsNull(varTotalPage), "NULL", """" & varTotalPage & """") &
"," & _
           IIf(IsNull(varISBN), "NULL", """" & varISBN & """") & ")"
       
       cmd.CommandText = strSQL
       cmd.Execute
   Next n
   
End Function

Some longer lines of the above code may be split over two lines when you
view this in your newsreader, so watch out for this and correct it if
necessary when you paste the code into the VBA window.

Ken Sheridan
Stafford, England

On 22 Mar, 07:55, inet...@gmail.com wrote:

Do you mean that the data source shown in (1) is actually a SQL table
with two columns and without a key? If so, then what you are asking
looks to be impossible. There is nothing in your table to specify
which "fields" defined in column A are supposed to be related to which
other fields to form a complete record. Tables have no logical
ordering so the order you wrote them out in your post is irrelevant.

What you could do is create a staging table with a key (a row number
for example) and populate that key at the time you load the data. You
could do this using DTS or Integration Services.

Every table should have a key. There are no exceptions.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

yeah you'll need to parse this by hand

I swear I could do it in about 10 minutes

-Todos

On Mar 22, 12:55 am, inet...@gmail.com wrote: