I have multiple subsites each containing some project template lists (e.g. risks, issues, tasks, etc.) I have a quite developed solution in Access with a few of the Sharepoint tables linked into it so formatted reports can be created for various audiences. However, to complete the reports i need all the subsite lists to be rolled up into one master list which can then be linked into Access. Can someone tell me what options i have?
Some points so far:
- Cannot purchase any 3rd party solutions
- Would ideally like to maintain Permissions so if the user does not have permission to access a particular subsite then they can't pull this info.
- CQWP initially seemed like an option but all this does is display the data. I need a way of getting the displayed/consolidated list into an Access table - so that any subsite subsequently created with lists using the establish content types get automaticalled added the master tables.
Can anyone tell me what my options are? In my head an option that gives me a 'CQWP with Open with Access function' would be just the ticket.