I have a database table called "tbl_personal" and struggling to VBA
program a bit of code to
create multiple tables in a database from the main "tbl_personal"
table.

In this case the 'color' column is the field I would like to look at.

1. Identify the number of unique records in column 'color'

2. For each color record that is label as red in the "tbl_personal"
table, create a table
called 'red' and populate with all red records in from the
"tbl_personal" table.
3.Then move onto the next unique color and repeat the whole process
again.
4. When all unique color tables are created in MS Access database end
with a msg "batch process done"

name surname age color country
aa aaa 1 red UK
bb bbb 10 green USA
cc ccc 20 blue China
dd ddd 30 red Germany
ee eee 40 green France
ff fff 1 blue Italy
gg ggg 20 red Spain
hh hhh 40 yellow Canada
ii iii 34 black Italy
…

Is this doable automatically in MS Access?

Many thanks.

brenda

Are you sure you want to do this?

I would expect 3 tables vis:
Table of Personel with PersonelID, Suname, FirstName, ColourID, CountryID &
DateOfBirth (Age will be out of date next birthday and should be
calculated),
Table of Colours ColourID & Colour
Table of Countries CountryID & Country.

You can then use simple queries to return all the red people or people who
live in UK.

Am I missing something?

Phil

<bbcdancer (AT) hotmail (DOT) com> wrote

I have a database table called "tbl_personal" and struggling to VBA
program a bit of code to
create multiple tables in a database from the main "tbl_personal"
table.

In this case the 'color' column is the field I would like to look at.

1. Identify the number of unique records in column 'color'

2. For each color record that is label as red in the "tbl_personal"
table, create a table
called 'red' and populate with all red records in from the
"tbl_personal" table.
3.Then move onto the next unique color and repeat the whole process
again.
4. When all unique color tables are created in MS Access database end
with a msg "batch process done"

name surname age color country
aa aaa 1 red UK
bb bbb 10 green USA
cc ccc 20 blue China
dd ddd 30 red Germany
ee eee 40 green France
ff fff 1 blue Italy
gg ggg 20 red Spain
hh hhh 40 yellow Canada
ii iii 34 black Italy
…

Is this doable automatically in MS Access?

Many thanks.

brenda