Averages For Duplicate Cells

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 Replies


ADVERTISEMENT

Fill Blank Cells With Appropriate Data From Preceeding Cells

Jun 28, 2005

I have table1:

Name, Date, Points
Peter, 1.8.2005, 100
________2.8.2005, 200
Paul, 1.8.2005, 100
________4.8.2005, 300

etc. and I need to fill the blanks with Peter, Peter... , Paul, Paul, ... etc. so, that I can later take it as a group ID and work with the data.

Preferably with SELECT query, but insert or update is also possible.

View 3 Replies View Related

Calculating Averages

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

Numbers In Averages

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

Averages Query

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

Odd Averages Result From Query

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

Weighted Averages In A Query

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

Weighted Averages In Sub-query...

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

Averages Query Not Accurate

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

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 10 Replies View Related

Help With Grouping Items And Calculating Averages

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

Queries :: Calculating Three Different Rolling Averages In One Query?

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

Queries :: Top N For Each Combination Based On Group Averages

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

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

Queries :: Averages Of Values Based On Dates

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

Queries :: Returning Averages For Quality Test Data

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

Calculating Field Specific Averages From Records In Separate Tables

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

Forms :: Prevent Duplicate Values With Alert After User Enters Duplicate Value

Dec 30, 2013

We are working on an Access (2007) database that is on a SharePoint Site (2007).

Currently the form is operational, but there is one last thing that would be nice to have.

The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.

In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.

All data is currently bound, so once the user makes a change it is made, no submit button is required.

We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.

TABLE - UPDATED HEADCOUNT
COLUMN in UPDATED HEADCOUNT - EMPID
FORM CELL user will input an EMPID - newEMPID
FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID

So below is what we are trying to do, we are sure there are a few commands missing....

=IIF(newEMPID=dlookup([UPDATED HEADCOUNT]![EMPID]), newEMPID, "Error, EMPID already exists")

View 11 Replies View Related

Images In Cells...

Feb 19, 2008

Hello,

I did a cursory search of the forum and didn't find anything (probably more my search than the content). I also think I know the answer to this, but I want to clear it up once and for all.

Is it true that I cannot insert an image into an Access Table in a way that it treats the image like data (e.g., it comes up on reports as entered).

If so, and I suspect it is so, what would be the best way to associate a row with a certain image in Access? Here's what I'm hoping to do: I have a list of projects and all of them have a status. Right now the "status" column includes the text "Green," "Yellow," or "Red." I would rather have this text display as green, yellow, or red color rather than text. Is this possible?

Thank you!! :D

View 4 Replies View Related

Is There Anyway I Can Format The Cells

Apr 24, 2007

Hello,
say for example I have a particular column in Access that I would like to force users to enter as percentage only, is there a way to format the cell as in Excel to create such effect? Thanks a lot!

Regards,
Anyi

View 1 Replies View Related

Combining Two Cells

Jul 18, 2007

In a query design view, I have two fields, LastName and FirstName. In the Field of a blank column I enter [LastName]&[FirstName]. In the query the last and first names are now connected , like SmithJohn. How do I put a space and or a comma to separate them?

View 1 Replies View Related

There's Got To Be A Better Way! Merging Cells...

Aug 31, 2006

Once again, I'm working with a file that I got from somewhere else that isn't in *quite* the right format.

What I have is a table listing all of the titles and their holdings we have in various journal databases. The table looks something like this:

|____TITLE____|___COVERAGE______|____DATABASE___|
|_ABC Journal_|_1998 until 2000_|_EBSCOhost DB__|
|_ABC Journal_|_1953 until 1986_|_Proquest DB___|
|_ABC Journal_|_1980 until 2006_|_Free Journals_|
|_XYZ Journal_|_1899 until 1956_|_CINAHL DB_____|
|_XYZ Journal_|_2000 until 2006_|_EBSCOhost DB__|
|_XYZ Journal_|_1955 until 1999_|_Proquest DB___|

The titles, as you can see, are listed more than once, with one record for each holdings record from each database where that title is found. For my purposes, I need the holdings to show up all together. I can do this in a report, by sorting by the title, but I really need to have it all together in its own cell--one line per title. The above table should look more like this:
|____TITLE____|_______________________COVERAGE____ _________________|
|_ABC Journal_|_1998 until 2000_,_1953 until 1986_,_1980 until 2006_|
|_XYZ Journal_|_1899 until 1956_,_2000 until 2006_,_1955 until 1999_|

I don't really care what happens to the database field, but if it helps, the title and the database fields together would be a unique combination in the table.

Currently, I am working on an exported version of this table in excel, merging the cells 2 by 2 with a function command like this: =A2&" "&B2. Since there are 32366 records, I hope there is a quicker way. At this rate, I'm not sure if I'll be done for 100 years.

View 6 Replies View Related

Modules & VBA :: Sum Row Cells Value?

Sep 17, 2013

I'I need to randomly input values into row cells and when press enter key, it calculates the total value and show in another sheet. Example, in sheet1, I input random values in A1,C1,D1,Z1 and when I press enter key, it calculates the total value and appear in the first row cell, A1, of sheet2. All by VBA coding.

View 1 Replies View Related

Drop Down Cells In Database

Aug 24, 2005

i'm creating a table and would like to have a list of items to chosefrom in a cell to keep from having to type an answer. e.g countries , or Prefix

thank you

View 2 Replies View Related

Updating EMPTY Cells

Sep 2, 2007

I need to update column 'customer' in table 'db' with a name of a customer.
Currently this column is empty from data.

I do following , but it doesn't work.

UPDATE db SET Customer = 'Microsoft'
WHERE Customer='';

Where is the problem.

View 2 Replies View Related

Pasting To Multiple Cells - Help Please

Dec 16, 2007

How do I paste to multiple cells in access 2007? Basically I want to copy one
cell in the dataview and then highlight maybe 10 cells and copy the info from the one cell into the highlighted 10 cells at one time. I had no problem doing this with 2003 but in 2007 this seems to be an issue. Any help is appreciated!

View 1 Replies View Related







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