Transpose – (Sort of)

Topic

New Reply

Hi,
In Access 2000, I need to take a table with 3 fields Data_ID, Dealer, User
and append another table with the same fields in a pipe “|” separated format.( this will be used in another application.)

Example:
Data_ID Dealer User
1234 ABC Dealer smitha
3423 DEF Dealer smitha
5545 GHI Dealer smitha
3323 JKL Dealer jonesd
3856 MNO Dealer jonesd
….

Output:
Data_ID Dealer User
1234|3423|5545 ABC Dealer|DEF Dealer||GHI Dealer smitha
3323|3856 JKL Dealer|MNO Dealer jonesd

The following function works until the last record. It errors with no current record and the last record isn’t written.
I don’t know how to get it to stop at the end and write the record.

Function CreateDealerList()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim strUsername As String
Dim StrSql As String

Set db = CurrentDb
StrSql = “SELECT tblDealers.DATA_ID, tblDealers.User, tblDealers.Dealer ” & _
“FROM tblDealers WHERE (((tblDealers.Status) = ‘Active’))ORDER BY tblDealers.User, tblDealers.Dealer;”

DoCmd.SetWarnings False
DoCmd.RunSQL “Delete tblDealerList.* From tblDealerList;”
DoCmd.SetWarnings True
Set rs = db.OpenRecordset(StrSql)
Set rst = db.OpenRecordset(“tblDealerList”)

Do While Not rs.EOF
strUsername = rs!User
rst.AddNew
rst!User = rs!User
Do Until strUsername rs!User
rst!Dealer = rst!Dealer & rs!Dealer & “|”
rst!Data_ID = rst!Data_ID & rs!Data_ID & “|”
rs.MoveNext
Loop
rst.Update
rs.MoveNext
Loop
End Function

Thanks for any help,
Scott

Reply | Quote

Replies

Hi,
In Access 2000, I need to take a table with 3 fields Data_ID, Dealer, User
and append another table with the same fields in a pipe “|” separated format.( this will be used in another application.)

Example:
Data_ID Dealer User
1234 ABC Dealer smitha
3423 DEF Dealer smitha
5545 GHI Dealer smitha
3323 JKL Dealer jonesd
3856 MNO Dealer jonesd
….

Output:
Data_ID Dealer User
1234|3423|5545 ABC Dealer|DEF Dealer||GHI Dealer smitha
3323|3856 JKL Dealer|MNO Dealer jonesd

The following function works until the last record. It errors with no current record and the last record isn’t written.
I don’t know how to get it to stop at the end and write the record.

Function CreateDealerList()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim strUsername As String
Dim StrSql As String

Set db = CurrentDb
StrSql = “SELECT tblDealers.DATA_ID, tblDealers.User, tblDealers.Dealer ” & _
“FROM tblDealers WHERE (((tblDealers.Status) = ‘Active’))ORDER BY tblDealers.User, tblDealers.Dealer;”

DoCmd.SetWarnings False
DoCmd.RunSQL “Delete tblDealerList.* From tblDealerList;”
DoCmd.SetWarnings True
Set rs = db.OpenRecordset(StrSql)
Set rst = db.OpenRecordset(“tblDealerList”)

Do While Not rs.EOF
strUsername = rs!User
rst.AddNew
rst!User = rs!User
Do Until strUsername rs!User
rst!Dealer = rst!Dealer & rs!Dealer & “|”
rst!Data_ID = rst!Data_ID & rs!Data_ID & “|”
rs.MoveNext
Loop
rst.Update
rs.MoveNext
Loop
rst.Update
End Function

Thanks for any help,
Scott

Add the second update method call, shown above in bold blue.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

David,
Thanks but it never gets to that point when its on the last record. Its still in the inside loop.
Scott

Reply | Quote

Does it get out of the inner loop?

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

Not when it is on the very last record.
Scott

Reply | Quote

Duh!

You have the following.

Do Until strUsername rs!User

This provides no condition to exit the loop when rs reaches end of file. Add a sceonc condition, as follows.

