Reports :: Report Is Not Returning Expected Averages
May 9, 2013
My database is set up to track call evaluations with 4 fields for number data (S, A/C, C/E and B) each of these have a possible point total. I also have a percentage field to track out of total possible points.When I run my query I get a list of each of the totals for each of the evaluations with the associates names (as expected).I take that query and try to run a report wizard to give me an average socre for each associate. and the system returns averages of 0 or an odd number that does not make sense.when I use the =Avg([fieldname]) process I get an accurate average of the total but can not get it to do a "subtotal" for each associate.
View Replies
ADVERTISEMENT
Mar 2, 2007
Hi all:
running access 2000. I have a query that I selected to return the Top 1 record sorted descending on the date so it would pick the most recent entry. I did it by selecting Top 5 from the drop down box in query design mode and then changed the 5 to a 1 using SQL View from qry design.
SELECT TOP 1 Class.CourseID, User.UserName, Class.TrainerID, Class.ClassLocationID, Class.ElapsedTime, Class.ClassDate, Class.Comments
FROM [User] INNER JOIN Class ON User.UserID = Class.UserID
ORDER BY Class.ClassDate DESC;
It still gives me as many records as are entered on the system. How can I get JUST the most recent record entry?
thanks:confused:
View 3 Replies
View Related
Sep 7, 2007
O.k. I have a query working, and it's calculating perfectly and I'm reporting on it fine. However I noticed that when I run the query it doesn't populate the equation for all the results. Let me explain further.
I have a main form for tracking company information, and a sub form that tracks departments for that company and waste breakdown information. We take measurements for the company in two ways. 1 - a total weight for the day, and 2 - we weigh out categories of waste (within the sub form that tracks the department stuff)..we then calculate what percentage of the daily waste a specific category is. This calculation is done in a query, and works fine for the first department of every company, however when it moves to the next department for the same company, all I get is #Error in the field. eg./
Company "X"
Total Daily Weight = 750 Kilograms
Department "Shipping"
Category - Plastic Bottles - 20 Kilograms
I then have a query that calculates what % 20 Kilograms is of 750, then a report based on the query. This works fine; However in my report I look at the next department, with the exact same informaton as above...I see #Error returned in the field.:confused:
Does all of that make sense?? Is there a way to make sure my query calculation flows through all of the departments??
Thanks.
View 9 Replies
View Related
Aug 20, 2013
I am designing queries to return averages for quality test data.
I have this query that functions as I want it too [URL] .....
It returns the averages of all the values received for different tests for a lot number (the lot number criteria should be filled out as well)
When I want the query to be more specific and average only certain box numbers in the lot (that start with the prefix PB") the query does not return an average for box numbers starting with PB but splits them up, showing an average for PB1, PB2 instead of combining the data for those boxes into a single unified average ...
[URL] .....
View 3 Replies
View Related
Aug 7, 2015
I'm running a query from two related tables in the database.the table relationships and the query design. Instead of doing what I want it to do/what I think I'm asking it to - which is show the sum total number of weeks on the program for each student - it shows me the number of weeks for that student just for that claim, not the total for the student overall. I've added some 'dummy data' (2 records in the claim table relating to 1 student), and the query then returns the student twice in the results.
View 1 Replies
View Related
Oct 27, 2014
I have a report. behind that report is a query.
The query returns the parts used for each job. This could be 1 to many, so I get 1 to many rows returned in the query for each job. There can be a number of jobs to a work schedule (I'll call this WS). Each job is for a particular model. So I bascially have
WS1 JOB1 MODELx PART1
WS1 JOB1 MODELx PART2
WS1 JOB1 MODELx PART3
WS1 JOB1 MODELx PART4
WS1 JOB2 MODELw PART7
WS1 JOB2 MODELw PARt8
WS1 JOB3 MODELx PART5
WS1 JOB3 MODELx PART6
I want to count the number of jobs each model appears in ie MODELx appears in 2 jobs, MODELw appears in 1 job.
I've read DCOUNT can eliminate duplicates but I can't see how to use it to do so.
I have (general gist)
DCOUNT("model number"."query","model number" = [model num])
View 3 Replies
View Related
Apr 10, 2014
I a report based on query based on joined tables. Im using FK and PK accordingly in the tables and have a junction table.
I've attached a sample (removed all unique identifiers for privacy in case you're wondering).
Table structure as follows:
Employees tbl - Junction (License Link tbl) License No tbl, License Class tbl, Endorsements tbl
So the query runs on all of these tables.
When I build a report on the query, Simon shows up Six times. I suspect because he has six endorsements.
How do I produce a report on this table/query structure so that I get:
Simon.
Class, 1,2,3,4,5
Endorsement F,R,T,W,DG
View 1 Replies
View Related
Jan 17, 2006
Ive been pondering on this problem for far to long now so decided it was time to ask here !
I would like to return (based on a query) some records that would populate a report. I can set the source of the report as the query but my issue is that I need to actually have some code prior to report population.
EG if they have two products I want to list them product1/ product2 and as far as I know this isnt possible when you just set the source for the report...
The data is coming from SQL server so I have the option of a stored procedure but from trsting that seems to make things even more confusing.
Any help appreciated as Ive been stuck on this for far too long !!
Phil
View 2 Replies
View Related
Nov 5, 2006
I have a database containing values in 16 fields. the fields are filled in over a period of three years. I would like to be able to calculate the average of the last four values entered, regardless of when in the cycle the value is required. I have tryed to use quereies but connot find away to assign the four fields to the expression so that it is the last four values and if four don't exist, avearage what values there are.
View 2 Replies
View Related
Mar 29, 2007
Hi guys...
I have a problem...
In a query i calculate averages.How can averages looked like
Number,NumberNumber for example: like 2,35 and not like 2,346789?
Thanx in advance...
Johann
View 14 Replies
View Related
Jan 18, 2008
Hi,
In my database, I have clients and client hours. I need to calculate average client hours. My total client hours expression is: TTotals: Sum((Nz([SessionHoursCompleted])+Nz([OptionalHoursCompleted]))). I need to divide this number by the number of clients.
Any suggestions ie sum of client number, sum of clients, count of clients.
I've tried these and the results are incorrect.
Thank You
View 1 Replies
View Related
Oct 2, 2013
I have been an MS Excel man all along my career and I am a novice in MS Access.I have created a table, [Initial Customer Approval] which records data from a Form, [Initial Customer Approval]. Once the data is entered in the Form, I need to do some calculations based on the data entered in some of the fields in the form.I created 6 different queries for the six possible values in those fields. now for each of those queries I created respective reports.I placed a Print command button in the Form.
1. When I press the Print button it should open the report for the current record in the Form. (Currently It Opens all the reports simulatneously, with only one relevant report containing the current record; other opened reports being blank.)
2. If user presses the Print button before pressing Save button then system should prompt user.
Here is the code (Please note [reference number] is the unique ID generated for each record entered in the tabe through form):
Private bSaveClicked As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not bSaveClicked Then
MsgBox "You are trying to navigate away from the active record. Please either save your changes, or press ESC to cancel your changes.", vbOKOnly + vbInformation
Cancel = True
[code]...
View 5 Replies
View Related
Jun 15, 2007
I am trying to calculate the average patients age from 2671 records using this SQL:
SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);
why am I getting the result:
68.1131066106
I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.
thanks
View 3 Replies
View Related
Jan 4, 2006
Right, So i've got a table which has records for the Top 40 Music Charts.
I want to be able to find out the averages for each song taken from it's positions in the chart over the weeks:
http://www.playfm.orcon.net.nz/Chart4.GIF
So essentially I get something that tells me
[Lenny Kravits] [American Woman] [24.5]
[Silverchair] [Ana's Song (Open Fire)] [41.1]
and so on...
Any ideas?
Cheers guys,
Alex.
View 5 Replies
View Related
Aug 12, 2005
I have developed the following query to summarise a blast design at a mine site.
Pattern Depth m Subdrill Burden Spacing BCM/hole #Holes BCM
1.00 7.00 1.00 3.00 3.00 54 10 540
1.00 7.00 1.00 3.50 3.00 63 10 630
2.00 7.00 1.00 3.50 3.00 63 10 630
2.00 7.00 1.00 4.00 3.00 72 10 720
Now there will be many patterns, the above however only contains one (called 1). Now what i need is the average Depth, Subdrill, Burden, Spacing, bcm/h. In MS Excel I would have following:
Average BCM per hole = Sum of BCM / Sum of Holes
How to do this in Access? Then just to make it more difficult I want to have the average BCM for each different pattern.
View 1 Replies
View Related
Sep 18, 2007
Hi there,
once again I have a problem for which I am looking for some hints...
I still have one table, called tblTransactions, which contains security market transactions. For each buy and sell order, respectively it contains one data set with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).
The following code gives me all stocks, which are no longer part of the portfolio since they have been sold out completely for any arbitrarily chosen date (here 1/30/07) together with the date, on which the last position in a certain stock (identified by the ticker) were sold:
SELECT T.Ticker, max(T.Date) AS SellDate
FROM tblTransactions AS T
WHERE T.Date<=#1/30/2007#
GROUP BY T.Ticker
HAVING sum(T.Qty) =0
ORDER BY T.Ticker;
Now it becomes complicated: What I am looking for is a sub-query, which I want to add to the code above and which gives me the weighted average price at which the stocks were bought and sold, respectively if there have been more than one buy or sell transaction.
That is, for the following sample data of tblTransactions...
Date --- Ticker --- Quantity --- Price
01/01/07 --- AAA --- 50 --- $50
01/01/07 --- BBB --- 25 --- $75
01/10/07 --- BBB --- 75 --- $100
01/15/07 --- AAA --- 30 --- $60
01/20/07 --- BBB --- -100 --- $100
01/25/07 --- AAA --- -40 --- $120
01/26/07 --- AAA --- -40 --- $100
...the query I am looking for should give the following result as per 01/30/07:
Ticker --- SellDate --- WeightedAvgEntry --- WeightedAvgExit
AAA --- 01/26/07 --- $53.75 1) --- $110.00 3)
BBB --- 01/20/07 --- $93.75 2) --- $100.00
Notes / how to calculate the weighted averages:
1) (50*$50 + 30*$60) / 80 = $53.75
2) (25*$75 + 75*$100) / 100 = $93.75
3) (40*$120 + 40*$100) / 80 = $110.00
I do not have any clue at all how to solve that problem. I would be very happy for any hint that could lead to the right direction.
Best regards
JapanFreak
View 5 Replies
View Related
Jan 18, 2008
My database has 8 clients. During a sample date range, between 1/1/05 and 1/1/11, they worked a total of 348 hours. I need to query them for hours divided by client by date range. 3 clients, for instance, worked a total of 162 in the sample date range but the query is dividing the 162 hours worked
by all 8 clients. I need it to divide the hours in this case by 3. Needless to say, these numbers will change when a different date range is inputted but if the expressions are correct...
Currently, to calculate this number I am using this expression :
Averages: Sum((Nz([SessionHoursCompleted])+Nz([OptionalHoursCompleted])))/DCount("IDOC","spise clients_OLD_OLD").
A copy of the query 'Current Average/Total' is attached.
Thank you for your assistance.
View 1 Replies
View Related
Dec 28, 2014
I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.
On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.
On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.
I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.
Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.
=[rptP&LExpensesOverview]![AccessTotalsAmount]
=[rptP&LSalesOverview]![AccessTotalsTotal Sales]
To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.
As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).
View 2 Replies
View Related
Feb 15, 2007
I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.
Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.
Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?
View 10 Replies
View Related
Oct 28, 2007
Simple query for the right person!!!
Hi Guys,
Im in need of some help with a query that i am trying to set up. I have a table with data shown roughly below. And need to extract the average [call length] for each heading of [Call type] on a given date.
Ie. The query will be run and the date can be inputted. It would the display each category only once and the average of that category.
Call type |Call Length (Shown in Minutes)|Submitdate
Split Billing | 4 |27/10/07
Split Billing | 8 |27/10/07
Split Billing | 9 |27/10/07
Split Billing | 1 |27/10/07
Split Billing | 4 |27/10/07
Tariff changes | 2 |27/10/07
Tariff changes | 3 |27/10/07
Tariff changes | 8 |27/10/07
Tariff changes | 5 |27/10/07
Tariff changes | 5 |27/10/07
Billing Cycle | 1 |26/10/07
Billing Cycle | 2 |26/10/07
Billing Cycle | 3 |28/10/07
Billing Cycle | 20 |26/10/07
Billing Cycle | 15 |27/10/07
View 3 Replies
View Related
Nov 16, 2014
The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...
View 3 Replies
View Related
Dec 21, 2014
How do I hide the report footer based on the report's data ?
I'm trying to hide if number of users = 1
The report's data is a query built inside the report's RecordSource, not a self standing query.
View 10 Replies
View Related
Apr 10, 2014
I've done this once entirely by accident and can't seem to duplicate it...
I have a report. It has the following:
Report Header: Logo and title
Department Header
Supervisor Header
Group Header
Detail
Department Footer: Totals
Report Footer: Overall Totals for all departments
Here's my question.
I have combo boxes on my main form that filter this report. The combo boxes are referred to by the query that runs the report. How do I get proper unfiltered overall totals in my report footer?
View 4 Replies
View Related
Apr 18, 2013
I have a report that displays incidents, their details, consequences and a photo. Among the details is a severity rating high medium or low, I have been asked to make the report shorten the records which have been given a low severity (because it takes up as much space as the more important/severe ones).
The only method I can think of is to use the onformat event, to shrink and make invisible all the fields that I don't want to see if the severity field shows 'low'.
View 3 Replies
View Related
Jun 26, 2013
Is there a way to calculate three different rolling averages in one query?
I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.
Code:
SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num]
FROM LIST_WITH_TNC;
Is there a way to make this work?
View 2 Replies
View Related
Jul 16, 2014
I need to create a query where in the end, I will have four rows of data based on based on two combinations of WaterSourceType and Crop.
I need the query to bring back the results of the average Top N (lets say Top 10%) for each combination.
I have tried this every which way and I can't seem to get it grouped like I want it. I NEED to have four distinct rows with the average of the ProfitPerBushel for each grouping.
Basically, what this does is show me the average profitablity of the top 10% in each grouping.
WaterSourceType | Crop | ProfitPerBushel
Irrigated | Soybeans | ProfitPerBushel
Non-Irrigated | Soybeans | ProfitPerBushel
Irrigated | Corn | ProfitPerBushel
Non-Irrigated | Corn | ProfitPerBushel
View 9 Replies
View Related
