I have a table in Access 2007 and Access 2003 which contains the YTD sales information for my company. I import the file from a csv file, then I have a query that adds month and year fields based on an existing field. Finally, I use a make table query to create the final table result. From this "base table", I run a query that creates a table that limits the results to each specific sales person. I have approximately 15 queries. On the Excel side, I have pivot tables based on the query results of each sales person. I am having two problems with this configuration. First, I need to run an import and two queries just to get the base table. Secondly, when I open the Excel spreadsheets and refresh the pivot tables, I am not getting the results I look for. Often I do not have any update to the pivot table. I then go in and reestablish the ODBC connection and I get an update. Is there any way around this?
Can I update the pivot tables by calling the query in the Access db? How do I retain the ODBC connection so that I don't have to update it each time?
Thanks in advance for your input.
Can I update the pivot tables by calling the query in the Access db? How do I retain the ODBC connection so that I don't have to update it each time?
Thanks in advance for your input.