[RESOLVED] Access: Multiple Select through 1 select statement

View post #4






I am not exactly sure how to explain this, but here is what I want to do:

I have a table, with a layout as follows

SplitNumber ID Course1 SD1 ED1 Course2 SD2 ED2...

up until 15

Now, I want to run a query to take that row, and place it into another table in the following layout

Course SD ED
Course2 SD ED
Course3 SD ED

up until 15

I have tried thinking of ways to do it with a query, but have run short. And I cant seem to find any reference to this sort of problem in any of the books I have.

If anyone could even point me in the right direction with some keywords to search or anything I would greatly appreciate it.

Kfc :D

So basically you want to take the columns and place them into one column.. ie concatenate the columns? Or have mis-read

I want to take the rows, and split them into columns ;)

Here is the code I am trying, keeps giving me an error though

VB Code:

Dim rsSplitting As ADODB.Recordset
Set rsSplitting = New ADODB.Recordset
Dim strTest As String
Dim strTest2 As String
Dim strTest3 As String
strTest = "Select Course" & lstSplit.Value & " from Splits where SplitsNumber > 0"
strTest2 = "Select SD" & lstSplit.Value & " from Splits where SplitsNumber > 0"
sttrTest3 = "Select SD" & lstSplit.Value & " from Splits where SplitsNumber > 0"
MsgBox "StrTest: " & strTest
rsSplitting.Open strTest, con, adOpenKeyset, adLockPessimistic, adCmdText
    txtCourse.SetFocus
    txtCourse.Text = rsSplitting.Fields("Course" & test)
rsSplitting.Close
rsSplitting.Open strTest2, con, adOpenKeyset, adLockPessimistic, adCmdText
    txtsd.SetFocus
    txtsd.Text = rsSplitting.Fields("SD" & test)
rsSplitting.Close
rsSplitting.Open strTest3, con, adOpenKeyset, adLockPessimistic, adCmdText
    txted.SetFocus
    txted.Text = rsSplitting.Fields("ED" & test)
rsSplitting.Close

If you could tell me how to use multiple selects in 1 select statement I would appreciate it ;), and the error I get is on

VB Code:

rsSplitting.Open strTest, con, adOpenKeyset, adLockPessimistic, adCmdText

and the error is

Quote:

---

Originally Posted by Runtime Error '3001'

Arguements are of the wrong type, are out of acceptable range, or are in conflict with one another

---

And the messagebox I have there is displaying

"Select Course1 from Splits where SplitsNumber > 0"

Solved it

VB Code:

Private Sub Form_Current()
Dim rsSplitting As ADODB.Recordset
Set rsSplitting = New ADODB.Recordset
Dim strTest As String
Dim strTest2 As String
Dim strTest3 As String
ID.SetFocus
strTest = "Select Course" & lstSplit.Value & " from Splits where ID = " & "'" & ID.Text & "'"
strTest2 = "Select SD" & lstSplit.Value & " from Splits where ID = " & "'" & ID.Text & "'"
strTest3 = "Select ED" & lstSplit.Value & " from Splits where ID = " & "'" & ID.Text & "'"
rsSplitting.Open strTest, con, adOpenKeyset, adLockPessimistic, adCmdText
    txtCourse.SetFocus
    txtCourse.Text = rsSplitting.Fields("Course" & test)
rsSplitting.Close
rsSplitting.Open strTest2, con, adOpenKeyset, adLockPessimistic, adCmdText
    txtStartDate.SetFocus
    txtStartDate.Text = rsSplitting.Fields("SD" & test)
rsSplitting.Close
rsSplitting.Open strTest3, con, adOpenKeyset, adLockPessimistic, adCmdText
MsgBox strTest3
    txtEndDate.SetFocus
    txtEndDate.Text = rsSplitting.Fields("ED" & test)
rsSplitting.Close
End Sub

Works :)


New question: Could someone tell me, is it possible to select multiple columns through 1 select statement?

Selecting multiple columns..

"SELECT Course" & lstSplit.Value & ", SD" & lstSplit.Value & ", ED" & lstSplit.Value & " FROM Splits WHERE ID = " & "'" & ID.Text & "'"

Much obliged, although I can't rep you yet :(

Not a problem...