One single query resulting in multiple tables, is that possible?

[khowailedg]

Hello,

I appreciate your time and effort. I have 2 tables;



1. Sponsors table about 50 records
2. Contractor table about 3000 records. Each contractor has a parent sponsor

I would like to query specific contractors field for each sponsor I can use the sponsor name as the criteria and run it 50 times but that's time consuming. Do you know of any other way to do this? Pretty much for every unique ID in the sponsors table create a separate table with the selected contractors information.

THANKS for your help

[JoeM]

What is your end goal here? What exactly are you intending to do with these 50 tables (or queries)?
I am not sure I see the advantage to creating 50 tables.

[khowailedg]

I than would export these 50 tables to excel and send it to individual sponsors to review, edit and complete.

[JoeM]

I don't think you would save much by doing that. Either way, you are probably going to need a loop, either to create and populate 50 tables, or a loop to export the query with 50 different criteria.

I am thinking you could use Recordsets, and loop through your Sponsors table, updating the criteria of your query to match that particular Sponsor, export the data, then continue on to the next one.

[khowailedg]

Thanks, what is the Recordsets?

[JoeM]

If you Google something like "Access programming with DAO Recordsets", you should find lots of information.

When I get more time, I may be able to work up an example, but I would need to know the structure of your Sponsors table, and what the SQL code of the query you want to export looks like.