473,325 Members | 2,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Acc97: Splitting tables

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
2 2071
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: robsom | last post by:
Hi, I have a problem with a small python program I'm trying to write and I hope somebody may help me. I'm working on tables of this kind: CGA 1988 06 21 13 48 G500-050 D 509.62 J.. R1 1993 01...
6
by: Earl Anderson | last post by:
I have a A97/XP applet I've developed for my own use in my department. My boss "suggests" that since I built it, I share it with and instruct the other 6 members of my department on its use. I've...
2
by: Tony Ciconte | last post by:
Iam trying to distribute a runtime Acc97 program using the ODE, Wise Installer, and SageKey scripts. Everything has been working great until I recently tried to include the MSINET.OXC internet...
20
by: Ed | last post by:
I am running Access 2002 and just ran the built in Access wizard for splitting a database into a back end (with tables) and front end (with queries, forms, modules, etc.). After running the...
3
by: David Holmes | last post by:
I have been developing a commercial Access Application for some four years now, the mde file is over 30Mb without data, I have recently been approached to make some additions, but due to my poor...
7
by: amazo | last post by:
Dear all, A new network has been installed on a customer and I get some troubles to do specific things; I will explain: The infraestructure is as follows: .. Server: Windows Server 2003 .....
1
by: donhyams | last post by:
It seems like at one time I knew how to do this, but right now I'm drawing a blank. I know there has to be an easier way to link tables using more than one field. Heres a simplfication of the...
1
by: CaptainWillard | last post by:
Is there any difference between splitting the .mdb vs. linking a table in another .mdb file ? For example, if I use the database splitter tool I end up with Widgets.mdb and Widgets_be.mdb. All the...
2
by: planetthoughtful | last post by:
Hi All, I'm building some reports in Acc97 and using a custom calendar form to allow users to pick dates with which to report. I'm wondering if there's an easy way in code to be able to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.