Automating a query

[harrisw]

I have two tables.

First table holds the data.
Second table holds criteria ie activity of business, number of employees.

Instead of having a query and changing the criteria in it manually to match the second table how can I do it so that it will read each line of the second table and query the first table and then append the results to a third table.?

Any help appreciated.

 

[charityg]

Create a new module then use this code (modify the table and field names)

Public Sub AppendRecords()


Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef

Set db = CurrentDb
Set rst = db.OpenRecordset("table2", dbOpenSnapshot)

rst.MoveFirst
Do Until rst.EOF
Set qdf = db.CreateQueryDef("", "INSERT INTO table3 SELECT Table1.* FROM Table1 where table1.[Activity of Business]='" & rst![Activity of Business] & "' and table1.[Number of Employees] =" & rst![Number of Employees])
qdf.Execute
rst.MoveNext
Loop
End Sub

Then call this procedure whenever you want to execute the queries.

 

[Pat Hartman]

Add both table1 and table2 to the QBE grid. Join them on the appropriate columns. Select whatever columns you want from each table. You can use this query as the recordsource for a form or report or in the TransferText Method to export a recordset so there should be no need to actually make a third table. If you insist on making a new table, just save the query as a make-table query.

 

[harrisw]

charityg

Thanks for that, How would I change the SQL statement if I had a 3rd column in table 2 that said how many records I wanted to return?

What would it do if the recordset didn't reach the number of records required.?

Thanks Again

 

[harrisw]

Just tried the code but I keep getting "data type mismatch errors". All the fields in the tables are test if this helps.

Thanks again

 

[charityg]

Please submit the table structures for both of your tables. Field names, and types, and the fields you want to add to table3. After I can get a better view of what you require I'll post back and include how to return the predefined number of records.

Or you may email me your database if it doesn't contain any sensitive data.

 

[harrisw]

As the data is of a sensitive nature I can only send you the database with the 3 tables (without data)

Can you pm me your email address?

 

[harrisw]

I've got an access 97 database ready for you.

Still not got a PM with your email.

Cheers

 

[charityg]

I sent you a pm. Doesn't matter, email it to me at
cgaborik@christensenfarms.com