Filling in missing data (1 Viewer)

ARC20

New member
Local time
Yesterday, 21:14
Joined
Feb 20, 2020
Messages
4
I am new to access and have been trying to read through some tutorials and other posts to figure this out, but could use some more direct advice on my issue. I am trying to organize a set of utility usage data. There are several accounts (30+) that are read on different days. The end goal will be to create a report that incorporates monthly usage for each account plus some other calculations and info. So in order to do this, I need to create estimates of usage.

For each account, whenever a new reading comes through I am provided with the read date, the current reading, and the calculated usage since the last reading.
1: Readings are typically done on, for example, the 10th day of one month, then the 12th of the next. It isn't consistent.
2: Some accounts are read monthly, some are read bi-monthly.
3: Accounts weren't all started at the same time. Some have 10 years of data, some 5, etc.

There are 2 parts to this I am looking for suggestions on:
The first requirement is to generate an estimate of monthly usage for the 1st to the last day of each month (instead of mid-month to mid-month). The plan for this is taking the average daily rate from each billing period, and applying that. So if the bill was read on the 10th, applying the daily rate from that bill to the first 10 days of the month. And then using the next billing period's daily rate for the remaining days in the month after the 10th. So far I have a query that calculates the days between each read date and calculates the daily rate for each billing period under each account (see below table). But now I need to figure out how to apply that (which leads into the below).

Then the question is about the bi-monthly readings. I have the estimated daily rate for the billing period of say mid-March to mid-May which I would like to apply to April. But my question is in the actual data, what is the best way to add a new row for each skipped month of each account, and then use the daily rates to calculate an estimated monthly usage?

What I have:
Acct_Num Read_Date Daily_Rate (since last read)
1 2/12/17 50
1 3/14/17 76
1 4/11/17 46
2 2/11/15 40
2 4/13/15 36
2 6/17/15 38
...etc

Desired output would be something like below:
Acct_Num Period Monthly Usage
1 Mar-2017 xxxx
1 Apr-2017 xxxx
2 Feb-2015 xxxx
2 Mar-2015 xxxx
2 Apr-2015 xxxx
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:14
Joined
Oct 29, 2018
Messages
21,358
Hi. What would the xxxx actually be?
 

ARC20

New member
Local time
Yesterday, 21:14
Joined
Feb 20, 2020
Messages
4
xxxx would be the sum of usage for an account of each entire month, such as March 1 to March 31. So in the end I want monthly estimates of utility usage for each account from whenever the meter was installed through current day.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:14
Joined
Oct 29, 2018
Messages
21,358
xxxx would be the sum of usage for an account of each entire month, such as March 1 to March 31. So in the end I want monthly estimates of utility usage for each account from whenever the meter was installed through current day.
Okay, that might still be possible. For example, you can use an Outer Join query between a list of dates and your usage data and then use the Nz() function to provide the value for xxxx.
Sent from phone...
 

cheekybuddha

AWF VIP
Local time
Today, 02:14
Joined
Jul 21, 2014
Messages
2,237
Code:
SELECT
  Acct_Num,
  Format(Read_Date, "YYYY-MM") AS [Year/Month],
  SUM(Daily_Rate)
FROM YourTable
GROUP BY
  Acct_Num,
  Format(Read_Date, "YYYY-MM")
ORDER BY
  Acct_Num,
  Format(Read_Date, "YYYY-MM")
;

But, without the outer join the DBG suggests you may have missing months in the list is there is no read within a particular month.

hth,

d
 

Users who are viewing this thread

Top Bottom