average 2

[macca007]

Hi i have this query which i would like to find the average length, height and width

what is the criteria for this to work

the sql is this

SELECT treatment.HOSP_NO, treatment.TREATDATE, treatment.SURNAME, treatment.FIRST_NAME, treatment.[DIAGNOSIS GROUP], treatment.Embolised, treatment.TRT_VOLUME, treatment.LESION, treatment.PREV_RS, treatment.LENGTH, treatment.HEIGHT, treatment.WIDTH
FROM treatment
WHERE (((treatment.TREATDATE) Between #1/1/2003# And #4/29/2004#) AND ((treatment.Embolised)=-1) AND ((treatment.LESION)=1) AND ((treatment.PREV_RS)=0));

cheers

 

[lespaul]

This should work:

SELECT treatment.HOSP_NO, treatment.TREATDATE, treatment.SURNAME, treatment.FIRST_NAME, treatment.[DIAGNOSIS GROUP], treatment.Embolised, treatment.TRT_VOLUME, treatment.LESION, treatment.PREV_RS, AVG(treatment.LENGTH), AVG(treatment.HEIGHT), AVG(treatment.WIDTH)
FROM treatment
WHERE (((treatment.TREATDATE) Between #1/1/2003# And #4/29/2004#) AND ((treatment.Embolised)=-1) AND ((treatment.LESION)=1) AND ((treatment.PREV_RS)=0))
GROUP BY treatment.HOSP_NO, treatment.TREATDATE, treatment.SURNAME, treatment.FIRST_NAME, treatment.[DIAGNOSIS GROUP], treatment.Embolised, treatment.TRT_VOLUME, treatment.LESION, treatment.PREV_RS;

Leslie

 

[cdagenais]

Is this what you are looking to do? I just used the Totals functionality of Access.

SELECT treatment.HOSP_NO, treatment.TREATDATE , treatment.SURNAME , treatment.FIRST_NAME, treatment.[DIAGNOSIS GROUP], treatment.Embolised , treatment.TRT_VOLUME , treatment.LESION , treatment.PREV_RS , Avg(treatment.LENGTH) , Avg(treatment.HEIGHT), Avg(treatment.WIDTH)
FROM treatment
WHERE ((([treatment].[TREATDATE]) Between #1/1/2003# And #4/29/2004#) AND (([treatment].[Embolised])=-1) AND (([treatment].[LESION])=1) AND (([treatment].[PREV_RS])=0))
GROUP BY treatment.HOSP_NO, treatment.TREATDATE, treatment.SURNAME, treatment.FIRST_NAME, treatment.[DIAGNOSIS GROUP], treatment.Embolised, treatment.TRT_VOLUME, treatment.LESION, treatment.PREV_RS;

 

[cdagenais]

lol, 2 for 1

 

[cdagenais]

Thank You for the Star! Very Appreciated!

Cindy

http://www.aslconsulting.com/EN/Index.asp