Full Version: How to retrieve records in a table and use results in SQL
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
TheOtherDodge
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!
LPurvis
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
TheOtherDodge
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)"
LPurvis
Well if you have other criteria then it needs to be included - added together logically
trSQL = "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.
TheOtherDodge
oh whoops...what a bone head! (me that is)... Thanks, I will try it after dinner!
TheOtherDodge
Thanks that worked... But one more please.
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.
Oattached the db if you want to take a look at it to see what Im talking about!
Thanks!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.