import crosstab excel-file

quacka

Registered User.
Local time
Today, 10:53
Joined
May 16, 2007
Messages
16
My first post.. I am not a native english-speaker, so hopefully I can make myself clear.

I have the following problem
I have a excel-file that will be used to update information in a easy way, so outside Access.
It is a kind of a crosstab-format:

Product - Client1 - Client2 - Client3 - ... - ... - Client18 (variable information)
product1 - 100 - 105 - 95
product2 - 200 - 210 - 190
...
...
product180 - 300 - 305 - 295

I want to import this in a easy way to Access (access2000, if someone wants to know)
In access I want the information in a other format:
product - Client - Price

product1 - client1 - 100
product1 - client2 - 105
product1 - client3 - 95
product2 - client1 - 200
product2 - client2 - 210
etc

Now I already have a solution for this, a SQL-query.
First I import the data from excel to a tmptable in access.
Second I run the SQL-query (SELECT, UNION ALL SELECT kind of query)

Only this is not what I want. It's not flexible enough and it isn't easy to use.
I created my tmptable and my SQLcode for 16 clients. When I need to import 18 clients, I first need to change my table and my SQL-query. And I only want to create the dbase, someone else will work with it.

I think it would be easier when a script automatically reads the values of Row1 (so 'Client1', 'Client2', 'Client3', etc). Automatically reads column A (all products). And of course the values as values.
And then prints the results in the correct table in Access.

I think this can be done with some VBA-code.
But how to do it...
My knowledge of VBA is growing, but isn't that great I can do this myself...
So it would be great if anybody can help me!
 
I created my tmptable and my SQLcode for 16 clients. When I need to import 18 clients, I first need to change my table and my SQL-query.

This is a symptom of an un-normalized database.

Google-search "Database Normalization" and read up on the topic, sticking to the .EDU sites for colleges and universities you know, and for vendor sites you trust. What you describe should not occur unless the database is not normalized.
 
This is a symptom of an un-normalized database.

Google-search "Database Normalization" and read up on the topic, sticking to the .EDU sites for colleges and universities you know, and for vendor sites you trust. What you describe should not occur unless the database is not normalized.
My dbase in normalized.
But the excel-format above is the most easy way for the person to change the values. Changing the values will be done manually, so the excel-file should be as easy as possible. Because otherwise the changing-part will take to much time.
So I need a way to automatically normalize the excel-file. If I must do this in Excel, or in Access: I don't care.
 

Users who are viewing this thread

Back
Top Bottom