[RESOLVED] Access: Multiple Select through 1 select statement

[kfcSmitty]

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.

[dannymking]

Kfc

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

[kfcSmitty]

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

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

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

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

and the error is

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"

[kfcSmitty]

Solved it

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?

[dannymking]

Selecting multiple columns..

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

[kfcSmitty]

Much obliged, although I can't rep you yet

[dannymking]

Not a problem...