Is this possible with SQL?

[NJudson]

I have an SQL statement which basically filters out a table to display a particular record.

SELECT * FROM Table1 WHERE [MTX] = '" & MTX & "' AND [Freq] = '" & VCH & "'

This filters the recordset but what if I want to work with a field from the SQL result? Is this possible or is it more of a display or read-only result. I don't have much experience with sql so I'm not sure what works or what doesn't.

My goal is to loop through a table2 and for each recordset I set MTX = Table2.Field(0) and VCH = Table2.Field(2). Then filter out Table1 Where Table1.[MTX] = MTX And Table1.[Freq] = VCH. When the Filter returns the result I then want to make Table2.Field(1) = Table1.[Channel]. There may be up to 10,000 recordsets in table2 to loop through so my code the I'm currently using with nested loops and nested if then statements is highly ineffiecent and slow. One of may biggest things I don't understand is how to work with the results of SQL statements. I'm using Access 2k. Thanks for anyone taking the time to look at this.

 

[RichMorrison]

I don't follow all your process requirements, but that's OK.

In general you can create a Recordset from the result of an SQL statement. Then you can use all rows and fields in the Recordset for additional processes.

But first I would rethink the requirements. Writing a lot of code to manipulate numerous tables is often a sign of incomplete analysis.

Good data design and careful use of queries is most often better than writing lots of DAO or ADO code.

RichM

 

[NJudson]

I'm not sure If I'm on the right track or not but from my confusing original post I was trying to say that I want to filter out Table1 to find the recordset WHERE

Table1.[MTX] = Table2.Field(0) And Table1.[Freq] = Table2.Field(2)

From this recordset result I want to take Table1.[Channel] and basicaly do a
With Table2
.Edit
.Field(1) = Table1.[Channel]
.Update
End With

Here's what I was working on but not sure if it'll work and I'm having errors in the SQL.

MTX = Table.[MTX]
VCH = Table.[Freq]
SQL = "SELECT VCHINV.Field2" & _
"FROM VCHINV" & _
"WHERE (((VCHINV.Field1) = '" & MTX & "') And ((VCHINV.Field3) = '" & VCH & "'));"
Channel = Docmd.SQL (SQL)

Then if that's feasible edit the Table2 field as shown above with the With Statement. Thanks.

 

[NJudson]

I got it working. Thanks again RichMorrison. I took a few steps back and re-thought my problem and came up with this solution (with the help of another post on this forum).

MTX = recWizardCatsLayersAndCarFilesCombined![MTX]
Freq = recWizardCatsLayersAndCarFilesCombined![Freq]
SQL = "SELECT [Field2] FROM VCHINV WHERE (([Field1] = '" & MTX & "') And ([Field3] = '" & Freq & "'))"
Set recChannel = dbs.OpenRecordset(SQL)

Then recChannel.Field(0) is the value that I'm looking for. What may have taken 15-20+ minutes to run with nested loops and if statements now takes 30-45 seconds to run. God bless SQL! I hope to learn more of it so I can go back on some of the code I've written over past year and update it with SQL.