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