Acc97: Splitting tables

**Jack MacDonald** · Nov 13 '05, 02:55 AM

Re: Acc97: Splitting tables

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 "generalize d" 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.execu te 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:
[color=blue]
>I am looking for a simple solution to a recurrent problem
>with imported data in Access 97. The example below is a
>simplificati on 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$pa m.com | my domain name from no$spam
> | to execpc
>============== =============== =============== =============== =[/color]

*************** *******
jackmacMACdonal d@telusTELUS.ne t
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

**Steve** · Nov 13 '05, 02:55 AM

Re: Acc97: Splitting tables

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$pa m.com> wrote in message
news:10iffa61c0 pgjbe@corp.supe rnews.com...[color=blue]
> 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
> =============== =============== =============== ===============
>[/color]

Acc97: Splitting tables

Acc97: Splitting tables

Comment

Comment