Is a sub query the solution?

[bulgroz]

Hi there

what i'm trying to do is something of the sort...

SELECT TOP 10 AVG(items.field1) AS field1, AVG(items.field2) AS field2, items.date, items.staff_no, staff.staff_name, staff.staff_no
RIGHT JOIN staff ON staff.staff_no = items.staff_no
WHERE
items.dags >=#01.12.2004# AND
items.dags <=#31.12.2004# AND
AVG(items.field1) > 1500
GROUP BY staff.staff_no
ORDER BY AVG(items.field1) DESC

of course i cannot use the date field like above cause it is a part of the table that i'm grouping on. I've tried all sorts of sub-query schemes, i imagine that is the solution, but to no gain.
if anyone could assist me with this it would be greatly appreciated

thanks in advance

 

[r937]

try this --

select top 10 
       avg(items.field1) as field1
     , avg(items.field2) as field2
     , items.date
     , items.staff_no
     , staff.staff_name
     , staff.staff_no
  from staff
left outer
  join items
    on staff.staff_no = items.staff_no
   and items.dags >= #01.12.2004# 
   and items.dags <= #31.12.2004# 
group 
    by staff.staff_no
[b]having[/b] avg(items.field1) > 1500
order 
    by avg(items.field1) desc

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[bulgroz]

Hi there r937 and thanks for the fast reply.

I tried your solution but access just gives me the error "join expression not supported"

 

[r937]

try

left outer
  join items
    on (
       staff.staff_no = items.staff_no
   and items.dags >= #01.12.2004# 
   and items.dags <= #31.12.2004# 
       )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[bulgroz]

Ok, tried that

Seems like it’s getting somewhere. now i get the error

"The specified field 'staff_no' could refer to more than one table listed in the FROM clause of your SQL statement"

(my FROM clause just specifies "FROM staff" like you typed above)

I tried removing staff.staff_no from the select clause an than I got the error

"You tried to execute a query that does not include the specified expression 'dags' as part of an aggregate function"

and the same one of course if I leave staff.staff_no and take out items.staff_no

 

[r937]

could you please show your latest query

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[bulgroz]

Sure, goes like...

SELECT TOP 10 Avg(items.field1) AS field1,  Avg(items.field2) AS field2, items.dags, items.staff_no, staff.staff_name, staff.staff_no
FROM staff
LEFT OUTER JOIN items on (
	staff.staff_no = items.staff_no
	AND items.dags >=#01.12.2004#
	AND items.dags <=#01.12.2005#
)
GROUP BY staff.staff_no
HAVING Avg(items.field2) >= 1500
ORDER BY Avg(items.field1) DESC

I managed to get a running code though by using this...

SELECT TOP 10 Avg(items.field1) AS field1, Avg(items.field2) AS field2, staff.staff_no as Dags, staff.staff_no, staff.staff_name 
FROM staff
RIGHT JOIN items ON staff.staff_no = items.staff_no
WHERE
	items.dags >= #01.12.2004# AND
	items.dags <= #31.12.2004# AND
	items.field2 > 1500
GROUP BY staff.staff_no, staff.staff_name

... it doesn’t look very reassuring, especially that staff_no AS dags but that prevents Access for prompting me with a input box for that field since items.dags is not included in the SELECT clause
I would love to have a more solid code, so if you see what is up with the other one please let me know

thanks for all the help!

 

[r937]

actually, now that i think about it, access usually complains if you assign an alias that's the same as the column

so avg(foo) as foo should raise an error

the thing about LEFT versus RIGHT joins is determined by two things -- which table you want all the rows of, and whether you prefer LEFT versus RIGHT (because they are actually equivalent, if you flip over the tables)

i never write RIGHT joins

i dunno what's up with your data, sorry

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[PHV]

Why not simply this ?
SELECT TOP 10 AVG(items.field1) AS avg_field1, AVG(items.field2) AS avg_field2, items.staff_no, staff.staff_name
FROM items LEFT JOIN staff ON items.staff_no = staff.staff_no
WHERE items.dags Between #2004-12-01# And #2004-12-31#
GROUP BY items.staff_no, staff.staff_name
HAVING AVG(items.field1) > 1500
ORDER BY AVG(items.field1) DESC;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244