Help Please on query

[jotb]

Hello All:

I need to find an average. Within one field I have data that is as follows: 6.0, 6.5 7.0 (FIELD NAME IS DST). Another field called 1FR has data like 2154, 2212, 2346. I previously ran a query using the field (1FR) and the fIELD (DST).When I averaged the datasheet view had an average for 6.0, 6.5 and 7.0 seperately for each DST. I would like to have one average for all 3 DST'S combined. How do I perform this query step by step?

Thank you in advance,
Joe

 

[psemianonymous]

Somewhere in your query you have the field "1FR" and under it written "Group By". Delete this field entirely and you will be left with one overall average.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[dodge20]

I believe foolio is correct, if you want to use the sql you can just use

select AVG(DST) AVERAGE_DST
from table



Dodge20

 

[jotb]

Hello Foolio and Dodge:

This did not work for me. Within the (DST) field, I have other data types like 8.0, 8.5, 9.0, 10.0. I just want to use 6.0, 6.5. and 7.0. In order to perfom this query I need to use the following fields: (TRK), (Sur), (dst) and (1fr)
In the criteria I use AQU for the (TRK) field, M for the (SUR) FIELD. When I placed 6.0, 6.5 and 7.0 in the criteria straight down under the field (dst)(like 6.0 or 6.5 or 7.0)I ran the query but each distance had an average. 6.0 was 2254, 6.5 was 2276, and 7.0 was 2300. I would like to combine the 3 distances and get one calculation like 2250. Do I need the wizard for this or another type of query? Is a formula needed to combine all 3 distances?

Thank you and I'm sorry to take up your time,
Regards,
Joe

 

[psemianonymous]

This is what you do:

1. Open query in design view.
2. Find the "DST" field.
3. Where it says "Group By" under the DST field, change to "Where"
4. Save


It should run properly now. If you use "WHERE" instead of "GROUP BY", it WILL restrict to values that meet the criteria, BUT it will not distinguish between the three values.

Hopefully this is what you need.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[dodge20]

select AVG(1FR) AVERAGE_DST
from table
where DST in ('6.0','6.5','7.0')

Dodge20

 

[jotb]

Hello:

I'm losing my mind with this. Here is what I did exactly. I selected a query in design view. I added the table and closed out of the box. In design view in the first row, I dropped down to field and put in (track). I proceeded to the next row and dropped down to field and put in (surface). I went next to the third row and dropped down to field and put in "distance". Moving on the the 4th row, I dropped down to the field and put in (1st fraction). I then hit the total button and itr said (group by) in all 4 rows. I put in (average) in the (1st fraction field) and (where) in the (distance field). I moved back to the first row for (track) and dropped down to (criteria) and typed in AQU because I have several tracks. I moved over to the next row (surface) and put in (main). In the criteria for (distance) I put in the following: ('6.0,'6.5','7.0') on the same line. I left (1st fraction empty in the criteria. I ran the query in datasheet view and an error came up informing me about (comma's). I'm sure I did not follow to the instructions you both gave me and that is why it did not work. I guess I,m brain dead. I need someone to walk me through this step by step. I'm so sorry!

Best regards,
Joe

 

[dodge20]

Can you switch to sql view and post the code that you have? I will be able to help you better.

Dodge20

 

[jotb]

Hello Dodge:

I found another way. Using the wizard, I put in the 4 fields (track,distance,first fraction,surface)and clicked on summary options. Instead of using average, I clicked on sum and checked count records. In design view, I put in the criteria for track (aqueduct), surface (main), distance (6.0,6.5.7.0). I left first fraction empty. I ran the query and added the sums of the first fractions to the distance and then divided by the total records. More than likely, this was not a shortcut but it worked. I just want to thank you for and Foolio for all your help.

Best regards,
Joe