Skip to Content
0
Former Member
Nov 25, 2008 at 08:26 PM

sum of average in report with many joins

20 Views

hello all. i have read a few other threads that are similar to my situation but nothing i've found that helps my specific situation. it is probably in part to my lack of expertise but i am pretty much a newbie here so go easy on me.

in summary, i have an employee table (em), a rooms table (rm), and an organizational hierarchy table (org_hier). em and rm tables are joined on the building, floor, and room fields that are in each table. org_hier table can be linked to either em or rm.

basically i am just trying to take the average area of each room (rm.area). here may be multiple occupants in a room and i don't want rm.area to be counted more than once which is the reason for the average. i then want a sum of that average at each level of the org hierarchy by using groups and a grand total at the end. the trick is that i am only looking for rooms that house certain employees, restricted by the em.em_id. that is the only reason for the em table being included.

would you all suggest using subreports somehow or is there a SQL function that would work best? let me know if i am being too vague here. what should my joins be?

thanks in advance,

js