# Average From Multiple Columns

Hi,

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!

Cheers

## 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?

## SubForm To Average Multiple Values

note - period is 'accounting period' for a calendar month...

table
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)

subform
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.

## 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.

Toni

## 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.

## 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 worksat.store = store.storenumber, employee
WHERE worksat.store = store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;

## Multiple Columns For Crosstab

hi

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

thanks

## Querying Multiple Columns

Hi

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.

Thankyou.
Kip;)

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

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

Job1......Job2......Job3......Job4......Job5
Joe.......Bob.......Jim.......Fred......Jeff
Joe.......Joe.......Jeff......Jim.......Jeff
Bob.......Bob.......Jeff......Bob.......Jim

and so on and so forth.

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

Bob
Fred
Jeff
Jim
Joe

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!)

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

B11.Europe.

## Searching Across Multiple Tables With Same Columns....

Ok,

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?

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

## 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?

Thanks!

## Multiple Columns - Version: 2007 (12.0)

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.

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

Mike
Jack
Bob
25
32
36
Essex
Surrey
Newcastle

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

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

SELECT DISTINCT [Hoods].[C1]
FROM Hoods
WHERE C1<>"";

Are there any suggestions?

Thanks

## 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.
Example;
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.

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

Mike
Jack
Bob
25
32
36
Essex
Surrey
Newcastle

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!

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

Mike

## Multiple Columns In Combobox On DAP - Version: 2003 (11.0)

Is it possible to have multiple columns displayed in a combobox on a DAP. I do this all the time on my forms but it doesn't appear to be the case that one could do it. Perhaps Rob might have an answer. anyone else know if there is a way and if so how?

Cheers

## Multiple Two Columns' Data - Version: 2003 (11.0)

what is the function for multiple two columns' numbers? for example

column 1- unit weight
column 2- unit qty

(unit weight) * (unit qty)= total weight

## Crosstab With Multiple Columns? - Version: 2003 (11.0)

If I have a tempTable that has a few columns of data that I want to show in a crosstab, is it possible to show two columns as headers. For example;
2000 | Payment count | 2001 | Payment count
Client
Client
Client

I have a Client Row Heading
I have a Year Column Heading
I have a Total Payments Value

I have another field Payment Count I would like to use as a second value for each year.

Is this possible?

Thanks,

## Multiple Columns On One Page - Version: 2003 (11.0)

Is it possible to set a report to print multiple columns of one field on a page in a report?

## Multiple Columns In Report - Version: 2002 (10.0) XP

I am trying to create a report that has 12 columns (legal size document) and can't quite get the spacing to shrink in between the columns. I have tried using the page setup function to minimize the spacing but some reason that does not work. Also, I would like labels to appear in the first column only but can't figure out how to get these to show up only once and not repeat 12 times which takes up a lot space. Any suggestions or comments would be greatly appreciated.

Thanks,
Joe

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:

## 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;

## Getting Information From Multiple Columns Into One Column - Version: 2003 (11.0)

I have a table that has 96 fields that I need to put into a singlr column either through a query or coding. Here is an example of what I am wanting to do:

Existing layout

ID, Field1, Field2, Field4, ...Field96
1, 10, 15, 60, 5
2, 20, 20, 50, 10
3, 20, 100, 150, 60

End Result

ID, Values, Field Name
1, 10, Field1
1, 15, Field2
1, 60, Field3
1, 5, Field96
2, 20, Field1
2, 20, Field2
2, 50, Field3
2, 10, Field96
3, 20, Field1
3, 100, Field2
3, 150, Field3
3, 60, Field96

Any assistance on getting me started would be greatly appreciated

## Multiple Columns In Detail Section - Version: 2002 (10.0) XP

How do I get a report to show multiple columns in the detail section?

## List Detail Results In Multiple Columns - Version: 97 (8.0)

How do I make a list of records show up in two or 3 columns instead of one? i.e. Say I have 15 records in my detail and I want 3 columns of 5 names instead of one column with 15 names?

is this possible?

thanks!

## Output To Multiple Columns Per Page - Version: 2003 (11.0)

I have a list of movie titles. I want to create a report that outputs the titles to multiple columns per page. I cannot figure out how to do that. Any help would be greatly appreciated.

Thanks,
Nike

## Combine Data From Multiple Columns Into One Column - Version: 2003 (11.0)

I am constructing a research database on students' top 3 favorite ice cream flavors. I have attached a sample of the information that will be used.

IceCreamSurveytbl

StudentID

StudentName

IceCreamFlavor1

IceCreamFlavor2

IceCreamFlavor3

The information will be entered into a table via a form. I would like to combine all the ice cream flavors data into one column and calculate the percentage of each ice cream chosen in a report.

I would also like to take each IceCreamFlavor1, IceCreamFlavor2, and IceCreamFlavor3 data and put it in a report and calculate the percentage for that field.

For instance:

Chris: Chocolate, Vanilla, Strawberry

Meagan: Strawberry, Vanilla, Cookies & Cream.

Report1:

Chocolate: 17%

Vanilla: 33%

Strawberry: 33%

Report2:

Favorite--------------2nd Favorite-------3rd Favorite

Chocolate: 50%-----Vanilla: 100%-----Strawberry: 50%

Big E

Edited by: bige23 on Thu Mar 1 14:06:34 EST 2007.

## Data Access Page Combo Box - Multiple Columns - Version: 2002 (10.0) XP

I am trying to replicate a form as a data access page. The form has a combo box where both a firstname and surname field appear in the dropdown. Data access pages seems to only allow one field as the ListdisplayField. I have the ID number in the listBoundField - is there any way to get both the firstname and surname for this ID number in the listDisplayField? At the moment I have surname as the listDisplayField but need first name to uniquely identify a couple of people.

Many thanks if you can help.