I am looking for a simple solution to a recurrent problem
with imported data in Access 97. The example below is a
simplification of a problem with a much larger database.
Let's say I start out with a single table, describing the
colors of 4 national flags. I have 4 fields:
Country Text
1stColor Text
2ndColor Text
3rdColor Text
Inside I have 4 records, listing the colors of 4 countries'
flags. If a flag has fewer than 3 colors, I leave the later
fields blank (.):
Country 1stColor 2ndColor 3rdColor
--------- -------- -------- --------
US red white blue
Germany black red orange
Colombia yellow blue .
Honduras blue white .
This is undesireable for several reasons:
- It wastes space with all the duplicate entries for
identical colors and unused fields,
- I sometimes need to print a report where all the colors
are in a language besides English,
- Today I am creating an entry for South Africa and need 6
colors. I must switch to design view and change the
structure of the table, and waste even more space. I
might have to do this for every entry from now on.
I decide I want to split the table into two. The new
database would look like this:
Split table 1: Flag | Split table 2: Color
|
Country ClrID | ClrID English Spanish German
--------- ----- | ----- ------- ------- ------
US RED | RED red rojo rot
US WHT | WHT white blanco weiss
US BLU | BLU blue azul blau
Germany BLK | BLK black negro schwarz
Germany RED | ORG orange naranja orange
Germany ORG | YEL yellow amarillo gelb
Colombia YEL |
Colombia BLU |
Honduras BLU |
Honduras WHT |
The first table now has a record for every color of each
country, which indexes via key ClrID into the second table,
having one entry for each color. I can add new colors
easily and new languages with a little more effort and have
no upper limit on the number of colors in a given flag.
My request is for the simplest sequence to create the split
tables from the flat file. The table analyzer has no ability
to break a record with a key and 3 fields into 3 records of
one key and one field (I can add the other language fields
later). I tried creating a make-table query for the 1stColor
field, and 2 append-query fields for the 2nd- and 3rdColor,
but can't figure out how to re-synchronize the keys (other
than manual editing) between files. And in my real database,
the flat file records can hold up to 60 "color" fields,
causing alot of coding.
Suggestions appreciated.
================================================== ==========
Gary | To send mail, change
bookworm@no$pam.com | my domain name from no$spam
| to execpc
================================================== ==========