Hi all,
This is my first post - so my apologies in advance if I don't make entire sense first time round - however, I'll try to state the problem I'm having as clearly as possible.
I am importing data initially from an excel spreadsheet into a staging table in this kind of format (I'm representing the columns with "|" below) :
| | Date1 | Date2 | Date3 | Date4 |... Date99...
Department A | Team 1 | 10.2 | 12.3 | 16.8 | 12.0 | .... 13.9...
Department B | Team 1 | 15.0 | 17.8 | 11.0 | 10.6 | .... 21.3...
Department A | Team 4 | 23.1 | 22.8 | 19.4 | 17.4 | .... 30.2...
Department C | Team 3 | 19.1 | 21.1 | 23.7 | 27.2 | .... 29.0...
Department A | Team 2 | 09.5 | 07.9 | 10.4 | 11.0 | .... 13.1...
- and my aim is to get the data into this format :
Department A | Team 1 | Date1 | 10.2
Department A | Team 1 | Date1 | 10.3
Department A | Team 1 | Date1 | 16.8
Department A | Team 1 | Date1 | 12.0
...
Department A | Team1 | Date99 | 13.9
Department B | Team 1 | Date1 | 15.0
Department B | Team 1 | Date2 | 17.8
... and so on
I think this could be done with a number of crosstab queries - however I don't know how many date columns will exist in the excel spreadsheet before it arrives (at present I'm up to 86) - therefore I was anticipating using a loop to go through each date column and get all it's date starting with Date1 and working up until it reaches the first one with no date.
Unfortunately I have other restrictions to work to which makes this all the more difficult (at least for my knowlege) - I'm only able to work with tables / queries / forms and macros - as I shall be handing this app over to a business team with no VBA knowledge when I'm done - so it needs to be made using only the components they can work with.
I'm also not able to get the format of the incoming excel file amended to be in a more workable format.
To me it seems to be a complicated problem / situation I'm try to deal with but maybe I'm missing something really simple ?
I can share the things I've already tried (and failed !) if that will help but this post seems long enough already.
Any / all help and advice you can offer is very much appreciated.
Thanks,
Reg.
This is my first post - so my apologies in advance if I don't make entire sense first time round - however, I'll try to state the problem I'm having as clearly as possible.
I am importing data initially from an excel spreadsheet into a staging table in this kind of format (I'm representing the columns with "|" below) :
| | Date1 | Date2 | Date3 | Date4 |... Date99...
Department A | Team 1 | 10.2 | 12.3 | 16.8 | 12.0 | .... 13.9...
Department B | Team 1 | 15.0 | 17.8 | 11.0 | 10.6 | .... 21.3...
Department A | Team 4 | 23.1 | 22.8 | 19.4 | 17.4 | .... 30.2...
Department C | Team 3 | 19.1 | 21.1 | 23.7 | 27.2 | .... 29.0...
Department A | Team 2 | 09.5 | 07.9 | 10.4 | 11.0 | .... 13.1...
- and my aim is to get the data into this format :
Department A | Team 1 | Date1 | 10.2
Department A | Team 1 | Date1 | 10.3
Department A | Team 1 | Date1 | 16.8
Department A | Team 1 | Date1 | 12.0
...
Department A | Team1 | Date99 | 13.9
Department B | Team 1 | Date1 | 15.0
Department B | Team 1 | Date2 | 17.8
... and so on
I think this could be done with a number of crosstab queries - however I don't know how many date columns will exist in the excel spreadsheet before it arrives (at present I'm up to 86) - therefore I was anticipating using a loop to go through each date column and get all it's date starting with Date1 and working up until it reaches the first one with no date.
Unfortunately I have other restrictions to work to which makes this all the more difficult (at least for my knowlege) - I'm only able to work with tables / queries / forms and macros - as I shall be handing this app over to a business team with no VBA knowledge when I'm done - so it needs to be made using only the components they can work with.
I'm also not able to get the format of the incoming excel file amended to be in a more workable format.
To me it seems to be a complicated problem / situation I'm try to deal with but maybe I'm missing something really simple ?
I can share the things I've already tried (and failed !) if that will help but this post seems long enough already.
Any / all help and advice you can offer is very much appreciated.
Thanks,
Reg.
