Queries :: SQL Code To Compute Moving Average

Dec 3, 2013

I have a query that pulls data from a table called Data. I'd like to add a column to the query that will be the moving average -- For example, for a Month Beginning Date of 12/2013, it would average the SumOfCount variable for 10/2013, 11/2013, and 12/2013.

This is the sql code for my query without the moving average:

SELECT Data.Behavior, Sum(Data.Count) AS SumOfCount, Data.[Month Beginning Date], [First Name + Last Name].Wing, Data.[Full Name]
FROM Data INNER JOIN [First Name + Last Name] ON Data.[Full Name] = [First Name + Last Name].[Full Name]
GROUP BY Data.Behavior, Data.[Month Beginning Date], [First Name + Last Name].Wing, Data.[Full Name]
HAVING (((Data.Behavior)="PRNs") AND ((Data.[Full Name])=[forms]![report parameters]![fullname]));

How to insert that column into my code?

View Replies


ADVERTISEMENT

Queries :: Calculate Moving Average - Make Table Query Type Incorrect

Aug 16, 2015

I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.

However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.

I've added an extra button and Make Table query to the example.

As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.

How do I make the created Table use Number Type for my calculated moving average?

Using Access 2013

View 2 Replies View Related

Moving Average - How To Calculate Values From Column

Aug 13, 2013

Is there any formula or any way to calculate moving average on access? What I need is to calculate a value based on data from the past 3 months:

I have following structure for example:

Column A ----- Column B -------- Column C
ValueA1 ------- Value B1 ---------

ValueA2 ------- Value B2 ---------
ValueA3 ------- Value B3 --------- =average (ValueB1, valueB2, valueB3)
ValueA4 ------- Value B4 --------- = average (valueB2, valueB3, valueB4)

My main point id how to calculate values for column C.

View 1 Replies View Related

Queries :: Compute Field With DLookUp In Query?

Jun 6, 2014

I'm designing a query. It's very simple.

I just need to compute something like:

Var: TT[YYZ for this month]-TT[YYZ for last month]

The table is called C1 and has the fields i'm interested

YYZ: Location
TT: number
mxx: date

So, i have for YYZ a list of places and for TT a list of values. MXX has the date of the record on the table.

There is one record per date, which it means, there only one record for 1/1/1991 for the YYZ=2.

NF should calculate the difference between the the record on t against t-1.

I've tried with this

Var: YYZ-DLookUp("yyz","[C1]"," "[YYZ]=[YYZ]" AND "[mxx]=DateAdd("m", "-1", "[mxx]"))

But it fails.

I upload a xls sheet with the desired field, Var ,calculated.

View 2 Replies View Related

Queries :: Compute Year Growth For A Field

Jun 2, 2014

I need to compute the increase for the field Area the following:

yyz(t)-yyz(t-12).

T es month. The table has this structure

Area yyz date Y

Area represents different locations. Each area has only one record per month. Month is obviously the field Date. And Y is yyz(t)-yyz(t-12).

I tried to use DateAdd on access 2013 but the function seems strange, and besides, i don't know how to link
Area yyz(t) date Y:yyz(t)-yyz(t-12).

I just want to create a query in which i can see for each record his yyz value this month and yyz the last year on Y.

View 12 Replies View Related

Help With Code For Moving Cursor

Jun 16, 2005

First I am no good with code so I need your help!
I have code that tells the cursor to move to a certain field.

Private Sub Form_Current()
ClaimNumber.SetFocus
End Sub

It works for the most part - our problem with this is if we have the form open and we are searching on a different field say "Last name" when we click find next it finds the first one but if we click find next again it tells us that there is no records. This is because the cursor has moved to the ClaimNumber field.

Is there a way I can modify this code to say move the cursor only on a new record.

Any help would be great!

View 2 Replies View Related

Forms :: Moving Form By Code On The Display

Jul 22, 2014

I'd like to do the following: when the user does something on Form A, Form B has to be open and the Forms should be located on the sides (Form A to the left side, Form B to the right side) of the display so the user can see them at a time.

However both Form A and Form B has an automatic centering function set when used alone.

Is there a way I could push this from code? I can't fine anything like that among the form's property.

View 2 Replies View Related

Compute Age

Mar 2, 2007

Hi all,
my question is how do I compute an age based on the day of birth, and compute the average age, all in the same query ?

All answers would be appreciated.

View 8 Replies View Related

Queries :: Get Average Of All Non-zero Entries?

Nov 26, 2013

I have a query result-set with several columns: A, B, C ,D. All Four of these columns display numbers. From this query, which I will call the "SetUp"query, I want to query it and obtain the average for each of the columns A, B , C ,D. However, some of the values in columns A, B , C ,D are zero. If I use the standard 'Avg' function in Access, it averages the column AND INCLUDES THE ZEROS IN THE AVERAGE. How can I get an average of just all the non-zero entries?

View 4 Replies View Related

Queries :: Average Function In Query

Jul 5, 2013

I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard

Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status

The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon..I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013. My Dashboard needs to have the below fields

Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days

I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero.

SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days]
FROM TblWO
WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected"))
GROUP BY TblWO.[Operational* Categorization Tier 2]
HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));

View 3 Replies View Related

Queries :: Exclude Zero From Average Query

May 21, 2013

I made a query to calculate the average of a column, suppose that I have 5 records in that column (46,35,0,19.3,12), when the query calculate the average it sums the total of the column and divide by 5 (that's 112/5 = 22.4), what I need the query to do is to divide by 4 because one record is zero (that's 112/4=28). I put in the criteria the following (Not Is Null And <>0), yes this will not show the column that has zero but it still divide by 5.

View 6 Replies View Related

Queries :: Joining Rows To Average Row

Jul 7, 2013

Any way to join 6 rows into one and calculating average.

So I have temperature data in rows in time intervals of 10 minutes. I would like to join rows in one hour interval, and at the same time calculate average temperature in temperature field.

View 3 Replies View Related

Queries :: Get Average Of Select Group Of Records?

Sep 26, 2013

I am trying to get the average of a select group of records within a query. It appears the davg function should give me what I need, however my query returns no results. Here is a sample of my data.

Item Cost Basis Group Cost
1HF20812 1HF208 6.17
1HF20816 1HF208 8.63
1HF20820 1HF208 9.44

Here is the davg string I am trying to use.

Group: davg("Cost","Cost Basis Group")

View 2 Replies View Related

Queries :: Counting Records Which Exceeds Monthly Average Value

Nov 14, 2013

I am currently working on a small database to track my own investment records.

I would like to create the function which enable to count the number of records that has exceeds the average value during that period.

Following is the SQL that I have managed to produce:

SELECT [ED 2013].Code, Count([ED 2013].Start) AS CountOfOpen1
FROM [ED 2013]
WHERE [ED 2013]![Date] Between #1/1/2013# And #2/1/2013# AND [ED 2013]![Start]>[ED 2013]![End]
GROUP BY [ED 2013].Symbol;

This SQL does work. However, when I try to add the Avg function. The Query stop working.

Meanwhile, I am wondering if there is any existing Ms Access template (free or commercial - but must be customisable ) available?

View 1 Replies View Related

Queries :: Average Call Rate - Determine If Trend Is Up Or Down

Jun 28, 2013

I have a query that displays the average call rate on various pieces of equipment over the last 12 months. I was wondering if there is a way to have Access look at this query and determine if the call rates are trending up or down over that time frame. Kind of like a trend line would do on a chart in Excel, only I want this to show me the word Up or Down in my results. Is this possible?

Here is the query I am using:

Code:
TRANSFORM FormatNumber(Avg([All Call Rate]),2) AS AvgCallRate
SELECT List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
FROM List_With_TNC_tbl
GROUP BY List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
PIVOT Format([Month Start Date],"mmm") In ("Jan","Feb","Mar","Apr","May", "Jun", "Jul","Aug","Sep", "Oct", "Nov", "Dec");

View 3 Replies View Related

Queries :: MS Access - Average Of All Records Greater Than Zero In A Field

Mar 24, 2013

I have a field in an MS Acess query that has records like - 1,000; 534; 769.13; 0; 654; 0, etc. It can also have few null records.I want to calculate the average of all records which are greater than zero. Ignore zero values or null values.

View 4 Replies View Related

Queries :: Criteria For Average Within A Year Previous To Specified Date From A Control

Dec 16, 2013

I'm setting up some analytical reports and i'm having a hard time with some date criteria.

I've gotten monthly averages already and what i want to do is take those monthly averages, and average them throughout the year. (the year being from the specified date on a form control back one year)

Month([Forms]![Report Runner]![Date1]) and Year([Forms]![Report Runner]![Date1]) worked great for specifying a certain month in a certain year, but what i'd like is to show all the monthly numbers for every month going back one from [Forms]![ReportRunner]![Date1].

I tried this:

Code:
>(Year([Forms]![Report Runner]![Date1])) And <((Year([Forms]![Report Runner]![Date1]))-1)

and it didnt work. How to do this?

View 2 Replies View Related

Queries :: Average Price Of Last 5 Line By Items By Recent Date

Mar 4, 2015

Any way to filter the average price of of a Product within the last 5 occurences (Line Items). It would pull a week back so WHERE: Between Now()-7 and Now(). Example:

Code:
PARTID | Price | Date
--------------+-------------+---------
111223344 | 5 | 3/1/2015
111223344 | 7 | 3/2/2015
111223344 | 8 | 3/4/2015
111223344 | 10 | 11/22/2014
111223344 | 20 | 10/1/2014
111223355 | 5 | 2/5/2015
111223355 | 6 | 2/1/2015

to:

What I want:

Code:
PARTID | avgPrice | MinDate
--------------+----------------+-------------
111223344 | 10 | 10/1/2014
111223355 | 5.5 | 2/1/2015

View 4 Replies View Related

Queries :: Rounding Average Values - Result With Two Decimal Places?

Apr 23, 2013

I'm trying to get this query to work:

Code:
SELECT Avg(Round([Final 205].[LastOfSumOfRehLOS],2)) AS AvgInpLOS, Avg(Round([LastOfSumOfWaitLOS],1)) AS AvgWaitTime, Count([Final 205].PHN) AS Observations, [Final 205].LastOfIntensity AS Intensity_Level
FROM [Final 205]
GROUP BY [Final 205].LastOfIntensity;

It doesn't found the result to two decimal places.

View 1 Replies View Related

Queries :: Calculate Differential Between Average Earnings And Percentage Within Crosstab Query

Apr 9, 2014

I obtained data for earnings by industry for men and women over time (5 years). I developed crosstab query that showed the average earnings for men and women for the 5 years.

Now I want within this query to calculate the differential between men and womens average earnings and calculate a percentage within this crosstab query. I tried to use Expressionbuilder with little luck.

View 6 Replies View Related

Queries :: Moving Date Information

Apr 16, 2014

I am trying to split a field that typically contains a revision level and a revision date in a single entry as follows:

(Rev Level) {Space} (Date: MM/DD/YY)
REL 10-30-14
A 11-20-14
C 11/13/14

Note: dates use either - or / as separators. 99% of the time, there is a space between revision level and date.

Is there a simple way to write an update query to take the value in the current field, strip away all letters and spaces (but not - or /), and insert the remaining value into the revision_date field?

View 4 Replies View Related

Queries :: Average IF Statement-syntax Error (missing Operator) In Query Expression

Jun 5, 2014

I am trying to get Average If function to access sql. I have columns Period and Costs_Per_Capita, result should be like like this:

Costs_Per_Capita Period CALCULATED_Period_Avg_Costs
15,505 1 15976.27582
16,368 1 15976.27582
16,037 1 15976.27582
15,995 1 15976.27582
15,000 2 16000
17,000 2 16000

I tried:

SELECT Costs.Costs_Per_Capita, Costs.Period
IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1),
(Select AVG(Costs_Per_Capita) From Costs Where Period = 2)
AS result
FROM Costs;

But get "syntax error (missing operator) in a query expression ..."

View 8 Replies View Related

Queries :: Get Average Repair Price Of Multiple Part Numbers With Same Root Number

Nov 19, 2013

I need to get an average repair price of multiple part numbers with the same root number i.e.

8 each 8TJ124GGN1
4 each 8TJ124GGM1
7 each 8TJ124GGP1

First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.

If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages. HOWEVER, if I use the expression:

AdjustedAvgLabor: Int((Sum([LaborPrice])-Max([LaborPrice])- Min([LaborPrice]))/(Count([LaborPrice])-2))

…to get a more accurate average by throwing out the high and low values, I get a:

“…expression too complicated to be evaluated”

when I try to average the averages in the second query using the AVG function.

I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.

View 8 Replies View Related

Queries :: Average Calculated Field From A Previous Query - Crosstab Data Type Mismatch

Jun 3, 2014

I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.

The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.

The formula is

Code:
CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))

View 5 Replies View Related

What About This Average?...Field Average...

Feb 16, 2007

Hi guys!!!!

I try to find an answer in the forum about "Average Fields",but ican't
I am confused:(

I wan't to export Avg Of the fields like in the panel below:

View 2 Replies View Related

Queries Vs Code

Jan 30, 2008

If one has a query and vb routine that does the same thing, in an overall Access environment, is there any advantage to using one over the other ?
Just curious.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved