sportyaccordy
New Member
- Joined
- Jun 17, 2005
- Messages
- 6
Here is the basic gist of what I am trying to do: I have monthly utility usage data for 1100 facilities, and I want to create a calculated field either in a table or a query that has the monthly consumption from a year prior. I want it to work so that each month I can just paste append the latest data and have everything auto-populate/calculate. I have this built in Excel and the basic gist of the sheet looks like this:
Store Service M Y Factor UsageCurr UsagePrev
927 Electric 1 2016 927.Electric.1.2016 99,016.380 110,664.827
1451 Electric 1 2016 1451.Electric.1.2016 89,037.421 89,726.454
1152 Electric 1 2016 1152.Electric.1.2016 116,550.295 114,177.016
337 Electric 1 2016 337.Electric.1.2016 151,148.733 146,284.852
547 Electric 1 2016 547.Electric.1.2016 114,710.405 120,170.768
343 Electric 1 2016 343.Electric.1.2016 121,358.067 113,979.354
2588 Electric 1 2016 2588.Electric.1.2016 105,565.336 111,689.966
So what I do is concatenate the relevant parameters (facility, utility service type, month and year) so I can have 1 field to lookup against; then to get last year's usage I check if the usage last year is even available, and then just match/index the factor/usage to get it... the formula in "UsagePrev" looks like this:
=IF(ISNA(INDEX([Usage],MATCH(CONCATENATE([@Store],"."[@Service],".",[@M],".",[@Y]-1),[Factor],0))),0,INDEX([Usage],MATCH(CONCATENATE([@Store],"."[@Service],".",[@M],".",[@Y]-1),[Factor],0)))
So to get the previous year's electric usage at facility 343 in January 2016, Excel would return the usage corresponding to the factor 343.Electric.1.2015.
I pulled the Store through UsageCurr columns (creating that same concatenated Factor field) from Excel to Access and tried a wide range of things to get Access to calculate the UsagePrev.... DLookUp("Usage","Energy","Factor =" & [StoreNo] & [Service] & [M] & [Y]-1), creating a "PrevFactor" with the previous year and making the criteria for Factor to equal that, etc.... I keep getting nothing but errors for the resulting column. Is it a syntax error, am I asking too much of Access, not setting up DLookUp right or what? This is the only hangup keeping me from making the full migration to Access for this report.
Store Service M Y Factor UsageCurr UsagePrev
927 Electric 1 2016 927.Electric.1.2016 99,016.380 110,664.827
1451 Electric 1 2016 1451.Electric.1.2016 89,037.421 89,726.454
1152 Electric 1 2016 1152.Electric.1.2016 116,550.295 114,177.016
337 Electric 1 2016 337.Electric.1.2016 151,148.733 146,284.852
547 Electric 1 2016 547.Electric.1.2016 114,710.405 120,170.768
343 Electric 1 2016 343.Electric.1.2016 121,358.067 113,979.354
2588 Electric 1 2016 2588.Electric.1.2016 105,565.336 111,689.966
So what I do is concatenate the relevant parameters (facility, utility service type, month and year) so I can have 1 field to lookup against; then to get last year's usage I check if the usage last year is even available, and then just match/index the factor/usage to get it... the formula in "UsagePrev" looks like this:
=IF(ISNA(INDEX([Usage],MATCH(CONCATENATE([@Store],"."[@Service],".",[@M],".",[@Y]-1),[Factor],0))),0,INDEX([Usage],MATCH(CONCATENATE([@Store],"."[@Service],".",[@M],".",[@Y]-1),[Factor],0)))
So to get the previous year's electric usage at facility 343 in January 2016, Excel would return the usage corresponding to the factor 343.Electric.1.2015.
I pulled the Store through UsageCurr columns (creating that same concatenated Factor field) from Excel to Access and tried a wide range of things to get Access to calculate the UsagePrev.... DLookUp("Usage","Energy","Factor =" & [StoreNo] & [Service] & [M] & [Y]-1), creating a "PrevFactor" with the previous year and making the criteria for Factor to equal that, etc.... I keep getting nothing but errors for the resulting column. Is it a syntax error, am I asking too much of Access, not setting up DLookUp right or what? This is the only hangup keeping me from making the full migration to Access for this report.