By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

column from Row value

P: 2
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
Aug 24 '08 #1
Share this Question
Share on Google+
9 Replies


Expert Mod 2.5K+
P: 2,545
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
Aug 24 '08 #2

nico5038
Expert 2.5K+
P: 3,072
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:

Expand|Select|Wrap|Line Numbers
  1. Function fncRollUp(strBusinessName as string)
  2.  
  3. dim rs as dao.recordset
  4.  
  5. set rs = currentdb.openrecordset("select * from tblX Where 1bname='" & strBusinessName & "'")
  6.    While NOT rs.EOF
  7.        fncRollUp = fncRollUp & ", " & rs!floor
  8.        rs.MoveNext
  9.    End With
  10.  
  11. End Function
  12.  
This function must be copy/pasted into a module and can be used in a query like:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 1bname, fncRollUp([1bname]) As Floors  FROM tblX ;
  2.  
Nic;o)
Aug 24 '08 #3

ADezii
Expert 5K+
P: 8,669
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim strSQL_2 As String
  3. Dim strBusiness As String
  4. Dim strPostCode As String
  5. Dim MyDB As DAO.Database
  6. Dim rstUniqueBusiness As DAO.Recordset
  7. Dim rstBData As DAO.Recordset
  8.  
  9. strSQL = "SELECT DISTINCT tblBusinesses.bname FROM tblBusinesses " & _
  10.          "ORDER BY tblBusinesses.bname;"
  11.  
  12. Set MyDB = CurrentDb
  13.  
  14.  
  15. 'Generate a Recordset representing Unique Businessess Ascending
  16. Set rstUniqueBusiness = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  17.  
  18. With rstUniqueBusiness
  19.   Do While Not .EOF
  20.     strSQL_2 = "SELECT * From tblBusinesses Where tblBusinesses.bname = '" & _
  21.                 ![bname] & "'"
  22.     Set rstBData = MyDB.OpenRecordset(strSQL_2, dbOpenForwardOnly)
  23.      strPostCode = rstBData![postcode]
  24.       Do While Not rstBData.EOF         'build the Business String
  25.         strBusiness = strBusiness & rstBData![Business] & ", "
  26.         rstBData.MoveNext
  27.       Loop
  28.       Debug.Print ![bname] & ", " & strPostCode & ", " & _
  29.                   Left$(strBusiness, Len(strBusiness) - 2)
  30.       strBusiness = ""      'Reset
  31.     .MoveNext
  32.   Loop
  33. End With
  34.  
  35. rstUniqueBusiness.Close
  36. rstBData.Close
  37. Set rstUniqueBusiness = Nothing
  38. Set rstBData = Nothing
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. a, qqq qqq, office, office, office, shop
  2. b, www www, shop, office
Aug 25 '08 #4

ADezii
Expert 5K+
P: 8,669
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:

Expand|Select|Wrap|Line Numbers
  1. Function fncRollUp(strBusinessName as string)
  2.  
  3. dim rs as dao.recordset
  4.  
  5. set rs = currentdb.openrecordset("select * from tblX Where 1bname='" & strBusinessName & "'")
  6.    While NOT rs.EOF
  7.        fncRollUp = fncRollUp & ", " & rs!floor
  8.        rs.MoveNext
  9.    End With
  10.  
  11. End Function
  12.  
This function must be copy/pasted into a module and can be used in a query like:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 1bname, fncRollUp([1bname]) As Floors  FROM tblX ;
  2.  
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.
Aug 25 '08 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Aug 25 '08 #6

ADezii
Expert 5K+
P: 8,669
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.
Aug 25 '08 #7

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. fncRollUp = fncRollUp & ", " & rs!floor
  2.  
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)
Aug 25 '08 #8

P: 2
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
Aug 26 '08 #9

nico5038
Expert 2.5K+
P: 3,072
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)
Aug 26 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.