Hi,
You will need 2 more queries to take account of the nil values and changing values. I purposely held this back so that you could get started without getting confused. If you base the 2 new queries on the 3rd query but save them as queries 4 and 5 then you are already half way there.
Query 4 : an append that was query 3 (keep query 3 intact) but saved as query 4, add a new column being the [total_expenses], append it to nothing (i.e. leave the append line blank), in the criteria section under [total_expenses] enter "Is Null" (without the quotes), then change this bit of code :
[total_revenue] - [total_expenses]
to this :
[total_revenue] - iif(isnull([total_expenses]),0,[total_expenses]) {late edit - or you could try leaving it as just [total_revenue]}
and in the criteria section under that (i.e. the gp value) enter "<>0" (without the quotes) and lastly, double click on each line (one at a time) linking the two queries (in the top half of the screen) and change the Join Property to include all records from the
first query and only the matching records from the
second query.
Save and Run.
Query 5 : an append that was also query 3 but saved as query 5 (keep query 3 intact), add a new column being the [total_revenue], append it to nothing (i.e. leave append blank), in the criteria section under [total_revenue] enter "Is Null" (no quotes), then change this bit of code :
[total_revenue] - [total_expenses]
to this :
iif(isnull([total_revenue]),0,[total_revenue]) - [total_expenses] {late edit - or you could try leaving it as just
minus[total_expenses]}
and in the criteria section enter "<>0" (without the quotes) and lastly, double click on each line (one at a time) linking the two queries (in the top half of the screen) and change the Join Property to include all records from the
second query and only the matching records from the
first query (<- note the change).
An extra change you will need to make to query 5 is in the "project" and "month" columns - where the table say "query1" you need to change it to "query2" for both items.
Save and Run.
To automate all of this (i.e. to accommodate new data and changing value), you can create one more query (i.e. a delete query to delete all records from the main table where the account = "gp") and then roll up the new delete query as well as queries 3, 4 & 5 into a single macro.
If you go into the macro screen, create a new macro, one of the drop down commands is "open query" <- this is the one you want to run to get the queries to run. Please be aware that you only run one query at a time, first the new delete query to flush out the old data, then queries 3, 4 & 5 to append the gp values, but they can all be in the same macro. Don't forget to include the stop macro command as the last line in the macro.
If you save and run the macro it will then run the 4 queries for you - in the correct sequence.
Once you are happy the macro is working ok then you might also want to turn off the pop up warnings by setting the "setwarnings" value to "no" at the start of the macro and then turning it back on at the end (plus the "echo" on and off command stops the screen going beserk).
Sorry for the long and involved answer but you can see why I held it back from my earlier response.
HTH, Andrew.
