By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Acc97: Splitting tables

P: n/a
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
================================================== ==========

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Assumptions:
1 - the Split Table 1 already exists
2 - spelling for the colours and countries is consistent in all the
source records

I would create a no duplicates, two-field index on Split Table 1 to
prevent the creation of multiple entries a single colour for any
particular country. (eg, only one record can be Canada - Red)

Create three append queries to select records from the source table
where Colour1 is not null, Colour2 is not null, and Colour3 is not
null respectively. Append these records onto Split Table 1

Run the three queries in sequence. Repeat as often as required when
importing new data.

If you are comfortable using VBA, you can extract the SQL statements
underlying these three queries and create a new "generalized" query
that you build on-the-fly to deal with your 60 "colour" fields in the
real database.

(air code)

for i = 1 to 60
sSQL = "INSERT INTO SplitTable1 (Country, Colour) " & _
"SELECT Country, Colour" & i & " FROM Source " & _
"WHERE Colour" & i & " Is Not Null;"
currentdb.execute sSQL
next i
Finally, I would create a query that joined the two split tables based
on the ClrID, and showed the records that exist in the SplitTable1 but
that do not have a matching entry in SplitTable2 (ie, the colour
definitions). You do that by right-clicking on the "joining line" in
the query defintion, and chosing one of the "... all records in ..."
options.
On Sat, 21 Aug 2004 21:23:18 -0000, bookworm@no$pam.com (Gary Lynch)
wrote:
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
================================================= ===========

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Nov 13 '05 #2

P: n/a
Gary,

I have a further suggestion ---

Create a third table for languages.
TblLanguage
LanguageID
Language

Your color table then would look like:
TblColor
ClrID
LanguageID
Color

In your setup, you'll run into the same problem if you need to enter a new
language as you did to enter a new color.

PC Datasheet
"Gary Lynch" <bookworm@no$pam.com> wrote in message
news:10*************@corp.supernews.com...
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
================================================== ==========

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.