Creating Tables from a grouped recordset

[Chris via AccessMonster.com]

I have done something like this a million years ago my developer skills have diminished to nothing but now I am at a new job trying to regroup what I lost. This one came across my desk recently.

I need to create tables based on grouped recordsets the additional challenge is that the table name would equal the value or variable of the grouped recordset. Example below

Field_1 Field_2 Field_3
em***@email.com Boston MA
em****@email2.com Boston MA
em****@email3.com NY NY
em****@email4.com NY NY

In this case I would need an automated procedure to create two tables based on the grouped recordset in field 3. So one table called MA and one table called NY. In my app the values in Field3 are always variable so that eliminates using fixed names.

The last time I did this we used a Do while procedure declaring the variable calling the recordset with a SQl string. For some reason I just can't piece this together.

--
Message posted via http://www.accessmonster.com

[Alan Webb]

Chris,
Why bother with a database in that case? Access and other similar database
software is really designed as a container for information that has defined
and unchanging attributes and entities. Were I the architect of your
database I'd say you had three attributes of your entity. The first is
e-mail address, the second is City, and the third is State.
But, you can create tables from any select statement using SELECT . . .
INTO . . . FROM . . . It's documented in the help files if you look for
it.
The way it sounds, though, you don't really need a relational database but
perhaps should be creating data files in VB that do what you want.
"Chris via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:b6******************************@AccessMonste r.com...

I have done something like this a million years ago my developer skills
have diminished to nothing but now I am at a new job trying to regroup what
I lost. This one came across my desk recently.

I need to create tables based on grouped recordsets the additional
challenge is that the table name would equal the value or variable of the
grouped recordset. Example below

Field_1 Field_2 Field_3
em***@email.com Boston MA
em****@email2.com Boston MA
em****@email3.com NY NY
em****@email4.com NY NY

In this case I would need an automated procedure to create two tables
based on the grouped recordset in field 3. So one table called MA and one
table called NY. In my app the values in Field3 are always variable so
that eliminates using fixed names.

The last time I did this we used a Do while procedure declaring the
variable calling the recordset with a SQl string. For some reason I just
can't piece this together.

--
Message posted via http://www.accessmonster.com

[Chris Arenburg via AccessMonster.com]

Thanks for the quick response

I know what you are saying but perhaps I should be more specific of why I need to attempt this in Access. The tables or files I am trying to create are the source for a List Manager that displays these emails on the web the page is looking at a SQL server.

The list comes from Access and that is the only tool available to me right now... This is the reason I am doing it this way. I have done the same thing recently but have relied on DTS in SQL Enterprise manager, however that tool is not available to me right now. Also I have to compress this infromation as email addresses are duplicated several times and there are other variables involved.

So let me try to simplify the need the table creation should be automated based on the group variable with the name of the table being the same variable as the goup.

It's not the creating a table based on grouped records that I am having the problem with. It's the automation and using the grouping as well as (in example case Field 3) as the variable that automatically becomes the table name

--
Message posted via http://www.accessmonster.com