Averages From Multiple Colums With Ignored Values
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 Replies
ADVERTISEMENT
Feb 19, 2008
Hi Gurus,
Here's my situation. I have three columns in Access:
Attribute Name, DateValue, CharValue.
I wish to create a make table query which does the following:
If Attribute Name = 'approved_date' then take the value from DateValue Column and populate it in a New table 'new'.
but if the
Attribute Name = 'description' the take the value from CharValue column and populate it in the same table 'new' in a different column. Please help, how to achieve this.
Thanks,
Kon
View 1 Replies
View Related
Aug 20, 2013
I'm not sure if this is the right forum. If not let me know and I'll move the thread.
Table 1
Date
Measurement A
Measurement B
Measurement C
Table 2
Table 1 ID Link
Data 1
Tables are linked 1-to-many from table 1 to table 2 by ID.
I would like to average the Data1 data per Table1 ID and report it with the Table 1 Measurements.
View 1 Replies
View Related
Mar 28, 2006
Hello
I have had a look through the forum and I am not sure if this can be done. I have a table. i.e. with columns Decsription 1, Cost 1, Description 2, Cost 2 Description 3, Cost 3 description 4, cost 4 etc.
I am looking to try and gave a combo box show this information as follows. I.e
Colum 1 on Combo; Column 2 on Combo
Description 1 Cost 1
Description 2 Cost 2
Description 3 Cost 3
Description 4 Cost 4
I want to be able to auto fill some fields on a form based on the combo box selection.
Has anyone done this before and can it be done.
Thanks
View 1 Replies
View Related
Jul 12, 2005
I have a table which has a column and some of them are filled and some are blank. I want to fill the blank ones with the previous values, how do I do this. Thanks for any help..
View 4 Replies
View Related
Jun 29, 2007
OK, here we go...... Is there a way to convert multiple rows to a single record. For example:
Rec# Existing Data
001 | 1000
002 | 2001
003 | 3252
004 | 6573
005 | 8983
006 | 9897
Is there a way to put this all in one record, like this:
Rec# . 1st# . 2nd# . 3rd# . 4th# . 5th# . 6th#
001 | 1000 | 2001 | 3252 | 6573 | 8983 | 9897
And can you do this from the query design view?
Just curious.
View 4 Replies
View Related
Jun 10, 2006
Is there a way to hide a column in a query that does not have any data in it?
I have tables that are populated with YES/NO boxes and I only want to bring back the information for fields that are YES. Currently, the queries run and bring back all fields.
Each query will only have 1 result as they are limited by the primary key.
Thanks!
View 1 Replies
View Related
Jan 30, 2008
I am making this harder than it has to be i'm sure. I had a previous post of auto-texting a field, well why can't I just take two fields I have, such as the ID field which is an autonumber field and combine that with a type field that I have which is D.
If I could merge this in a query, wouldn't it give me the results as D01, D02, etc?
I couldn't find anything helpful online, one side said do the following.
SELECT receivables2.type+receivables2.ID
FROM receivables2;
Only I get errors.
I also tried this.
SELECT merge(receivables2.type; receivables2.ID);
FROM receivables2;
But it says merge is an invalid expression.
Could someone please help?
View 6 Replies
View Related
Sep 25, 2006
I have a form displayed as data sheet, how can I block the column so that the final user will not modify the width of the columns?
M
View 1 Replies
View Related
Nov 14, 2006
hi all
i am adding colums like
Col_1 + Col_2 + col_3 + ...so on
now when a field is empty/null there is error in result
how to overcome this please help
i am adding colums ( not SUM(col_1) )
thanks in advance
NMK
View 1 Replies
View Related
Jul 25, 2007
I created a fixed header cross tab query that totals up how many photos of each size there is in an order. I wrote some visual basic code to total up the price (kind of complex with the different packages) but any orders without at least one of every size photo completely locks up Access. The following is the code:
Private Sub Text66_DblClick(Cancel As Integer)
Dim Units
Dim UnitsCache
Dim PackagesCache
Dim F4x5Cache
Dim F5x7Cache
Dim E8x10Cache
Dim E11x14Cache
Dim S16x21Cache
Dim T21x24Cache
Dim WalletsCache
Dim T4x5
Dim T5x7
Dim T8x10
Dim T11x14
Dim T16x21
Dim T21x24
Dim TWallets
If [4x5] = Null Then
T4x5 = 1
Else
T4x5 = [4x5]
End If
If [5x7] = Null Then
T5x7 = 1
Else
T5x7 = [5x7]
End If
If [8x10] = Null Then
T8x10 = 1
Else
T8x10 = [8x10]
End If
If [11x14] = Null Then
T11x14 = 1
Else
T11x14 = [11x14]
End If
If [16x21] = Null Then
T16x21 = 1
Else
T16x21 = [16x21]
End If
If [21x24] = Null Then
T21x24 = 1
Else
T12x24 = [21x24]
End If
If [Wallets] = Null Then
TWallets = 1
Else
TWallets = [Wallets]
End If
F4x5Cache = T4x5
F5x7Cache = T5x7
E8x10Cache = T8x10
E11x14Cache = T11x14
S16x21Cache = T16x21
T21x24Cache = T21x24
WalletsCache = TWallets 8
UnitsCache = T8x10 + T5x7 2 + T4x5 4
Text58 = 0
Text56 = " "
Text54 = " "
Text52 = " "
RerunPackages:
PackagesCache = "x"
If [T21x24Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "F"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
[T21x24Cache] = [T21x24Cache] - 1
Text58 = Text58 + 439
GoTo FoundOne
End If
If [S16x21Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "E"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
[S16x21Cache] = [S16x21Cache] - 1
Text58 = Text58 + 305
GoTo FoundOne
End If
If [E11x14Cache] > 0 And [WalletsCache] >= 1 And [UnitsCache] >= 3 Then
PackagesCache = "D"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 3
[E11x14Cache] = [E11x14Cache] - 1
Text58 = Text58 + 236
GoTo FoundOne
End If
If [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "C"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
Text58 = Text58 + 169
GoTo FoundOne
End If
If [WalletsCache] >= 1 And [UnitsCache] >= 3 Then
PackagesCache = "B"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 3
Text58 = Text58 + 127
GoTo FoundOne
End If
If [WalletsCache] >= 1 And [UnitsCache] >= 2 Then
PackagesCache = "A"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 2
Text58 = Text58 + 89
GoTo FoundOne
End If
FoundOne:
If PackagesCache <> "x" Then
If Text56 = " " Then
Text56 = PackagesCache
GoTo RerunPackages
ElseIf Text54 = " " Then
Text54 = PackagesCache
GoTo RerunPackages
ElseIf Text52 = " " Then
Text52 = PackagesCache
GoTo RerunPackages
End If
End If
UnitsCache = (T8x10 + T5x7 2 + T4x5 4) - UnitsCache
Do
If F4x5Cache < 4 Or UnitsCache = 0 Then
GoTo Skip4x5
End If
F4x5Cache = F4x5Cache - 4
UnitsCache = UnitsCache - 1
Loop
Skip4x5:
Do
If F5x7Cache < 2 Or UnitsCache = 0 Then
GoTo Skip5x7
End If
F5x7Cache = F5x7Cache - 2
UnitsCache = UnitsCache - 1
Loop
Skip5x7:
Do
If E8x10Cache < 1 Or UnitsCache = 0 Then
GoTo Skip8x10
End If
E8x10Cache = E8x10Cache - 1
UnitsCache = UnitsCache - 1
Loop
Skip8x10:
Text42 = 0
Redo:
If WalletsCache >= 5 Then
Text42 = [Text42] + 40
WalletsCache = [WalletsCache] - 5
GoTo Redo
End If
If WalletsCache = 4 Then
Text42 = [Text42] + 37
ElseIf WalletsCache = 3 Then
Text42 = [Text42] + 33
ElseIf WalletsCache = 2 Then
Text42 = [Text42] + 28
ElseIf WalletsCache = 1 Then
Text42 = [Text42] + 18
End If
Text28 = [F4x5Cache] * 12
Text30 = [F5x7Cache] * 25
Text32 = [E8x10Cache] * 40
Text34 = [E11x14Cache] * 110
Text36 = [S16x21Cache] * 155
Text38 = [T21x24Cache] * 249
Text40 = [25x31] * 362
Text44 = [16x20 Collage] * 210
Text46 = [Hard Cover w/Poetry] * 212
Text48 = [Hard Cover Book] * 98
Text50 = [Soft Cover Book] * 68
Text66 = Text28 + Text30 + Text32 + Text34 + Text36 + Text38 + Text40 + Text42 + Text44 + Text46 + Text48 + Text50 + Text58 - Text60
End Sub
View 1 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
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
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
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
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 1 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
Apr 28, 2014
Let's say I have a table sort of like this one: [URL] .... (Table 1)
What I want to do is make another table that references the first table: [URL] ... (Table 2)
I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.
View 1 Replies
View Related
Mar 29, 2006
I am using the formula:
Normal Pay: Sum(IIf([PAYCODE]="STD" Or [PAYCODE]="PAY_R" Or [PAYCODE]="RATE*",[AMT],0)) .
as an expression and need my query just to pick up one row with a total sum of these values, however I am still receiving 2 or 3 lines of seperate values.
Can anyone help?
Thanks
Paul
View 1 Replies
View Related
