f



MS Access VBA making multiple tables in database from a main table.

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
=85

Is this doable automatically in MS Access?

Many thanks.

brenda
0
bbcdancer (5)
10/9/2008 3:25:39 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

1 Replies
1515 Views

Similar Articles

[PageSpeed] 50

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@hotmail.com> wrote in message 
news:495c4ca6-4c59-4315-abc3-31e9b75a0c69@25g2000prz.googlegroups.com...
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 


0
phil126 (320)
10/9/2008 8:58:01 PM
Reply: