According to the stats I was last here in 2004...how time flies...
I have a database containing dates and areas that I want to make a cumulative total of over the year. The relevant fields in my small test database look like this:
A_Date Area
01/01/2002 0.1
02/01/2002 0.56
02/02/2002 23.5
07/01/2002 0.7
* There may be several values for a single day – in this case I need a daily total.
* There may be days without any values – in this case I need to insert a day with a zero total.
* Running totals need to start again each year (always having 365 or 366 values per year).
To pad out the missing data I created a small table (“ALLDATES”) containing a single field with all the dates from 01/01/1980 to 31/12/2011, made an outer join to include them all, and grouped the data to get daily totals. So far so good… I can use the dates of the ALLDATES table to generate values for Year.
Then I found that I needed to generate a “Daycount” (1-365 or 166) variable from the dates as DSum gave wacky answers when I used the A_DATE variable direct (total at end of year was fine but all the intermediate values were not).
So now 1 have a query containing the following fields:
A_Date2 (all dates, none missing or duplicated)
Year (calculated from A_Date2)
Area2 (Sum of daily areas, or zero if originally missing)
Daycount (calculated from A_Date2, integer from 1-365 or 366)
I used this as input to another query:
CumArea: DSum("[TEST1].[Area2]","TEST1","[TEST1].[daycount]<=" & [TEST1].daycount & " And [TEST1].[YEAR]=" & [TEST1].YEAR)
This gives me exactly what I wanted.
But there is a complication…
In the full database, there are data from several countries. So there is another field “Country”. Values are strings of length 2 (eg PT, CY, BG). I need to calculate the running totals individually for each country.
In the grouped, padded out data (first query above), country information is missing for those days which didn’t have any data when I do the outer join.
I tried a parameter query [insert country] so that I could dynamically insert it and end up with values for the relevant country on every record so that I can use it in the DSum command.
Final command looks like this:
CumArea: DSum("[TEST].[Area2]","TEST","[TEST].[Daycount]<=" & [TEST].[Daycount] & " And [TEST].[Year]=" & [TEST].[Year] & "And [TEST].[Country]= " & ‘[TEST].[Country]’)
But whatever I do I end up with errors either complaining about syntax errors or, if I get it to run, the calculated fields all display #Error. Clicking on the field prompts the message "The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'choose country''."
I can manage to make it work if I first dump the results of the intermediate query into a table and then do a query on that. But that’s really clunky especially as I’ll have to do this for 22 countries, starting from 1980, and update it every year.
Any suggestions? Are there any glaring syntax problems that I’m missing?
(sorry about length of post, making up for lost time here)
I have a database containing dates and areas that I want to make a cumulative total of over the year. The relevant fields in my small test database look like this:
A_Date Area
01/01/2002 0.1
02/01/2002 0.56
02/02/2002 23.5
07/01/2002 0.7
* There may be several values for a single day – in this case I need a daily total.
* There may be days without any values – in this case I need to insert a day with a zero total.
* Running totals need to start again each year (always having 365 or 366 values per year).
To pad out the missing data I created a small table (“ALLDATES”) containing a single field with all the dates from 01/01/1980 to 31/12/2011, made an outer join to include them all, and grouped the data to get daily totals. So far so good… I can use the dates of the ALLDATES table to generate values for Year.
Then I found that I needed to generate a “Daycount” (1-365 or 166) variable from the dates as DSum gave wacky answers when I used the A_DATE variable direct (total at end of year was fine but all the intermediate values were not).
So now 1 have a query containing the following fields:
A_Date2 (all dates, none missing or duplicated)
Year (calculated from A_Date2)
Area2 (Sum of daily areas, or zero if originally missing)
Daycount (calculated from A_Date2, integer from 1-365 or 366)
I used this as input to another query:
CumArea: DSum("[TEST1].[Area2]","TEST1","[TEST1].[daycount]<=" & [TEST1].daycount & " And [TEST1].[YEAR]=" & [TEST1].YEAR)
This gives me exactly what I wanted.
But there is a complication…
In the full database, there are data from several countries. So there is another field “Country”. Values are strings of length 2 (eg PT, CY, BG). I need to calculate the running totals individually for each country.
In the grouped, padded out data (first query above), country information is missing for those days which didn’t have any data when I do the outer join.
I tried a parameter query [insert country] so that I could dynamically insert it and end up with values for the relevant country on every record so that I can use it in the DSum command.
Final command looks like this:
CumArea: DSum("[TEST].[Area2]","TEST","[TEST].[Daycount]<=" & [TEST].[Daycount] & " And [TEST].[Year]=" & [TEST].[Year] & "And [TEST].[Country]= " & ‘[TEST].[Country]’)
But whatever I do I end up with errors either complaining about syntax errors or, if I get it to run, the calculated fields all display #Error. Clicking on the field prompts the message "The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'choose country''."
I can manage to make it work if I first dump the results of the intermediate query into a table and then do a query on that. But that’s really clunky especially as I’ll have to do this for 22 countries, starting from 1980, and update it every year.
Any suggestions? Are there any glaring syntax problems that I’m missing?
(sorry about length of post, making up for lost time here)
Last edited: