Again I have a hybrid Access/Excel question/issue..
The problem is that I have a somewhat complicated query in Access. It is populating all of the fields exactly as expected and how I want them calculated. This query is set up as a connection which drives a pivot table in Excel. It looks like the SQL is a simple SELECT * FROM [MY_QUERY], it was populated through the load to pivot table wizard when making a new connection.
So here's the issue.. Within this pivot table I have a series of selections in the Rows. These are based on formulas within the query and populate a string of numbers and text that identify various hierarchies in the data. The issue is that what is being populated in the pivot data is not matching what exists in the Access database/query data. For example, one hierarchy is 123 in Access, and yet when it's shown in the pivot in Excel it is 111. I can drill into the specific record in Excel, then look it up in Access and it is different.
What I believe might be playing into this is the fact that the fields behaving this way are based on an IIF formula to handle certain exceptions where in this scenario it would check if the original value is 111 and then convert it to 123. Although this seems to be working and outputting the correct result in Access for some reason it is not being reflected in Excel's pivot version of the query..
Any ideas? The data is refreshing but for some reason not handling these few fields properly. For further details the IIF also contains a wildcard search to see if one field contains a "*WORDS*" type of result, then if it does outputs a different value than it would. Again, this is functioning properly in Acess and the records are how I intend, just not reflecting in Excel's pivot table version of the data..
The problem is that I have a somewhat complicated query in Access. It is populating all of the fields exactly as expected and how I want them calculated. This query is set up as a connection which drives a pivot table in Excel. It looks like the SQL is a simple SELECT * FROM [MY_QUERY], it was populated through the load to pivot table wizard when making a new connection.
So here's the issue.. Within this pivot table I have a series of selections in the Rows. These are based on formulas within the query and populate a string of numbers and text that identify various hierarchies in the data. The issue is that what is being populated in the pivot data is not matching what exists in the Access database/query data. For example, one hierarchy is 123 in Access, and yet when it's shown in the pivot in Excel it is 111. I can drill into the specific record in Excel, then look it up in Access and it is different.
What I believe might be playing into this is the fact that the fields behaving this way are based on an IIF formula to handle certain exceptions where in this scenario it would check if the original value is 111 and then convert it to 123. Although this seems to be working and outputting the correct result in Access for some reason it is not being reflected in Excel's pivot version of the query..
Any ideas? The data is refreshing but for some reason not handling these few fields properly. For further details the IIF also contains a wildcard search to see if one field contains a "*WORDS*" type of result, then if it does outputs a different value than it would. Again, this is functioning properly in Acess and the records are how I intend, just not reflecting in Excel's pivot table version of the data..
