On Mar 22, 8:15 pm, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:
> 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
>
> "inet...@gmail.com" wrote:
> > I have a Table imported from text file containing Records of books.
> > BUT the fields and data
> > value is entered as the First and Second Column (ColA, ColB). The
> > records are separated by 2 empty rows. Some fields are missing for
> > some records: Some records may miss the 'author' whereas some records
> > may miss some other fields, say ,'subject' AND 'totalpage'.
>
> > The Objective is Making it as a "Normal" Table (Vertical Columns and
> > Horizontal Rows) by tranforming the record horizontally with Each
> > field in one column with missing field blanked so that all the field
> > are
> > aligned in a column e.g. colA for 'Title', colB for 'Author' with
> > each individual information of a particular book in s single
> > horizontal row. (See Figures 1 & 2)
>
> > How can we convert varying length Vertically-aligned records (Figure
> > 1) to Horizontal Rows of list (tabular) (Figure 2)
>
> > (Figure 1- Three records: varying length- subject & totalpage
> > missing)
> > ==================================================
> > (Coulmn A) (Column B)
> > Title Intro to Cpt
> > Author James, Page
> > publisher M-HILL
> > subject CPT
> > totalpage 311
> > isbn 123
>
> > (Coulmn A) (Column B) <-- Subject % totalpage missing
> > Title Basic A/C
> > Author Ben William
> > publisher JWS
> > isbn 721
>
> > (Coulmn A) (Column B) <-- Author Missing
> > Title Modern Phy
> > publisher P-HALL
> > subject PHY
> > totalpage 466
> > isbn 265
>
> > (Figure 2: (Objective) "Normal" table with each detail of a individual
> > book listed in one row
> > horizontally)
> > ============================================
> > Title Author publisher subject
> > totalpage isbn
> > ---------------- ------------------- -------------
> > ---------- ------------- ------
> > Intro to Cpt James, Page M-HILL CPT 311
> > 123
> > Basic A/C Ben William
> > JWS 721
> > Modern Phy P-HALL PHY 466
> > 265
>
> > Thank in advance
Thank you very much. The code performs the task successfully.
Similar Threads
