Hi
I have an excel sheet that I've imported into an access 2007 table. It has a field with the user name in it but every so often a location address appears in this column meaning that the users who appear below the address are based at that location. What I need to do is create another column and copy that location address for all applicable users but since the address changes every few rows it's a bit more complicated than I thought. The good thing is the location address always starts with the word "Location:" as below so perhaps that makes it easier, any suggestions. Here's how the column looks:
USER
Location: 1 anywhere street
Homer Simpson
Marge Simpson
Dave Jenkins
Location: 34 over there street
Bart Simpson
Big Jake
Lance Jolly
Mrs Murphy
Mr Murphy
Location: 56 home street
Mr Big
Mr Small
Mr Tall
Mr Greedy
Here's how I want it to look:
LOCATION | USER
Location: 1 anywhere street | Homer Simpson
Location: 1 anywhere street | Marge Simpson
Location: 1 anywhere street | Dave Jenkins
Location: 34 over there street | Bart Simpson
Location: 34 over there street | Big Jake
Location: 34 over there street | Lance Jolly
Location: 34 over there street | Mrs Murphy
Location: 34 over there street | Mr Murphy
Location: 56 home street | Mr Big
Location: 56 home street | Mr Small
Location: 56 home street | Mr Tall
Location: 56 home street | Mr Greedy
Excel imported data
I have an excel sheet that I've imported into an access 2007 table. It has a field with the user name in it but every so often a location address appears in this column meaning that the users who appear below the address are based at that location. What I need to do is create another column and copy that location address for all applicable users but since the address changes every few rows it's a bit more complicated than I thought. The good thing is the location address always starts with the word "Location:" as below so perhaps that makes it easier, any suggestions. Here's how the column looks:
USER
Location: 1 anywhere street
Homer Simpson
Marge Simpson
Dave Jenkins
Location: 34 over there street
Bart Simpson
Big Jake
Lance Jolly
Mrs Murphy
Mr Murphy
Location: 56 home street
Mr Big
Mr Small
Mr Tall
Mr Greedy
Here's how I want it to look:
LOCATION | USER
Location: 1 anywhere street | Homer Simpson
Location: 1 anywhere street | Marge Simpson
Location: 1 anywhere street | Dave Jenkins
Location: 34 over there street | Bart Simpson
Location: 34 over there street | Big Jake
Location: 34 over there street | Lance Jolly
Location: 34 over there street | Mrs Murphy
Location: 34 over there street | Mr Murphy
Location: 56 home street | Mr Big
Location: 56 home street | Mr Small
Location: 56 home street | Mr Tall
Location: 56 home street | Mr Greedy
