column to row

[awake2424]

Is there a way to create a query that will transpose table columns into rows:
Genes Cytoband
xxxx yyyyyy
xxx yyyyyy
xxx yyyyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyyy
xxxxxxx yyyyy
xxx yyyyyy
xxxx yyyyy
xxxxxxx yyyyyyy
xxxx yyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyy

Is there a way to display the table in a single row seperated by commas:
xxxx yyyyy, xxxx yyyyyyy, xxxxxxx yyyyyyyy, xxxxx yyyyyyy

 

[Uncle Gizmo]

Have a look here, it may provide some inspiration:

http://www.access-programmers.co.uk/forums/showpost.php?p=308045&postcount=2

 

[billmeye]

It looks like you actually want to combine both columns and all rows into a single column and single row? Not exactly transposing which would be swapping the columns and rows but maintaining a list or rows, not a single row.

I've attached an example using VBA to concatenate what I think you might be shooting for. The procedure is Concat() and can be called from an unbound control on a form or report or a query if you need to return other pieces of information from the source table.

 

[awake2424]

Sub Test()
'
' Test Macro
'
'
Selection.Range.InsertDatabase Format:=11, Style:=191, LinkToSource:=False, _
        SQLStatement:="SELECT * FROM `tblTest`" & "", _
        DataSource:="N:\Torrent\Setup\NGS.accdb", _
        From:=-1, To:=-1, IncludeFields:=True
End Sub

seems to output the data:
Genes Cytoband
xxxx yyyyyy
xxx yyyyyy
xxx yyyyyy

A single row separated by commas is desired:
xxxx yyyyy, xxxx yyyyyyy, xxxxxxx yyyyyyyy, xxxxx yyyyyyy

I am not sure how to modify the code to include concatenate, but that is what I would like to do. Thank you.

 

[billmeye]

Are you working from Excel or Access? The sample I provided is for Access and assumes you either have a local or linked table to use as the source, in my example it is Table1. It also only produces a single data point, not a new table. To use the code, just replace Table1 with your table name and Genes and Cytoband with the actual field names in the table. If you actually have a table you are working from you could attach the DB and I can adjust it for you for an example.

 

[awake2424]

The code basically connect to the table in the database and pulls the two columns into a SQL word report linked to an access db. I attached a sample and thank you for your help.

 

[billmeye]

Here is your DB with the form Form1 showing how it works. You could save the value to a table if you have one setup or show in the form or on a report.

 

[awake2424]

Is it possible to put the concatenate code into the VBA? Thanks.

Sub Test()
'
' Test Macro
'
'
Selection.Range.InsertDatabase Format:=11, Style:=191, LinkToSource:=False, _
        SQLStatement:="SELECT * FROM `tblTest`" & "", _
        DataSource:="N:\Torrent\Setup\NGS.accdb", _
        From:=-1, To:=-1, IncludeFields:=True
End Sub

 

[billmeye]

Yes, the code is VBA code in a function. You can move the code to an event procedure in your form.

 

[awake2424]

I tried to insert the vba into the code and it does not format the text. The out putis the same as the original. Thanks.

 

[billmeye]

The really is no advantage to moving the code from the Module to your specific event procedure. Just call the function as I showed you in the form example:

Me.MyField = Concat()

 

[awake2424]

Is it possible to use a field on the form (Order) as an If statement that based on the selection in order a certain table is used? For example, if Order = Jack then tblTest is used, but if Order = Sam then tblTest2 is used. Thank you.

Option Compare Database
Option Explicit

Function Concat() As String
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset("tblTest", dbOpenSnapshot)
If Rst.RecordCount > 0 Then
Rst.MoveLast
Rst.MoveFirst
Concat = Rst!Gene & " " & Rst!Cytoband
Rst.MoveNext
Do While Not Rst.EOF
Concat = Concat & ", " & Rst!Gene & " " & Rst!Cytoband
Rst.MoveNext
Loop
End If
Rst.Close
Set Rst = Nothing
End Function