I have a query in a database I use to manage my accounts, each record is a transaction. No calculations are carried out in this query it simply brings together calculated results from other queries to show the correct value and date of each transaction (other queries are used to ensure that the forecast date and amount are shown for forecast transactions and the actual date and amount are shown for actual transactions).
Once all this has been done I prefer to use Excel for the actual analysis, importing the records as a table, then creating a pivot table with months in the columns and categories in the rows as it is easier in Excel to add rows for opening and closing balance and do other forms of analysis.
In theory it should be simple, and it is to a point. I have checked the closing balances derived in excel and up to June they match the closing balances on my bank statements, From there onwards it goes wrong.
The reason why it is going wrong is that the most recent transactions entered have zero amounts when I look at the table in Excel, but this doesn't match the query in Access which the table is supposed to be connected to. As far as I can see when the information leaves Access it is all correct, but when it arrives in Excel it has been changed somehow and is now incorrect. Not a clue why, I can't put my finger on why the recently entered records might be different to the earlier ones and have tried creating a new connection to the same query but to no avail.
Once all this has been done I prefer to use Excel for the actual analysis, importing the records as a table, then creating a pivot table with months in the columns and categories in the rows as it is easier in Excel to add rows for opening and closing balance and do other forms of analysis.
In theory it should be simple, and it is to a point. I have checked the closing balances derived in excel and up to June they match the closing balances on my bank statements, From there onwards it goes wrong.
The reason why it is going wrong is that the most recent transactions entered have zero amounts when I look at the table in Excel, but this doesn't match the query in Access which the table is supposed to be connected to. As far as I can see when the information leaves Access it is all correct, but when it arrives in Excel it has been changed somehow and is now incorrect. Not a clue why, I can't put my finger on why the recently entered records might be different to the earlier ones and have tried creating a new connection to the same query but to no avail.
