Nested Select statements

Clarifications

more to the point I have no intention of ever doing so.
That would give you a cartesian product if tyhe parse didn't get really upset. Table1 * Table2 records
Same as
Select Column1,Column2,ColumnA From Table1,Table2

Nested queries are
Select * From (Someotherquery) dummyalias.

Most DBMS's make you alias the query in the brackets.

I do this kind of thing all the time, for instance, i select some columns from a main table and a count of related records in another table:

select some_id, some_info, other_count from some_table LEFT OUTER JOIN (select other_id, count(*) as other_count from other_table GROUP BY other_id) as other ON (other.other_id = some_table.some_id)

Here is an example:

SELECT DISTINCTROW Consumer.[Comp Code], Consumer.[Comp Date], Consumer.ADV, Consumer.[Case Type], Consumer.description, [fname] & " " & [lname] AS Name, advisor.[Adv Name], Business.[Bus Name], Business.[Trade Code], Consumer.[Referred To], Consumer.[Practice Code], Consumer.[Process Code], Consumer.[Status Code], Consumer.[Practice Code]
FROM Person INNER JOIN ((advisor RIGHT JOIN Consumer ON advisor.[Adv Code] = Consumer.ADV) LEFT JOIN Business ON Consumer.[Bus Code] = Business.[Bus Code]) ON Person.[Person Code] = Consumer.[Person Code]
WHERE (((Consumer.[Comp Date])>=[forms]![frmadvisorinfo]![BeginDate] And (Consumer.[Comp Date])<=[forms]![frmadvisorinfo]![EndDate]) AND ((Consumer.ADV)=[forms]![frmmainmenu]![txtuserid]));