Taking the Average of a Row 1

[jvet4]

I have a database that records software download times for a particular number of machines in a particular number of cases.&nbsp;&nbsp;I have set up a query that displays the download time in the following format:<br><br>CaseNum&nbsp;&nbsp;&nbsp;&nbsp;#1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#4&nbsp;&nbsp;&nbsp;&nbsp;#5&nbsp;&nbsp;&nbsp;&nbsp;#6&nbsp;&nbsp;&nbsp;&nbsp;#7&nbsp;&nbsp;&nbsp;&nbsp;#8<br>&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;nn:ss&nbsp;&nbsp;nn:ss&nbsp;&nbsp;nn:ss&nbsp;&nbsp;nn:ss nn:ss nn:ss nn:ss nn:ss<br><br>Where the fields labeled #1...#8 are the machine numbers.&nbsp;&nbsp;<br><br>Since for each case, the same thing is being downloaded to each machine, I would like to get the average time for each case (i.e. (#1+#2+...+#8)/8).&nbsp;&nbsp;All the information I have read has made a references to averaging columns, however I can not seem to find how to take the average of rows.&nbsp;&nbsp;<br><br>Any thoughts?<br><br>Thanks, Jason

 

[kathryn]

Just a note, before I answer your question:&nbsp;&nbsp;You shouldn't use #, or any non-alphanumeric character, in your field names.&nbsp;&nbsp;Access is easily confused.&nbsp;&nbsp;For example, when it sees # it thinks that there is a date lurking around, because # is the date delimiter.<br><br>Now, back to your question.&nbsp;&nbsp;In your query, create a new field which will show the average.<br><br>Go to a new column in your query grid (it must be to the right of all the numbered columns for the calculation to work) and enter<br><br>&nbsp;&nbsp;&nbsp;Average: ([Field1]+[Field2]+...+[Field8])/8<br><br>Check out the following help topic for more info:<br>&quot;Create a field that performs custom calculations or manipulates field values in a query&quot;<br><br>Good luck <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[jvet4]

Kathryn, <br>Thanks for the help, but this has given me a new problem (it seem that everytime I get something working, some other problem emerges!)<br><br>Is it possible to have different parts of a query appear in the same field? Or, is there a way to write a query that will average only those fields with data?<br><br>In my query, half of the test cases have data for all 8 fields so the (1+2+3...+8)/8 way of averaging will work.&nbsp;&nbsp;However, other test cases have less then 8 so the above way of taking the average yeilds a blank result.&nbsp;&nbsp;<br><br>If possible, I'd like all the results to appear in the same field so it is easy to view.&nbsp;&nbsp;Can this be done?<br><br>Jason

 

[kathryn]

I think that we have to go back to your original table of data and do a group on Case Number and do an average that way.<br><br>Can you tell me what your table looks like? <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[cmmrfrds]

Getting blank is probably due to having null in the fields that contain no data. Here is the technique to force a zero into the null field. Use this function&nbsp;&nbsp;Nz(fieldname,0)<br><br>Average: (Nz([Field1],0)+Nz([Field2],0)+...+Nz([Field8],0))/8<br><br>

 

[jvet4]

Basically here is what my table looks like.&nbsp;&nbsp;I have 30 test cases the involve downloading software to 8 different computers.&nbsp;&nbsp;Each set of software has either 2 or 4 distinct parts (DL1-DL4).&nbsp;&nbsp;Case 1 involves downloading 2 pieces of software, case 2 involves downloading 4 different pieces, however, since the database is going to be a template for future testing, all the cases are setup to record 4 different pieces.&nbsp;&nbsp;To make this a little more complicated DL1 can further be broken into three more parts.&nbsp;&nbsp;For each piece of software downloaded there is a start and completion time.&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The table that holds this data is pretty messy (this is the first large database that i have setup).&nbsp;&nbsp;For each case the table looks like this:<br><br>Case# - Comp1DL1Start,Comp1DL1Complete,Comp1DL2Start,Comp1DL2Complete...Comp8DL4Start, Comp8DL4Complete.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;There is also a form that was set up to make the data entry process easier (this form mirrors old data sheets that have the computers 1-8 listed across the top and the start and completion times for each of the four donwloads listed down the side.)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I have developed a set of queries that isolate the downloads into their specific parts (query 1 lists the duration of DL1 for all 8 computers and all 30 cases and so on).&nbsp;&nbsp;Based on this query I want to be able to get the average download time for DL1...DL4 for each case(case average) and the overall average of DL1...DL4 (the average of the case averages).&nbsp;&nbsp;<br>&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;For DL2,DL3,andDL4 there are no problems with averaging the data.&nbsp;&nbsp;The problem occurs with DL1 because in certain cases all three parts are being downloaded to seperate computers.&nbsp;&nbsp;In my data base I have seperated these cases into three different parts and entered the data into the table that way (this way if the user wants to view only DL1-1, it can be done with out confusion).&nbsp;&nbsp;I have a problem when I am trying to average in these particular cases because using (Comp1+...+Comp8/8) contains several null values.&nbsp;&nbsp;In these particular cases DL1-1 uses computers 1 and 2 (DL1-2 uses 3-6 and DL1-3 uses 7 and 8).&nbsp;&nbsp;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Since this pattern holds I am trying to develop a way that will average both the cases that have all 8 computers being used and the cases that have fewer computers used and display them in the same field called &quot;average&quot;.<br>&nbsp;&nbsp;This code won't work but it shows a little clearer what I mean:<br>&nbsp;&nbsp;((Comp1+...Comp8)/8) and (Comp1+Comp2)/2 AS Average<br><br>Is there a way to do this?<br><br>This could probably be the most unclear explination possible but I hope it helps a little more.<br><br><br>Thanks, Jason