column from Row value

[leoaziz]

Is there a way to create a colum from values in a row?
For example, Let's say I have the following:
bname,postcode,floor,business
a,qqq qqq,ground,office
a,qqq qqq first, office
a, qqq qqq, second,office
b, www www, first, shop
b,www www, second, office

i can turn this using crosstab like this;
bname,postcode,ground,first,second
a,qqq qqq, office,office,office
b,www www,,shop,office

now i want to convert

if 1bname have different business at its same floor like
bname,postcode,ground,first,second
a,qqq qqq,ground,office
a,qqq qqq first, office
a, qqq qqq, ground,shop
a,qqq qqq,second,office


i want like
bname,postcode,ground,first,second
a, qqq qqq,office/shop,office,office

i have lots of data, how can i convert this using vb-6/MS-Access, or in crosstab option how can i join this string.

Thanks for all your help guys!
Aziz

[Stewart Ross]

Hi Aziz, and Welcome to Bytes.

The dummy data you have posted is too far removed from real data to get a feel for what you are actually wanting to see as output. (Posting a's and b's and a lot of 'qqq qqq' postcodes is actually much less clear than if you had supplied proper values with some changes to protect confidentiality.)

If you can please post something more like what you want to see - with different values for your groupings so that the range and type of difference is clearer - perhaps it will be possible to come up with practical suggestions.

Access crosstab queries can only pivot one column, so these are not really suitable if you want more than one column, or if you need to select different columns at times.

Please clarify what your real needs are and I'm sure we will do our best to help you.

Regards

-Stewart

[nico5038]

Basically you want to RollUp a field value into one field.
A solution could be to create a function that will "rollup" the floor into one field like:

Function fncRollUp(strBusinessName as string)
 
dim rs as dao.recordset
 
set rs = currentdb.openrecordset("select * from tblX Where 1bname='" & strBusinessName & "'")
   While NOT rs.EOF
       fncRollUp = fncRollUp & ", " & rs!floor
       rs.MoveNext
   End With
 
End Function
 

This function must be copy/pasted into a module and can be used in a query like:

SELECT DISTINCT 1bname, fncRollUp([1bname]) As Floors  FROM tblX ;
 

Nic;o)

[ADezii]

I may have over complicated matters a little, but here goes, using your data. For now, it just Prints the results, but it's too close to bedtime so I'll leave it as such. If Nico's idea works well for you, I strongly suggest you go with his approach.

Dim strSQL As String
Dim strSQL_2 As String
Dim strBusiness As String
Dim strPostCode As String
Dim MyDB As DAO.Database
Dim rstUniqueBusiness As DAO.Recordset
Dim rstBData As DAO.Recordset
 
strSQL = "SELECT DISTINCT tblBusinesses.bname FROM tblBusinesses " & _
         "ORDER BY tblBusinesses.bname;"
 
Set MyDB = CurrentDb
 
 
'Generate a Recordset representing Unique Businessess Ascending
Set rstUniqueBusiness = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
 
With rstUniqueBusiness
  Do While Not .EOF
    strSQL_2 = "SELECT * From tblBusinesses Where tblBusinesses.bname = '" & _
                ![bname] & "'"
    Set rstBData = MyDB.OpenRecordset(strSQL_2, dbOpenForwardOnly)
     strPostCode = rstBData![postcode]
      Do While Not rstBData.EOF         'build the Business String
        strBusiness = strBusiness & rstBData![Business] & ", "
        rstBData.MoveNext
      Loop
      Debug.Print ![bname] & ", " & strPostCode & ", " & _
                  Left$(strBusiness, Len(strBusiness) - 2)
      strBusiness = ""      'Reset
    .MoveNext
  Loop
End With
 
rstUniqueBusiness.Close
rstBData.Close
Set rstUniqueBusiness = Nothing
Set rstBData = Nothing

OUTPUT:

a, qqq qqq, office, office, office, shop
b, www www, shop, office

[ADezii]

Basically you want to RollUp a field value into one field.
A solution could be to create a function that will "rollup" the floor into one field like:

Function fncRollUp(strBusinessName as string)
 
dim rs as dao.recordset
 
set rs = currentdb.openrecordset("select * from tblX Where 1bname='" & strBusinessName & "'")
   While NOT rs.EOF
       fncRollUp = fncRollUp & ", " & rs!floor
       rs.MoveNext
   End With
 
End Function
 

This function must be copy/pasted into a module and can be used in a query like:

SELECT DISTINCT 1bname, fncRollUp([1bname]) As Floors  FROM tblX ;
 

Nic;o)

Really nice approach Nico, I was totally unaware that you can call a Function recursively within the context of an SQL Statement. That's what I love about this place, I get to learn something new every day! (LOL). Again, nice job.

[nico5038]

User functions in a query can be used "inside" Access and will be executed for every row that's extracted. You can't use this approach when passing the query directly to the Jet engine e.g. directly from VB.

Regards,

Nic;o)

[ADezii]

User functions in a query can be used "inside" Access and will be executed for every row that's extracted. You can't use this approach when passing the query directly to the Jet engine e.g. directly from VB.

Regards,

Nic;o)

I was more interested in the fact that you can have Recursive calls to the fncRollUp() Function for each Record processed in the SQL Statement. Again, thanks for the information.

[nico5038]

I was more interested in the fact that you can have Recursive calls to the fncRollUp() Function for each Record processed in the SQL Statement. Again, thanks for the information.

? Nothing recursive about this call in the query as it's "repeated" for every row.
A recursive function would be calling itself.

In my RollUp function the statement:

fncRollUp = fncRollUp & ", " & rs!floor
 

Perhaps caused you to think it's recursive, but it's a string handling operation on the variable fncRollUp, so no call to the function, but the variable fncRollUp.

Nic;o)

[leoaziz]

Thank you very much.

I want to view this result at msflexgrid uisng fncRollUp, and i write this code to view

Private Sub Form_Load()
Call connect 'for database connection

' msflexgrid code to view data
Dim rs As New ADODB.Recordset 'variable

Dim i, j As Integer 'variable

rs.Open "SELECT DISTINCT bname, fncRollUp([1bname]) As Floor FROM Table2 ; ", cnn, adOpenKeyset, adLockOptimistic

With rs

msfg1.Rows = rs.RecordCount + 1 'to show Rows

msfg1.Cols = rs.Fields.Count 'to show Colum

'msfg1.Cols = rs.Fields.Count + 1 'to show Colum

For i = 0 To rs.Fields.Count - 1

msfg1.TextMatrix(0, i) = rs.Fields(i).Name 'to show field name at 1st row.

' msfg1.TextMatrix(0, i + 1) = rs.Fields(i).Name 'to show field name at 1st row.

Next i

rs.MoveFirst

For i = 1 To rs.RecordCount

For j = 0 To rs.Fields.Count + 1

msfg1.TextMatrix(i, j) = rs.Fields(j)

'msfg1.TextMatrix(i, j + 1) = rs.Fields(j)

Next j

rs.MoveNext

Next i

rs.Close

End With

its give an error message:

Run-time error ‘-214721900(80040e14)’

Undefined function ‘fncRollUp’ in expression


where i mistake, or how can i view

and Thank You again

Aziz

[nico5038]

Hi Aziz,

I already warned: "You can't use this approach when passing the query directly to the Jet engine e.g. directly from VB."
That's exactly what you're doing. Only when executing queries from "within" Access a user defined function will work.

You can however use:
rs.Open "SELECT DISTINCT bname FROM Table2 ; "
and in the WHILE/WEND loop call the fncRollUp(rs!bname) to get the rolled up data.

Nic;o)