Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Average From Multiple Columns


I've got all the columns i require in my query and and i've averaged these, however i need to create a final column which contains the Average of all the other Column Averages.. if you get what i mean!
Put it this way i have columns A, B, C. All the numbers containd within each column have been averaged so now each column contains a single value. ie.

A = 10
B = 20
C = 9

So now i have to create column D which will contain the average of those results above i.e.:

D = 13

I would preferable like to contain this within a single query rather than having to write a vb module and having to add extra tables ect.. but that might be the only way!

So if anyone could tell me the best way of achieving this it would be much appreciated. I've tried a couple things like concatenation:
Average: [Score1]&[Score2]&[Score3]

but this hasnt work.. no surprises really!


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How Do I Get The Average Speed Answered Based On The Sum Of Two Columns?
I am trying to get the Total average speed answered (ASA) based on two columns sums. (i.e. sum of calls handled and sum total speed answer). I need to get the total avg speed ans (formula is sum of total speed answer (tsa) divided by sum calls handled). I did an average query for asa but it gives me the avg for the rows(records) of the asa and not of the two columns. I need it for the two columns above. I was trying to change the formula in report design view, but I am not coming out with the correct answer. Are there any suggestions?

Please help!

View Replies !   View Related
SubForm To Average Multiple Values
note - period is 'accounting period' for a calendar month...

store (text) primary key with period
period (text) primary key with store
(store and period as a pair are unique in table)
a couple dozen fields for uniqe sales values

another table:
period - primary key
period end - date/time - lists last day of month, for calcultions

main form
store, period, and sales value fields
(for data entry)

based on query that averages each sales value for past 365 days (12 periods)

need help here
i'd like the subform to calculate based on store & period as entered on main form. how get that into query(obviously need to getthe end date from the periods table.) how? i've tried [forms]![main]![store] and all sorts of permutations with !'s and .'s
it does:, the query prompts me for store and start date before the main form opens, but does populate the sub-form.

View Replies !   View Related
Sum Multiple Columns And IIf
I have a table with the following fields: planned_volume, actual_volume and monthdate.

I need to come up with a query that will sum the volumes based on monthdate, like:

(fieldname)1/1/2007 - If monthdate = 1/1/2007 then SUM "planned_volume" where "monthdate" betweeen 1/1/2007 And 12/1/2007.

2/1/2007 - If monthdate = 2/1/2007 then SUM "actual_volume" where "monthdate" = 1/1/2007 and then SUM "planned_volume" where "monthdate" between 2/1/2007 And 12/1/2007 and end up with one combined total.

Continue on this path through 12/1/2007.

Is a Select Case function a possible solution to this or is it something that I can do in the query grid?

Thanks for any help.


View Replies !   View Related
Multiple Columns
I am trying to build a database for my extensive DVD collection and I want something that shows more than one record per line (I ultimately want something with just an image and title for each record with maybe 4 records per line). This is my first venture into using MS Access, but I've gotten the basics down and have already set up the table & form. The only time I have found something that would display the way I want things is in a report, but it will only display it with multiple columns in print preview mode (which won't work because the titles are going to be hyperlinks and need to be clickable). If anyone has any ideas, they would be greatly appreciated. If I'm not on the right track and this can be done from within a form, then please set me straight. Thanks again in advance.

View Replies !   View Related
Grouping Multiple Columns
I am trying to display five columns from three seperate tables. I thought the GROUP BY operator would fix this, but I have more than one column to select. The query is fine but I have redundant fields showing. Oh, I'm not worried about the calculation "[employee.salary]*0.1 AS Salary_After_Raise." I just want to concentrate on narrowing the fields down first.

SELECT ID, FirstName, LastName, Salary, [employee.salary]*0.1 AS Salary_After_Raise, City
FROM worksat INNER JOIN store ON = store.storenumber, employee
WHERE = store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;

View Replies !   View Related
Multiple Columns For Crosstab

is it possible to have multiple columns within a crosstab query??

if so please specify how. at the moment i am not allowed to specify column heading for more than one item


View Replies !   View Related
Querying Multiple Columns

I'm really hopeful that somebody can help us out here. I'll explain as best I can.

I've got a table, where details of particular online environments are kept. Each online environment is assigned to up to four people. By this, it may be assigned to nobody, or it may be assigned to four seperate people, or anywhere in between.

What i'm trying to create is a query where the user of the database can type in a particular persons name, and it will return all the details of any record where that person is mentioned in ANY of the four colums, which can then be used to generate a report for that particular individual.

However, i'm really unsure of how to do this, and am aware that there is probably a really simple solution to my quandry, that will leave me kicking myself up the rear like theres no tomorrow.

Any advice as to how to proceed would be hugely appreciated, genuinely.


View Replies !   View Related
Checking Multiple Columns + Asp
Question concerning how to check a value against multiple columns in Access with ASP.

Need help converting this to proper ASP and Access syntax

Code:myId = "test"myName = "monkey"SELECT * FROM table1 WHERE (programId1='" & myId & "' OR programId2='" & myId & "') AND (name1='" & myName & "' or name2='" & myName & "')"

is this doable with asp and Access?

If this helps explain it better, the following is how I would call it with PHP & mysql:

Code:$myId = "test";$myName = "monkey";SELECT * FROM table1 WHERE (programId1 = $myId OR programId2 = $myId) AND (name1=$myName OR name2=$myName)

Any help would be appreciated