Do Until strUsername rs!User Or rs.EOF

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

david,
I tried that too. Still same message.

Scott

Reply | Quote

Message? What message?

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

Try this version. It also avoids having a pipe character at the end of the strings.

Code deleted because it was incorrect – see my next reply

Reply | Quote

Hans,
Its almost there but something isn’t looping correctly.
Here is the output.

Here is a sample database with your function if you can take a look.

Thanks,
Scott

Reply | Quote

Sorry, I was too hasty. Here is a modified version.

Code:

Function CreateDealerList()
  Dim db As DAO.Database
  Dim rst1 As DAO.Recordset
  Dim rst2 As DAO.Recordset
  Dim rst As DAO.Recordset
  Dim strDataID As String
  Dim strDealer As String

  Set db = CurrentDb

  db.Execute "DELETE * FROM tblDealerList;"
  Set rst = db.OpenRecordset("tblDealerList")
  Set rst1 = db.OpenRecordset("SELECT User FROM tblDealers GROUP BY User")

  Do While Not rst1.EOF
    Set rst2 = db.OpenRecordset("SELECT Data_ID, Dealer, User " & _
      "FROM tblDealers WHERE User='" & rst1!User & "' ORDER BY Dealer")
      
    strDealer = ""
    strDataID = ""
    Do While Not rst2.EOF
      strDealer = strDealer & "|" & rst2!Dealer
      strDataID = strDataID & "|" & rst2!Data_ID
      rst2.MoveNext
    Loop
    
    rst.AddNew
    rst!User = rst1!User
    rst!Data_ID = Mid(strDataID, 2)
    rst!Dealer = Mid(strDealer, 2)
    rst.Update
    rst1.MoveNext
  Loop

  rst1.Close
  rst2.Close
  rst.Close
  Set rst1 = Nothing
  Set rst2 = Nothing
  Set rst = Nothing
  Set db = Nothing
End Function

Reply | Quote

Add thes two lines to the code :
strDealer = “”
strDataID = “”

Code:

...
  Do While Not rs.EOF
    strUsername = rs!User
    strDealer = ""
    strDataID = "" 
    Do Until strUsername  rs!User
       strDealer = strDealer & "|" & rs!Dealer
...

Reply | Quote

Sorry,
Should have looked before I posted. I just had to add 2 lines (in bold)

Thanks for the solution

Function CreateDealerList()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim strUsername As String
Dim strSQL As String
Dim strDataID As String
Dim strDealer As String

Set db = CurrentDb
strSQL = “SELECT DATA_ID, User, Dealer ” & _
“FROM tblDealers ORDER BY User, Dealer”

DoCmd.SetWarnings False
DoCmd.RunSQL “DELETE * FROM tblDealerList;”
DoCmd.SetWarnings True
Set rs = db.OpenRecordset(strSQL)
Set rst = db.OpenRecordset(“tblDealerList”)

Do While Not rs.EOF
strUsername = rs!User
[indent]strDealer = “”
strDataID = “”[/indent]
Do Until strUsername rs!User
strDealer = strDealer & “|” & rs!Dealer
strDataID = strDataID & “|” & rs!Data_ID
rs.MoveNext
If rs.EOF Then Exit Do
Loop
rst.AddNew
rst!User = strUsername
rst!Data_ID = Mid(strDataID, 2)
rst!Dealer = Mid(strDealer, 2)
rst.Update
If rs.EOF Then Exit Do
rs.MoveNext
Loop

rst.Close
rs.Close
Set rst = Nothing
Set rs = Nothing
Set db = Nothing
End Function

Reply | Quote

Francois,
Scary how almost live this forum is. Or I need to be able to type faster.
Thanks to all.

Reply | Quote

Francois,
Scary how almost live this forum is. Or I need to be able to type faster.
Thanks to all.

Ah, for the good old days, when they were all this “live.”

Today, we usually don’t have time for such lively exchanges.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

Here is a version that (I think) does the job with just a query, using the concat function.

Reply | Quote