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 Replies


ADVERTISEMENT

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 :: 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 :: 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 :: 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

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

Reports :: Count Number Of Monthly Calls - Average Function In Footer

Jul 7, 2014

I have a report that counts the number of monthly calls. What I would like is an average of the monthly calls in the report footer.

My total for a particular month is =Count([Date]) and I named the unbound control MonthlyTotal.

This is in the DateFooter section of the report.

I then put an unbound control in the report footer and used the expression = Avg([MonthlyTotal]).

Of course when I changed from design view to report view, it asked me for an input of [MonthlyTotal].

I then tried =Avg([Reports]![qryLetterWritersbyDate]![MonthlyTotal]) and while it didn't ask me for an input, there was nothing in the ubound control in the report footer.

I have search for an answer, but all I find is using a query. Is what I am attempting to do possible? If so, how?

View 5 Replies View Related

Queries :: Update A Query Based On Results From Another Query Using Count Function

Apr 2, 2013

I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).

I have 3 queries which count how many patients come in 5, 4 and 3 times/week.

In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".

I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.

(I'm not using SQL view, I'm using the query design view)

In the "update to:" row, I use the Build function and locate the count I'm looking for.

Problem: when I run the query I get the error: Operation must use an updateable query.

View 3 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 :: Using If Function For Query

Oct 11, 2013

I have a query driving a report that fills in the on time or late based on comparing the release date to the production due date. The code works except for if we haven't released it yet and the release date is void. I need it to then check versus today's date and then populate the late/on time. But I need this to take a back seat to if both fields are populated. Here is the code I am currently using and I have attached a screen shot of the query with the 2 boxes highlighted in red.

On Time / Late Eng: IIf([Released to Prod]<=[Prod Due Date],"On Time",IIf([Released to Prod]>[Prod Due Date],"Late",""))

View 7 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 :: Max Function In Query By Row Not Column

Apr 3, 2015

I have a table that holds the dates of various approvals for documents.

Approval DatesDocumentID
ApproverA_DateApproved
ApproverB_DateApproved
ApproverC_DateApproved
LastUpdated

In a query, I'd like there to be a column with the date of the most recent approval (max date) for each document. Using the following formula in the Expression Builder gives me the error below.

Code:
Name: Max( [Projects]![ApproverA_DateApproved],[Projects]![ApproverB_DateApproved],[Projects]![ApproverC_DateApproved])

"The expression you entered has a function containing the wrong number of arguments"

How can I get the most recent date a document was approved by any approver?

BTW - I initially thought I could just use the date the record was last updated. However, sometimes people remove their approval. This causes the LastUpdated date to be wrong (for this purpose), since it then reflects the date they removed their approval, not the most recent date that someone gave it.

View 3 Replies View Related

Queries :: Using DateValue Function With Query

Aug 1, 2014

How do I use the DateValue function with a query that pulls data between dates? Here is my code below. I'm getting an error that my expression is too complex or incorrect.

Code:
SELECT IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))) AS LOB, Count(tblPACSTemplate.temID) AS CountOftemID, tblPACSTemplate.Type, DateValue([ApprovedDate]) AS ApproveDate

[Code] ....

View 8 Replies View Related

Queries :: Group By Function In A Query

Apr 8, 2014

I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.

View 2 Replies View Related

Queries :: Last Function In Totals Query

Apr 26, 2013

I'm trying to build (a rather simple) totals-query, but the "last" function doesn't give me the right values.Suppose I have a database where the amount of products that were sold are stored per month. It's possible that some products are not sold in some months. Now, I want the LAST REAL VALUE (= amount) for each product, no matter what month :

prod1 = 4
prod2 = 3
prod3 = 3

How can I do that, because the LAST-function gives me
prod1 = (empty)
prod2 = 3
prod3 = (empty)

View 6 Replies View Related

Queries :: Using Max Function In Aggregate Query

Mar 25, 2015

I have a list of product sales for various regions.How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?For example, If I run an aggregate query using the max function I still get the record for region:

SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region
FROM Stats
GROUP BY Stats.ProdCode, Stats.Region;

I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)

View 3 Replies View Related

Queries :: Using Choose Function In Query

Sep 28, 2013

I have an option group that lists a variety of categories which I want to query on.

I know that the option group only returns values.

In the Option Group, On Time = 1, Over Due =2 ,,,,

I am using a Choose function in my query. That is working fine. But ... I would like to have a last choice called "All" such that when selected all data is shown (i.e., On Time, Over Due, ....).

I am using the following:

Choose ([optiongrpStatus],"On Time", "Over Due", .... ,???) as my query criteria.

Have not figured what I can put in the last choice to get all data.

Tried "Is Not Null", tried "On Time or Over Due" - know these don't make sense -just wanted to try.

View 7 Replies View Related

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 14 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 :: Does Rnd Function Work With A Union Query

Aug 29, 2013

I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below

SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);

But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?

SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA

View 9 Replies View Related

Queries :: Query Or Function To Update Another Field Value

Nov 21, 2013

Would I use an update query or function to complete my task?

Task: Automatically update [Status] based on DateDiff calculation of [RequestDate] and [DueDate]

Issue: [Status] can be one of 6 values, the user can manually select Review or Completed.

[Status] values: Review; Completed; Due in 24; Due in 24-48; Beyond 48; Over Due

Query: Below is the query I just started to ignore the calculation if either Review or Completed exist.

Expr1: IIf([Status]="Review",[Status],IIf([Status]="Completed",[Status],DateDiff('d',[RequestDate],[DueDate])))If the value from above DateDiff equals to the below, I want to update the [Status] field to the [Status] value.

Due in 24 = (0-1)
Due in 24-48 = (2-3)
Beyond 48 = (greater than or equal to 4)
Over Due= (-1)

View 14 Replies View Related

Queries :: Search Function In Parameter Query

Nov 18, 2014

I have a parameter query in which consists of a search statement as below:-

Search: [Model] & " " & [FiscalYear]

It works very well. I am wonder if there is any options available to affect the search results. As far as I understand, when I enter the key word into the pop up box, it will search the fields sequentially and populate the result. Can I enter different key word for each field? If so, what is the syntax for entering the key words.

View 2 Replies View Related

Queries :: Instr Function In Select Query

Jun 21, 2013

I need to select the second word from a product description. Here are two examples;

2156015 Dunlop SP-30
1756514 Goodyear Sport

I found the correct code on another website to do this in Excel;

=MID([field], FIND(" ",[field],1)+1, FIND(" ",[field],FIND(" ",[field],1)+1)-(FIND(" ",[field],FIND(" ",[field],1))))

It works a treat. In order for it to work in Access you apparently change FIND to Instr.

Now this worked for the person on the other forum but when I try it, it doesn't and I get the #Error returned.

Here is what I am using. Description is obviously the field I am selecting from;

TEST: Mid([Description],InStr(" ",[Description],1)+1,InStr(" ",[Description],InStr(" ",[Description],1)+1)-(InStr(" ",[Description],InStr(" ",[Description],1))))

View 9 Replies View Related

Queries :: Replace Function In Query With Sub Table

Feb 14, 2014

I am running a query for an apparel manufacturing facility. In my query I have a table called 'OrderForm' which is where the orders are put in. There are more than one type of fabric that can be a part of an apparel item, so as a result, there are multiple fields pulling from the 'FabricType' table. In order to get this to work in my query I created 'SubTables' for the different fabric fields. For example, I have tblFrontfab, tblbackfab, tblsleevefab, and tblcollarfab which are just extra copies of the 'FabricType' table. The actual question is that when I want to replace characters like ,./& in the fabric field but I can't use the replace function. It says that it is too complex to calculate.

I used this notation
Frontfab: Replace([tblFrontfab].[fabric],".","")
and the error was that it is to complex to evaluate.

View 1 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







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