I have records in a table that I want to use as part of a where clause of an SQL query. How do you do that.

My table is named 'tblReportingCoSelected' and has only 1 field called "Reporting_Co_Name".

The SQL statement will be something like "Select * from ThisTable where Reporting_Co_Name in ........

Thanks!

Though you could use the In clause, such as:

Select * from ThisTable where Reporting_Co_Name In (SELECT Reporting_Co_Name FROM tblReportingCoSelected)

It's less efficient in Jet than joining (Jet is better optimised for joins).

Select T.*
FROM ThisTable T INNER JOIN tblReportingCoSelected R
ON T.Reporting_Co_Name = R.Reporting_Co_Name

Cheers

hmmm...that didn't work...

Here is what I tried:

strSQL = "Select * from tblData " & _
"Where Flow_Date between # " & Me.fldBegin & " # And # " & Me.fldEnd & " # " & _
"Reporting_Company in (SELECT Reporting_Co_Name FROM tblReportingCoSelected)"

Well if you have other criteria then it needs to be included - added together logically

strSQL = "Select * from tblData " & _
"Where Flow_Date between # " & Me.fldBegin & " # And # " & Me.fldEnd & " # " & _
" AND Reporting_Company in (SELECT Reporting_Co_Name FROM tblReportingCoSelected)"

The same would work using the join.

oh whoops...what a bone head! (me that is)... Thanks, I will try it after dinner!

Thanks that worked... But one more please.

I have a temp table (tblReportingCoSelected) that has some records in it (Reporting_Company). I want to write all of those records into another table (tblSavedQueryReportingCompany) along with a PK (that I can determine). So if there are 3 records in the temp table I want to write those 3 records to tblSavedQueryReportingCompany along with the same PK that I got from another table. So that table would look like:

PK Reporting_Co_Name
3 CENTERPOINT ENERGY MISSISSIPPI RIVER TRANS CORP
3 CHANDELEUR PIPE LINE CO
3 COLUMBIA GULF TRANSMISSION CO

Basically, Im trying to develop a way to save the users criteria for a query so that they can select that query later and re run it.

I attached the db if you want to take a look at it to see what Im talking about!

Thanks!