Use table fields as records

[Alberto Comino]

Can I use the fields I have in a table as recors in another table?

I have a large table that is organized like this:

Date Product France Germany Italy
12/10/08 A 1000 2500 3000
13/10/08 A 2300 3500 1500
14/10/08 B 750 5000 1700
15/10/08 B 1200 6500 1000
16/10/08 B 4000 4200 1200



What I would like is to reorganize the data in another table so that the
'countries' are records and not fields. It would be somethis like this:

Date Product Country Value
12/10/08 A France 1000
13/10/08 A France 2300
14/10/08 B France 750
15/10/08 B France 1200
16/10/08 B France 4000
12/10/08 A Germany 2500
13/10/08 A Germany 3500
14/10/08 B Germany 5000
15/10/08 B Germany 6500
16/10/08 B Germany 4200
12/10/08 A Italy 3000
13/10/08 A Italy 1500
14/10/08 B Italy 1700
15/10/08 B Italy 1000
16/10/08 B Italy 1200


Thanks

Alberto

 

[tina]

yes. in fact, the first table design you posted is "spreadsheet" style, not
normalized; while the second table is a correctly designed table for a
relational database.

hth

 

[John W. Vinson]

Can I use the fields I have in a table as recors in another table?

I have a large table that is organized like this:

Date Product France Germany Italy
12/10/08 A 1000 2500 3000
13/10/08 A 2300 3500 1500
14/10/08 B 750 5000 1700
15/10/08 B 1200 6500 1000
16/10/08 B 4000 4200 1200

Well, that's a good spreadsheet, but not a good table.

What I would like is to reorganize the data in another table so that the
'countries' are records and not fields. It would be somethis like this:

EXCELLENT idea! You can always go back to the spreadsheet view using a
Crosstab query.

What you need is what's called a "Normalizing Union Query". First, create your
properly normalized table (empty), with fields for SaleDate (don't use Date as
a fieldname, it's a reserved word), Product, Country and Value. Then use the
SQL window to create a query:

SELECT yourtable.[Date], yourtable.Product, "France" AS Country, [France] AS
Value
FROM yourtable
WHERE [France] IS NOT NULL
UNION ALL
SELECT [Date], [Product], "Germany", [Germany]
FROM yourtable
WHERE [Germany] IS NOT NULL
UNION ALL
SELECT [Date], [Product], "Italy", [Italy]
FROM yourtable
WHERE [Italy] IS NOT NULL;

Continue the same style with other countries if you have them.

Then save this query as uniNormalize, and then create a new Append query to
append from uniNormalize into your new table.

 

[Alberto Comino]

Thanks John. I know the data is not organized properly as a table forAccess,
that's why I want to have it reorganized. The problem is that indeed the
date sources are multiple Excel spreadsheets which I cannot modify as they
are related to the users workflows.

Now solution of making a union query works fine only if I have a limited
number of countries as in my example. However in the real tables I have a
huge number of countries -50 or so-. Do I have to declare one by one them
manually or is there a way to add them automatically?

Can I use the fields I have in a table as recors in another table?

I have a large table that is organized like this:

Date Product France Germany Italy
12/10/08 A 1000 2500 3000
13/10/08 A 2300 3500 1500
14/10/08 B 750 5000 1700
15/10/08 B 1200 6500 1000
16/10/08 B 4000 4200 1200

Well, that's a good spreadsheet, but not a good table.

What I would like is to reorganize the data in another table so that the
'countries' are records and not fields. It would be somethis like this:

EXCELLENT idea! You can always go back to the spreadsheet view using a
Crosstab query.

What you need is what's called a "Normalizing Union Query". First, create
your
properly normalized table (empty), with fields for SaleDate (don't use
Date as
a fieldname, it's a reserved word), Product, Country and Value. Then use
the
SQL window to create a query:

SELECT yourtable.[Date], yourtable.Product, "France" AS Country, [France]
AS
Value
FROM yourtable
WHERE [France] IS NOT NULL
UNION ALL
SELECT [Date], [Product], "Germany", [Germany]
FROM yourtable
WHERE [Germany] IS NOT NULL
UNION ALL
SELECT [Date], [Product], "Italy", [Italy]
FROM yourtable
WHERE [Italy] IS NOT NULL;

Continue the same style with other countries if you have them.

Then save this query as uniNormalize, and then create a new Append query
to
append from uniNormalize into your new table.

 

[John W. Vinson]

Thanks John. I know the data is not organized properly as a table forAccess,
that's why I want to have it reorganized. The problem is that indeed the
date sources are multiple Excel spreadsheets which I cannot modify as they
are related to the users workflows.

That's what I suspected... my sympathy!!

Now solution of making a union query works fine only if I have a limited
number of countries as in my example. However in the real tables I have a
huge number of countries -50 or so-. Do I have to declare one by one them
manually or is there a way to add them automatically?

Well, that can be a pain. There are a couple of alternatives, both rather
laborious. You could construct a huge UNION query (or more likely two or three
of them, in that you will probably run into the QUERY TOO COMPLEX error when
the size of the query exceeds 64KBytes). This can be made a bit less laborious
by copying and pasting the SQL of each SELECT clause and editing the
countryname. If there will never be NULL values in the value (or if you don't
mind creating records with null values), you can skip the WHERE clause.

Or you could write VBA code to open a recordset based on your spreadsheet,
loop through its Fields collection, and output to a recordset based on the
normalized table.

Do the field layouts and sets of countries vary from spreadsheet to
spreadsheet?

 

[Alberto Comino]

No, the layout of the spreadsheets is always the same. What it could happen
however is that they add a new country, but it would be done in all the
excels.

I'll give it a try in VBA. I thought there would be an easier solution by
making a query -the huge union seems indeed cumbersome.

Thanks anyhow for the prompt weekend help.