I have a userform and database for entering food purchases by receipt. The database is arranged with its first few left columns containing date information for each receipt, with the other columns representing the food items and their costs. There are over 140 such food item columns.
I am building different queries to show the expense totals by item, month, month and item, year, etc., in 3-row columns for display purposes, such as these (partial examples):
Item: Berries
Month Year Expense
Jan 2012 $ 12.36
Feb 2012 $ 7.89
Mar 2012 $ 9.12
And this:
Item: Berries
Month Year Expense
Apr 1999 $ 15.40
Apr 2000 $ 10.75
Apr 2001 $ 13.56
Apr 2002 $ 14.78
I am using advanced filter to extract chunks of unsummarized data, and DSUM formulas to produce totals from this extract to show costs in the above formats. The tricky part is those queries showing costs for the same month for each year, because I then need all the years in the database (1999-2012). I am trying to avoid setting up a criterion section with the item, month and year, and deciding what to do when a new year starts up (I already have 12 criterion sections for the months, but those will never exceed 12). So I am thinking of having one criterion section, extracting the unique years from the database, perhaps into an array, and then looping through those years, putting each year into the Year criterion field, calculating the result, and then storing that result so the next year doesn’t affect the previous year’s total (such a technique could also be used for the months, so I won’t need 12 criterion sections). At least this way, I can do with a single criterion section, no matter how many years get appended to the database.
Not that I know exactly how to do this looping, but is this a good approach, or is there a better way to achieve such query results?
I am building different queries to show the expense totals by item, month, month and item, year, etc., in 3-row columns for display purposes, such as these (partial examples):
Item: Berries
Month Year Expense
Jan 2012 $ 12.36
Feb 2012 $ 7.89
Mar 2012 $ 9.12
And this:
Item: Berries
Month Year Expense
Apr 1999 $ 15.40
Apr 2000 $ 10.75
Apr 2001 $ 13.56
Apr 2002 $ 14.78
I am using advanced filter to extract chunks of unsummarized data, and DSUM formulas to produce totals from this extract to show costs in the above formats. The tricky part is those queries showing costs for the same month for each year, because I then need all the years in the database (1999-2012). I am trying to avoid setting up a criterion section with the item, month and year, and deciding what to do when a new year starts up (I already have 12 criterion sections for the months, but those will never exceed 12). So I am thinking of having one criterion section, extracting the unique years from the database, perhaps into an array, and then looping through those years, putting each year into the Year criterion field, calculating the result, and then storing that result so the next year doesn’t affect the previous year’s total (such a technique could also be used for the months, so I won’t need 12 criterion sections). At least this way, I can do with a single criterion section, no matter how many years get appended to the database.
Not that I know exactly how to do this looping, but is this a good approach, or is there a better way to achieve such query results?
