Country Data Merge

[chisholm.alexander]

Hi Everyong-

I've got a database with many tables, each containing different
economic data for many countries. I want to be able to have all the
data in one table.

The problem is that some tables have different countries.

Example:
Table 1:
Italy 454
Germany 342
France 342

Table 2:
Italy 34
Ireland 12
France 23
Spain 23

From this I would like a Table 3 similar to this:

Table 3:
Italy 454 34
Germany 342 -
France 343 23
Spain - 23


Does anyone have any advice???

Thanks

Alex

 

[John Vinson]

Hi Everyong-

I've got a database with many tables, each containing different
economic data for many countries. I want to be able to have all the
data in one table.

The problem is that some tables have different countries.

Example:
Table 1:
Italy 454
Germany 342
France 342

Table 2:
Italy 34
Ireland 12
France 23
Spain 23

If you have FIELDS named Italy, Germany, and so on, that's a real
problem. And if each Table contains a different type of economic data,
that's an even worse problem! Storing data in fieldnames and
tablenames is simply bad design, and not how relational databases are
designed to work.

Table 3:
Italy 454 34
Germany 342 -
France 343 23
Spain - 23

How about, instead, a properly normalized table with fields Country,
Datatype, Amount? I don't know what the numbers in Table1 and Table2
represent, but a normalized structure would have (e.g.)

Country Datatype Amount
Italy Table1Stuff 454
Italy Table2Stuff 34
Germany Table1Stuff 342
France Table2Stuff 23
France Table1stuff 342
Spain Table2Stuff 23

and so on. You can, given this structure, create a Crosstab Query
which will be exactly your desired output, using Country as the Row
Header and Datatype as the Column Header.

To migrate the data from your current spreadsheets into the normalized
table, use an Append query for each one:

INSERT INTO newtable ([Country], [Datatype], [Amount]
SELECT [Country], "Table1Stuff" AS Datatype, [Valuefield] AS Amount
FROM Table1;

with similar queries for your other tables.

John W. Vinson[MVP]

 

[Joseph Meehan]

Hi Everyong-

I've got a database with many tables, each containing different
economic data for many countries. I want to be able to have all the
data in one table.

The problem is that some tables have different countries.

Example:
Table 1:
Italy 454
Germany 342
France 342

Table 2:
Italy 34
Ireland 12
France 23
Spain 23

If you have FIELDS named Italy, Germany, and so on, that's a real
problem. And if each Table contains a different type of economic data,
that's an even worse problem! Storing data in fieldnames and
tablenames is simply bad design, and not how relational databases are
designed to work.

Table 3:
Italy 454 34
Germany 342 -
France 343 23
Spain - 23

How about, instead, a properly normalized table with fields Country,
Datatype, Amount? I don't know what the numbers in Table1 and Table2
represent, but a normalized structure would have (e.g.)

Country Datatype Amount
Italy Table1Stuff 454
Italy Table2Stuff 34
Germany Table1Stuff 342
France Table2Stuff 23
France Table1stuff 342
Spain Table2Stuff 23

and so on. You can, given this structure, create a Crosstab Query
which will be exactly your desired output, using Country as the Row
Header and Datatype as the Column Header.

To migrate the data from your current spreadsheets into the normalized
table, use an Append query for each one:

INSERT INTO newtable ([Country], [Datatype], [Amount]
SELECT [Country], "Table1Stuff" AS Datatype, [Valuefield] AS Amount
FROM Table1;

with similar queries for your other tables.

John W. Vinson[MVP]

Follow John's advice. Normalize now and do it right or mess around over
and over until you find out that normalizing is really easier and faster
than trying to make a database work like a spreadsheet. I know it seems
like the hard complex way, but Access likes it that way and can do all kinds
of things if you work with it.