The following is my scenario. I have been handed a list which has 180+ columns! The principal reason there are so many is the same set of 5 columns has been repeated nearly 30 times to capture multiple instances of the same type of data e.g. date1, detail1;
date2, detail2 and so. Horrible. I would like to take the data in this list and push it into a new list which has 1 row for each of the instances of the dataset e.g. it just has a date, detail column etc and consequently about 140 less columns! Complicating
matters is the fact that one of the columns contains data in a multi-select lookup from another list in the same site.
I am trying to achieve this using Office tools only...e.g. Access, Excel, perhaps SharePoint Designer...I don't have dev skills, although I could ask others.
It seems to me I can work with these lists connected to Access and write a query which would get the data into the correct format but the multi-select lookup column causes issues:
- I can't seem to write an SQL query with a UNION operator when a multi-value field is involved
- I can't write an INSERT INTO (new list) statement where a multi-value field is involved
- I might also run into problems with display value versus ID for the lookup even if I could do the insert?
Any suggestions on how I might go about thi
View Complete Post