Access Calculation

Question

Hi,

I am working on creating a table in access 2013 and am trying to incorporate calculations that I am not familiar with in access.  I have been able to create a draft of the calculation in excel, but cannot convert that to access language.  If there is anyone who would be able to provide any insight as to how to create these calculations or if it is even possible would be greatly appreciated.

-          We have created a table with the following columns “Base”, “EntityCode”, “Date”, “TotalUnits”, “UnitsSinceBase”, “MonthsSinceBase”, “AvgUnitsPerYear”. 

-          We will track units on a periodic basis.  Each EntityCode will eventually have many periods of units noted in the access table.

-          We want to be able to set 1 period for that EntityCode as the base and then calculate certain stats for future periods based on that Base period.

-          So, for a particular EntityCode, we would want to calculate UnitsSinceBase and MonthsSinceBase.  Using those data points, we would want to calculate a third field which calculates an average annual units count (so total change in units, divided by total months = amount per month……*12 is amount per  year)

-          The base period may change overtime….currently using a Yes/No lookup drop down to indicate which period is the base. 

-          If there are periods of information that have a date before the base date, we would want to make the three calculated fields display “N/A”, or something similar.

-       

Thanks,

Doug

Answer 1

No, I mean what are the field names and what do they represent, not their datatypes. Also, I'm not referring to the table you want to put the data in, as I said this should be a query not a table.

It appears that you want to filter and aggregate data that is in another table. That's the table we need to understand the structure for to be able to help.

Answer 2

This is the structure of the table.

Autonumber

Date/Time

Number

Long Text

Number

Short Text

Number

Number

Number

Answer 3

Yes I mean what fields are in the table. 

By the way, its not a good idea to have "dropdown fields" on the table level. Lookup fields should be done on the form level, not at the table level.

Answer 4

When you say structure are you talking about what the fields in the tables are?  The table is just short/long texts, numbers, dates, and dropdown fields.

Thanks,

Doug

Answer 5

This should NOT be done in a table, but in a query. What is the structure of the base table you are pulling this data from.