Printable View
With Acc 2007 I have a table of Well Spots. This includes lat/long for the location of wells.
There are 135000 records of wells in 55 counties.
I'd like to automagically do a query (or something) to MakeTable for each county with the wells in it. The table has 27 fields in each record (County name, county number, lat, long, etc).
We could do a loop using the county number to key on to create the next table.
Would we use a macro to execute sql query, or how?
Before helping you create 55 separate tables, I would like to know what the reason for separating this one table into 55 would be. I do know, that more than likely, if you are trying to work with 55 tables rather than just one table, you will be making about anything you want to do many times more difficult. So, the real question is this: What would having 55 separate tables do for you that you can not do with one table?
The tables will be exported to Arcview. I can handle them.
Knowing my application, it will make things easier, not more difficult.
More time for a better answer now. I'm using Access to break up the large table in to 55 more manageable bites. When I create the wellspots in Arcview, I don't have to load the entire state's wells at one time. That slows down the refreshes when I move the map even a little bit. When I'm working on one county's worth, I don't need the other counties wells loaded.
It also helps debug. I had some wells show up in South America. Narrowing down the table that it's in is easier using only one county at a time.
Then write one query, give the county number/code a parameter on the criteria row that is pointing to a field on a form. Then, in code for a button on that form, read the table that has the 55 counties in it. If you don't have a table with the 55 counties in it, you can do a summary sort on the county number/code and use that query as the "table" to read, one record at a time. After reading the first record in the table/query, put that county number/code into the field referenced in the original query. After writing the county number/code into the field, then run the original query. It should be a MakeTable query. After the table is made, that is, after the query is finished, have you code rename that new table to be the table name you need it to be considering the county that is in that table. Then loop to read the next record from your 55 county table/query. That will output all the tables you are looking for.
Go back and read this carefully, making a step for each step I have outlined in the narration, and I think you can follow it that way.
Any questions, ask away. But I would suggest working with this first so you have an idea of what is happening before asking questions.
The well table already has a field with the appropriate county number in it. And I already have a table with just county names and numbers in it.
I figured the well table did have the appropriate county number in it. That would be the way to capture all the records for just one county. That is why the original query that will be used to make each of the 55 county tables needs a parameter in the criteria row of the county number field that is pointing to a field on a form. That field on "a form" is the field that your VBA code will update while reading, one record at a time, a table (or query) that has 55 records in it, one for each county. By moving that county number from the table (or query) into the field on the form, then running the query that will be a Make Table query, you can make a table for each county.