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

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: