Can you loop within a select statment?

[Jake]

Can you loop within a select statement?

 

[John Spencer]

No, you cannot loop in a select statement.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[louisjohnphillips]

No, you cannot loop in a select statement.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================




- Show quoted text -

I'm going to guess at what your intended meaning was when you wrote,
"Can you loop within a select statment? "

No. A select statement projects a table.

Yes. You can loop through each row in that table if it is projected
into a cursor. Reference the ADODB library in your project and try
something along this model.


Sub Main()

Dim sqlStmt As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

sqlStmt = "SELECT Name from MSysObjects"

rs.Open sqlStmt, CurrentProject.AccessConnection, adOpenDynamic,
adLockReadOnly, -1

While Not rs.EOF

' Perform one or more actions on each "Name" returned

rs.MoveNext

Wend

rs.Close
Set rs = Nothing

End Sub

Perhaps, you will receive a more definitive answer when you ask a more
direct question.

 

[pietlinden]

Can you loop within a select statement?

beating a dead dog, but no, you cannot loop in any set operations
(queries). You can open a query/SQL statement using a recordset
(result set) and loop through that.

 

[Michel Walsh]

What do you mean by 'looping'? A SELECT, in SQL, is already producing a
sequence, kind of a for-each. If you need two for-each's:

foreach ( i in set_of_is)
foreach ( j in set_of_js)

you can 'get' it, in SQL, like this:

SELECT ...
FROM set_of_is, set_of_js


and it is the FROM clause, not the SELECT clause, which 'speaks' of the two
loops. The SELECT clause just tell what we want (from the 'loops' ).

If your two foreach loops need to be 'in-synch', not the Justin Timberlake
thing, but more like imposing a condition that has to be enforced, at this
moment:

foreach ( i in set_of_is)
foreach ( j in set_of_js)
if( i.City <> j.City) continue
... do something otherwise, here


then you can use something like:


SELECT ...
FROM set_of_is INNER JOIN set_of_js
ON set_of_is.City = set_of_js.City


or

SELECT ...
FROM set_of_is, set_of_js
WHERE set_of_is.City = set_of_js.City



but again, it is the FROM clause which 'speaks' of the 'loops', helped, or
not, with a WHERE clause (or with a ON clause of a JOIN).



So, 'yes' we can 'loop', but that is only 'as if we were looping', and it is
done in the FROM clause, not in the SELECT clause.



Hoping it may help,
Vanderghast, Access MVP