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

On 16 Oct 2006 20:23:03 -0700, (E-Mail Removed) wrote:

>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.

>>From this I would like a Table 3 similar to this:
>
>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]

John Vinson wrote:
> On 16 Oct 2006 20:23:03 -0700, (E-Mail Removed) wrote:
>
>> 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.
>
>>> From this I would like a Table 3 similar to this:
>>
>> 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. :-)

--
Joseph Meehan

Dia duit