How to use multiple monthly values from same field in a table to calculate monthly value

[astdk]

I would like to use the SALES VOLUME - OIL (BBLS)*6 + SALES VOLUME - GAS (MCF) + SALES VOLUME - LIQUIDS (GAL)/42+6 to calculate MCFE.

SALES VOLUME - OIL (BBLS), SALES VOLUME - GAS (MCF), SALES VOLUME - LIQUIDS (GAL) are all monthly values and I need a corresponding monthly MCFE value to be calculated.

Image of the DB with the data:




Please help.

Thank you.

[June7]

This is not a normalized data structure. How many 'Occur' fields are there - 12? Is this a multi-year db?

You want to just Sum all the Sales GL codes?

[ssanfu]

Your table is structured incorrectly. You have it like an Excel spreadsheet - short and wide. You need to have an Access table tall and narrow.
Also you have spaces in object names. Shouldn't use spaces.

The table structure should be something Like

CC_Number
Prop_Name
GL_Code
Occur_MO (ex. 1, 2, 3, 4,..., 12
Occur_YR (ex. 2015)
Amount

Then it is easy to do the calculations in a query.



Actually, I would have 2 more tables:
tblProps
----------
ProP_ID_PK
PropDesc

tblGL_Codes
------------
GL_Code_ID_PK
GL_Code

[astdk]

This is not a normalized data structure. How many 'Occur' fields are there - 12? Is this a multi-year db?

You want to just Sum all the Sales GL codes?

I am sorry but i do not understand what you mean by a normalized data structure - could you please explain?

I want to use specific Gl Codes (oil, gas, NGL sales) to calculate a monthly value (BOE).

There are 12 Occur fields representing a yearly data dump form our accounting system.

The db will end up being multi month/year (we dump 12 months of data each month so dump 1 is 01/15-12/15, dump 2 will be 02/15 - 01/16, etc)

[astdk]

Thank you Steve.

It may be difficult to manipulate our raw data into a form you suggest based on what gets spit out by our systems.

Is tall and narrow simply easier to manipulate in Access?

I do have a second table for propDesc.

How would having the GL_Codes table help?

[June7]

Suggest you work through some tutorials and get a better understanding of relational database principles and data normalization. Here is one http://www.rogersaccesslibrary.com/

[ssanfu]

In a form, you would have a combo box to be able to select the GL Code instead of typing it in. Instead of duplication data, table GL_Codes woud have a primary key field you would store in the data table as a foreign key field - no duplication of data.

It may be difficult to manipulate our raw data into a form you suggest based on what gets spit out by our systems.

What the raw data looks like and how it is stored are two different things.


You want calculations by month (your Post# 1), correct? With your current structure it is very difficult to get data from multiple records for 1 month.
This is how your table might look:
Attachment 21891
I second June's advice - work through (don't just read) the tutorials.

[June7]

And if you want to calculate BOE, then why does this show as a record in the data?

Steve, I don't think normalizing the Occur fields will actually improve the situation. As it is, each Occur column can be summed to generate monthly aggregates. The issue is what records should be included in the sums.

Use the table or a query (SELECT * FROM tablename;) as RecordSource for a report and do the aggregate calcs in textboxes in report header/footer:

=Sum(IIf([GL Code] LIKE "Sales*", [Occur MO 1], 0)) / 42 + 6

[ssanfu]

I would like to use the SALES VOLUME - OIL (BBLS)*6 + SALES VOLUME - GAS (MCF) + SALES VOLUME - LIQUIDS (GAL)/42+6 to calculate MCFE.

SALES VOLUME - OIL (BBLS), SALES VOLUME - GAS (MCF), SALES VOLUME - LIQUIDS (GAL) are all monthly values and I need a corresponding monthly MCFE value to be calculated.

I think there is an error in the formula:
MCFE = [SALES VOLUME - OIL (BBLS)]*6 + [SALES VOLUME - GAS (MCF)] + ([SALES VOLUME - LIQUIDS (GAL)]/42)*6
Convert the gals to bbls, then multiply by 6. Yes??

I want to use specific Gl Codes (oil, gas, NGL sales) to calculate a monthly value (BOE).

Do you want to store BOE for each month?

The db will end up being multi month/year (we dump 12 months of data each month so dump 1 is 01/15-12/15, dump 2 will be 02/15 - 01/16, etc)

You are dumping a rolling 12 month period. So will need another field to define which "dump" a specific month will belong to? Will have to think about this.

Are you saying that every month, the "dump" includes the data for the preceding 12 months?
ie
June 2015 dump -> 5/14 - 6/15
July 2015 dump -> 6/14 - 7/15
Aug 2015 dump - > 7/14 - 8/15

So you would have 12 monthly dumps of 12/14 data in the table???

[astdk]

I think there is an error in the formula:
MCFE = [SALES VOLUME - OIL (BBLS)]*6 + [SALES VOLUME - GAS (MCF)] + ([SALES VOLUME - LIQUIDS (GAL)]/42)*6
Convert the gals to bbls, then multiply by 6. Yes??

Yes, the change you made is correct

Do you want to store BOE for each month?

No, BOE, MCFE, Total Volume, etc do not need to be stored as long as we can get them displayed in a report.

You are dumping a rolling 12 month period. So will need another field to define which "dump" a specific month will belong to? Will have to think about this.

Yes, we are dumping a rolling 12 month period. Looking back on our raw data dump, we actually have dates attached to each Occur (I had renamed the column in my table to take off the date in my first attempt to create our desired report).

Are you saying that every month, the "dump" includes the data for the preceding 12 months?
ie
June 2015 dump -> 5/14 - 6/15
July 2015 dump -> 6/14 - 7/15
Aug 2015 dump - > 7/14 - 8/15

So you would have 12 monthly dumps of 12/14 data in the table???

Correct. Each dump will be the previous 12 months of data (as you described). We would like to keep the previous dump's data in case we wanted to look back and see what/how the data changed form dump to dump.

[June7]

Did you see post 8?

[astdk]

Did you see post 8?

Yes I did. I do not fully understand the post though.

The BOE is not the sum of all the sales numbers - gas volumes have to be converted to bbls (*6) and then for a MCFE calculation the oil and ngl sales have to be converted to mcf (bbls oil*6, gals of NGLS/42*6) so I do not understand how the iif sum could work at the report level.

[June7]

Build a query that does the conversion calculations then do the aggregate Sum on that constructed field.

Not every record has the same conversion calculation formula so this will require IIf() or Switch() conditional expression or a VBA custom function.

Exactly which type of records get what calculation? Records where [GL_Code] LIKE "*Oil*" or LIKE "*Gas*"? What formula would apply to each?

[astdk]

Build a query that does the conversion calculations then do the aggregate Sum on that constructed field.

Not every record has the same conversion calculation formula so this will require IIf() or Switch() conditional expression or a VBA custom function.

Exactly which type of records get what calculation? Records where [GL Code] LIKE "*Oil*" or LIKE "*Gas*"? What formula would apply to each?

All of the 'sales volume' records will get some sort of calculation - either to convert to BOE or covert to MCFE. We also have several other similar type calculations that need to be done for the report (% Oil, % liquids, $/BOE, etc.).

I was getting stumped on the conversion query because of how I have the data in the table.

Oil converted to MCFE = bbls oil*6
Gas converted to BOE = MCF gas/6
NGLs converted to MCFE = gals NGL/42*6

[June7]

I don't see NGLs in any GL_Code.

ConvMo1: Switch([GL_Code] LIKE "*Oil*, [Occur Mo 1] * 6, [GL_Code] LIKE "*Gas*", [Occur Mo 1] / 6, [GL_Code] LIKE "*NGLs*"), [Occur Mo 1] / 42 * 6, True, [Occur Mo 1])

But now what do you want to aggregate? Can BOE and MCFE be added together?

=Sum(IIf([GL Code] LIKE "Sales*", [ConvMo1], 0))