A SQL question

pankaj_daga · December 17th, 2003, 02:16 PM

Hi everyone,

USing access, I try to run the following SQL:

SELECT (Select Value from AnotherTable) FROM MyQuery

MyQuery returns a set of data and I want to filter it further by selecting only the records that are returned by the sub-select. However, this does not work. Are such select queries illegal. Is it not possible to tell it the column names for select through another select query?

Thanks,
Pankaj

raulsormus · December 17th, 2003, 04:41 PM

Try this kind of statement

SELECT Table1.a, Table1.x, Table1.y FROM Table1
WHERE (Table1.x IN (SELECT Table2.x FROM Table2));

Regards,
Raul

pankaj_daga · December 17th, 2003, 05:13 PM

Hi there,

Actually here is a more clear description of what I am trying to do. Sorry to be vague earlier.

In a Select statement you can specify the columns that you want to display.
Example,

Select FirstName, LastName from PeopleTable.

What I am trying to do is this: I have a table (say PeopleTable) that has many columns...say LastName, FirstName, Data Of Birth, Occupation etc.

Now, I have another table that is filled by another process that tells me which columns to display: say, example, LastName, FirstName...

So what I wanted to do was:

SELECT (Select Values from AnotherTable) FROM PeopleTable

Select Values from AnotherTable would return LastName and FirstName and my query would be:

SELECT(LastName, FirstName) from PeopleTable. However, I guess SQL does not do such syntactic substitution.

Does anyone know a way to achieve this. I do not want to use a string and concatenate each of the choices, if there is a way to achieve this with SQL.

Thanks a lot

Sincerely,
Pankaj

Steven · December 17th, 2003, 07:01 PM

You'd have to turn to VBA for that. For example, you could create a recordset which goes through the records in your "AnotherTable" and creates a SQL statement from that, something along the lines of (and I've not tested this)....

Code:

Dim strSQL as String, rst as DAO.Recordset
strSQL = "SELECT "
set rst= CurrentDB.OpenRecordset("SELECT Field From AnotherTable")
rst.MoveFirst
While not rst.EOF
strSQL = strSQL & rst("Field") & ", "
rst.MoveNext
Wend
strSQL = Left(strSQL, Len(strSQL)-2)'Get rid of the trailing comma and space
strSQL = strSQL & " FROM PeopleTable"
Dim qdf as QueryDef
Set qdf = CurrentDB.CreateQueryDef("YourQuery", strSQL)

Steven

I am a loud man with a very large hat. This means I am in charge
(edited due to obvious typo)

jemacc · December 17th, 2003, 08:27 PM

It looks like you have to join your results and query those results sets. Do both tables have the similar data? Is the source of your second table the same as the first?

prasadvn82 · December 5th, 2005, 04:37 PM

jemacc is right!!!

<h1>prasad veluri</h1>

http://prasad-veluri.com

<h1>Narasimha Prasad Veluri</h1>