Use table value for query criteria & table name

[inhops]

How do I use a value from a table as criteria in a query? I created a MakeTable query and converted it to SQL. Then I copied the code multiple times to create the multiple tables - see below. I have a table with the vendor names. How can I get the vendor names from the vendor table so I can put this into a loop for all vendors in the table?



DoCmd.RunSQL "SELECT table_data.SKU, table_data.Price, table.Vendor INTO MT__1 " & vbCrLf & "FROM table_data " & vbCrLf & "WHERE (((table_data.Vendor)=""MyVendor1""));"
DoCmd.RunSQL "SELECT table_data.SKU, table_data.Price, table.Vendor INTO MT__2 " & vbCrLf & "FROM table_data " & vbCrLf & "WHERE (((table_data.Vendor)=""MyVendor2""));"

Thank you for your help!

[June7]

Why are you making tables? Can't you just filter the original dataset with criteria in a query? You probably need to do a join of the tables in query. Review Access Help on building queries with table joins and filter criteria.

Like:
SELECT table_data.*, VenderName FROM table_data LEFT JOIN Vendors ON table_date.VendorID = Vendors.VendorID WHERE table_data.VendorID='MyVendor1';

[inhops]

I will need to do some calculations on the resulting data (i.e. which vendor has the best price). I could do this with multiple queries (without making tables) but I was trying to avoid having queries run queries - due to the quantity of data I am dealing with. How do I get the vendor name from the first record of my vendor name table as the criteria in my query? And repeat that for each vendor?

Thanks for your help.

[June7]

"How do I get the vendor name from the first record of my vendor name table as the criteria in my query? And repeat that for each vendor?"

Don't really understand this request. What is the output you need? I suspect you need to do Totals (aggregate) query. Or better, build a report that groups and summarizes the data by vendor.

Why is queries running queries an issue? How much data?

[inhops]

I believe that I figured out one way (maybe not the best) to do it. I have a control on a form that is linked to my vendor name table. I created the query using this form's field. Which works fine when the user selects a field. But to run the query for each of these vendor names, I need to loop through the vendor name table setting the form's field to each record's vendor name. This creates the queries I need so I can use these tables to compare the prices from the vendors.

[June7]

So this is solved or still need assistance?