View Replies !   View Related
Combining Multiple Columns Into One
I'm trying to make a payment report which prints a separate pay sheet for each employee based on what job they did. I don't want to get into too much detail here, but basically, a name is associated with each action in a setup like this:


and so on and so forth.

I want to have a query that gives one column that looks like this:


I already know how to sort and group by, but how would I write a query that contains one column, then another column when that one ends, then another, et cetera?

(I need to know this, because it would be a lot cleaner to show one report for all 5 jobs than a report for each job separately, and of course, that's how the boss wants it!)

View Replies !   View Related
Concatenating Various Values From Multiple Columns
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.

So for example

Column1 Column 2 Column 3

B11 Europe

would concatenate as:


View Replies !   View Related
Searching Across Multiple Tables With Same Columns....

I have three tables that are named different, but have the same column names, but different data in them. The data in each table is linked by a master table that has a primary key.

What I need is a way to make a form with a box where I can search on a value that could be in any of the three tables and then the associted information from the site will display in the above boxes.

(Site Master Table contains all site information and the primary key)
(The three tables are sales order information. Stage Only table, Install table, and srop ship table)

Any ideas?

View Replies !   View Related
Summing Multiple Columns In The Same Query...
Hello! I'm new here, and I'm back into Access after a few years of not using it.

What I'm trying to do seems simple, but I can't seem to get the sytax down.

I have 6 columns that have dollar figures in them. I want to get the totals for all 6 columns to show in one field. I'm using the sum([field]) to total the individual columns, but I can't seem to get all of them together in one sum. Is this possible?

Thanks!! :D

View Replies !   View Related
Way To Reset Multiple Columns Properties?
I am trying to generate a query that shows the data that the user indicates via check boxes.

What I did is a form (GenerateQuery) which lists down over 30 fields with their respective checkbox. When the query is run, it will open up another form (All details) with the massive froms in DS views but hide those field that are not checked.

For each checkbox that is checked, I have the follow code

If Forms!GenerateQuery.CheckClientName = True Then
Forms.Alldetails.ClientName.ColumnHidden = False
End If

First of all, I am not sure if this would even be the most efficient way of generating a query. Any advice would be most helpful.

The question I have is how to reset all the columns to .Columnhidden = true before I run the query everytime? Do I need to write 30 statements of the above and set them = true (initially all are hidden)? Or would there be a quicker way?


View Replies !   View Related
Moving Multiple Columns Data Into One Column?
How can I move multiple columns data into a single column so that:

Name Age Location
Mike 25 Essex
Jack 32 Surrey
Bob 36 Newcastle

appear in a single column with data appended column-wise as


Any help would be much appreciated. I'm a novice at VBA, so if anybody could do the code, it would be great!

View Replies !   View Related
Select Distinct From Multiple Columns In Same Table
I am trying to write a query that will select distinct values from three columns. The table name is Hoods. The columns are C1, C2, and C3. All three columns contain colors and can have the same values, but I only need to have a particular color selected once. For instance, all three columns contain "Black", but "Black" should only be listed once. The result of the query will be used in a drop down list.

The following works for one column:

FROM Hoods
WHERE C1<>"";

Are there any suggestions?


View Replies !   View Related
Comparing Similar Columns In Multiple Tables.
Hi Everyone,

I am trying to compare/relate two columns in two tables to each other in access, where the results shoud return similarities among both colums from both tables.Even if one column has some parts of it.
T1 T2
Name Members
John Johnson.kay
mike mike
Daniel Danielson.mic
Richard Richardson

I tried; "like[T1.Name]*" in the criteria section of Members.Need Help pls.

View Replies !   View Related
Moving Multiple Columns Data Into One Column?
How can I move multiple columns data into a single column so that:

Name Age Location
Mike 25 Essex
Jack 32 Surrey
Bob 36 Newcastle

appear in a single column with data appended column-wise as


Any help would be much appreciated. I'm a novice at VBA, so if anybody could help me with the code, it would be great!

View Replies !   View Related
Dynamically Adding Multiple Columns Question
I coulnt figure out how to reply to a reply. This is a follow up question to a previous post.

I can now create a new column in my access db through my web form.

Here is the script:
ALTER TABLE FAQ ADD City varchar(30)

this adds a new column called city.
Here is my problem:
Every time a user asks a question, that question will be in the new column created.
I need the newly created columns to have unique names.
I tried creating a form with a text file (to simulate where they type in their question) then post to post.asp.
post.asp holds the script.
I replaced city with ::T1:: (the variable of the text file), but got an error.

Creating columns dynamically is worthless unless there is a way to make the column names unique.

Can anyone shed some light on this problem for me?

Much appreciated


View Replies !   View Related
Joining Multiple Queryes With Diferent Number Of Columns
Hy everyone
I have to make a report based on data from 5 queryes, the problem is that any of the 5 queryes could be with no vales in it and the queryes has diferent numbers of columns. I tyed union query but it gave me an eror because of diferent number of columns.
In design wiev I aded the queryes but now if any of the query has no data in it the output quey is void.
Here is the SQL statement from the query the db is to large to post it:

SELECT qryIpsosFiltruUserData.*, qryIpsosRacitorUserData.*, qryIpsosSilozUserData.*, qryPastaMixerUserData.*, qryRocaGipsUserData.*, *
FROM qryIpsosFiltruUserData, qryIpsosRacitorUserData, qryIpsosSilozUserData, qryPastaMixerUserData, qryRocaGipsUserData;

Please help a newbie.:confused:

View Replies !   View Related
What About This Average?...Field Average...
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 Replies !   View Related

Copyright 2005-08, All rights reserved