Make table loop?

23 Feb 2005 3:52 PM

Daniel

Hi,

I have a very large table that I want to split into
chunks so I an paste it into excel. The table is lookup
table it onlt has two colmns but is massive. The two cols
are code and description. What I would like to do is
create some sql in VBA that will loop round and create a
multiple tables based on a set criteria. The criteria
would be on the codes bit of the table. I know that the
codes start with a letter, from A to Z, all the A's are a
prticulr group, as are all the B's etc.so that wouold be
a good way of breaking it down, does anybody know of a
way to do this, otherwise I will just do lots of make
table queries.

Thanks

24 Feb 2005 12:53 PM

Nikos Yannacopoulos

Daniel,

Try something like:

For i = 65 To 90
     strSQL = "SELECT * INTO " & Chr(i) & "_CHUNK"
     strSQL = strSQL & " FROM MyTable"
     strSQL = strSQL & " WHERE IDField Like '" & Chr(i) & "*'"
     CurrentDb.Execute strSQL
Next

HTH,
Nikos

Daniel wrote:
Show quote

> Hi,
>
> I have a very large table that I want to split into
> chunks so I an paste it into excel. The table is lookup
> table it onlt has two colmns but is massive. The two cols
> are code and description. What I would like to do is
> create some sql in VBA that will loop round and create a
> multiple tables based on a set criteria. The criteria
> would be on the codes bit of the table. I know that the
> codes start with a letter, from A to Z, all the A's are a
> prticulr group, as are all the B's etc.so that wouold be
> a good way of breaking it down, does anybody know of a
> way to do this, otherwise I will just do lots of make
> table queries.
>
> Thanks

24 Feb 2005 1:00 PM

Jeff Boyce

Daniel

"Massive" has different meanings to different folks ... have you already
tried exporting the table to Excel?

Have you considered using a query instead of making temporary tables? You
could build a new query that gets all the rows where the code is:

Like A*

When that query returns the rows you want, export THAT query's output to
Excel. Modify the query to find the codes starting with B (Like B*),
export, and so on.

While this would take 26 "cycles", if you are only doing this one time, it
would probably take less time than building/testing/running vb code.

--
Good luck

Jeff Boyce
<Access MVP>

Show quote

"Daniel" <anonym***@discussions.microsoft.com> wrote in message
news:173701c519bf$b600c980$a501280a@phx.gbl...
> Hi,
>
> I have a very large table that I want to split into
> chunks so I an paste it into excel. The table is lookup
> table it onlt has two colmns but is massive. The two cols
> are code and description. What I would like to do is
> create some sql in VBA that will loop round and create a
> multiple tables based on a set criteria. The criteria
> would be on the codes bit of the table. I know that the
> codes start with a letter, from A to Z, all the A's are a
> prticulr group, as are all the B's etc.so that wouold be
> a good way of breaking it down, does anybody know of a
> way to do this, otherwise I will just do lots of make
> table queries.
>
> Thanks