Make Multiple Tables Using Parameters from List

[magicarty]

I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed. Thoughts?

Many thanks.

 

[MarkK]

The name of a person is data, not structure. If doesn't make sense to name a table after data. By that logic in an accounting system we'd need tables tbl21.95, tbl21.96, and tbl21.97, which doesn't make sense. That data goes in a field in a table, and the table is named to represent the TYPE of thing the table describes.

Maybe you need a table called tPerson with a Firstname field.

 

[magicarty]

Let me try and be more clear...

I currently have Access use a pull through query to grab about 60 columns and about a million rows of data from our data warehouse via ODBC connection. Then I basically use Access as a pivot cache for an Excel doc to make everything prettier and easier to use for my customers. Unfortunately, they "need" all of this subscriber data to make sales calls and run other reports, so I can't really limit it any more. What I CAN do to make the pivot tables cache the data faster is break up the million rows into smaller table chunks by Associate Director, and get about 100k instead as I've been getting complaints that the reporting is too slow (which it is). My thought was to have Access run the same query that builds the 1M row table, but then in the where clause, it would use each AD's name from a list (table) and chunk out the 100k tables instead. The net data size is the same, but with the smaller pivot caches, it should run much faster. Everything is automated using the old method, but of course it takes hours and I was looking for help on how to accomplish using vba or whatever to try my new idea.

What's the better way to do this?

 

[MarkK]

So the first problem is that you have a table that is very big, 1 million + rows, and reporting on the data in that table is too slow? If so, have you indexed the fields that you use to search or sort?

 

[magicarty]

I haven't, but how much faster would that make it? Do Excel pivots care about indexes in Access databases? I'd probably have to do like 15 of them because of how many ways people may want to spin the data?

 

[MarkK]

Indexing makes a dramatic difference in speeding up retrieval times. Searching and sorting are not trivial matters particularly in large sets of data, and if fields are indexed they are essentially pre-sorted. And consider that it's most efficient, by far, to search a list that is already sorted and any filter is a sort and a search. If you don't have indexes and you run a search on a few fields, the system has to re-sort ALL your data EVERYTIME you query it.

Before you chop up your tables like that add indexes and see how that works out . . .

 

[way2bord]

Indexing makes a dramatic difference in speeding up retrieval times. Searching and sorting are not trivial matters particularly in large sets of data, and if fields are indexed they are essentially pre-sorted. And consider that it's most efficient, by far, to search a list that is already sorted and any filter is a sort and a search. If you don't have indexes and you run a search on a few fields, the system has to re-sort ALL your data EVERYTIME you query it.

Before you chop up your tables like that add indexes and see how that works out . . .

If I'm not mistaken - adding indexes improves data communication speeds, but increases file size.

Adding indexes to all fields for a multimillion row data file may well blow up an access database (max file size ~2GB).

Keep an eye on your file size when you start adding indexes.

 

[MarkK]

It makes sense that the file size would increase. And the time required to save edits and new records will increase too.

 

[ECEstudent]

Hi. I have a problem with my vba code and I would appreciate any help I can get!

What my code is supposed to be doing is taking in a user input (example: 38A1018X002) searching Table1 for it's associated value (ECX29-C7) Then taking in that found value and looking it up in a different table. I know how to do all of that no problem. My problem is that the value found is just PART of the new value that I would be looking for in a different table. For example: I would be looking up ECX29-C7 in a table field that contains values like:

ECX29-C7-D9-E6
ECX29-C8-U9
ECX30-C7-K5
ECX29-C7
.
.
.
[And so on]

What I need it to return in this example is ECX29-C7-D9-E6 AND ECX29-C7

I am just stuck on this one part. It needs to be in VBA. I can't do a query with this just because everything else I have is in code and this would be SO much simpler. Please help

 

[MarkK]

Wow that's bad form ECEstudent. You started a thread already with exactly this same request, so that's a double post, and you hijack this thread???? You think your need is way more important than someone else's??

 

[magicarty]

Seriously. Anyway, I'm working on trying to index where I can right now. I assume that since this is a make table query by way of a pass through, I have to run another query to create the indexes on the table after-the-fact? What's the structure? I was assuming something like CREATE INDEX on table_name... something something?

 

[ECEstudent]

Whoa! My bad. Relax. First time here. Posted in the wrong place.
By the way thanks for the reply! Very helpful

 

[magicarty]

OK, so it took fore.ev.er but I tried adding a ton of indexes and such. Problem is when you access the pivot from Excel, it still takes just as long (I think) because it still has to re-look at the data from the pivotcache as it's no longer looking at the data connection. In my current setup, I have Access kill that huge table after all the pivots are updated anyway.

So back to my original question. Instead of creating separate make table queries that use the pass through queries (so now I have 12 make tables and 12 pass through) for each Associate Director, I want to have it automatically create the 12 tables needed through code so that it looks at a table that has the 12 AD names already in it, and runs the exact same query 12 times, but uses each individual's name in the where clause of the pass through.

Make sense? I'm sure this is doable, but I just don't have the knowledge.

 

[way2bord]

OK, so it took fore.ev.er but I tried adding a ton of indexes and such. Problem is when you access the pivot from Excel, it still takes just as long (I think) because it still has to re-look at the data from the pivotcache as it's no longer looking at the data connection. In my current setup, I have Access kill that huge table after all the pivots are updated anyway.

So back to my original question. Instead of creating separate make table queries that use the pass through queries (so now I have 12 make tables and 12 pass through) for each Associate Director, I want to have it automatically create the 12 tables needed through code so that it looks at a table that has the 12 AD names already in it, and runs the exact same query 12 times, but uses each individual's name in the where clause of the pass through.

Make sense? I'm sure this is doable, but I just don't have the knowledge.

You could probably run a basic loop through the recordset?

Pseudocode to add to a random button on a random form ---

set Rs = currentDB.openrecordset ("Select ADNames From ADTable")

Do While Not Rs.EOS
strTableName = "t_" & Rs("ADNames")
'create table
strSQL = "CreateTable " & strTableName & "..."
DoCmd.RunSQL strSQL

'update using old table
strSQL = "SELECT * INTO " & strTableName & "FROM ..."
Rs.MoveNext
Loop