Help with above average sum in Access SQL

[ozchadl]

I have a horse database which has the following tables:
horse (horse_id, name, colour, sire, dam, born, died, gender)
Showsite (show_id, name, address, suburb)
Judge (judge_id, name, suburb)
Event (event_id, show_id, event_name, judge_id)
Entry (event_id, horse_id, place)
Prize (event_id, place, money)

I need the career winnings code to do a sum in the 'where' or 'having' so that I can find out which horses have an above average amount

so if sum(prize.money ) > (sum(prize.mone y)/count(horse.nam e) where horse.name <> 'unknown dam' and horse.name <> 'unknown sire')
This would display the correct records

The code is from 2 seperate queries.

Thanks for your help.

career winnings code

SELECT entry.Horse_id, Horse.Name, Sum(prize.Money ) AS ['Career Winnings']
FROM (Horse LEFT JOIN entry ON Horse.Horse_id = entry.Horse_id) LEFT JOIN prize ON entry.Event_id = prize.Event_id
WHERE (((entry.Place) =[prize].[place]))
GROUP BY entry.Horse_id, Horse.Name
HAVING (((Horse.Name)< >"unknown sire" Or (Horse.Name)<>" unknown dam"));


Average of prize money won code

SELECT Sum(prize.Money ) AS AverageOfMoney
FROM (Horse LEFT JOIN entry ON Horse.Horse_id = entry.Horse_id) LEFT JOIN prize ON entry.Event_id = prize.Event_id
WHERE (((((([entry].[Place])=[prize].[place]))/(select count(horse.nam e) from horse HAVING (((Horse.Name)< >"unknown sire" and (Horse.Name)<>" unknown dam"))))<>False ));

[colintis]

Try this:

SELECT entry.Horse_id, Horse.Name, Sum(prize.Money) AS ['Career Winnings']
FROM (Horse LEFT JOIN entry ON Horse.Horse_id = entry.Horse_id) LEFT JOIN prize ON entry.Event_id = prize.Event_id
WHERE (((entry.Place)=[prize].[place]))
GROUP BY entry.Horse_id, Horse.Name
HAVING (((Horse.Name)<>"unknown sire" Or (Horse.Name)<>"unknown dam"))
AND Sum(prize.Money) > (SELECT Sum(prize.Money) AS AverageOfMoney
    FROM (Horse LEFT JOIN entry ON Horse.Horse_id = entry.Horse_id) 
    LEFT JOIN prize ON entry.Event_id = prize.Event_id
    WHERE (((((([entry].[Place])=[prize].[place]))/(select count(horse.name) from horse 
    HAVING (((Horse.Name)<>"unknown sire" and (Horse.Name)<>"unknown dam"))))<>False)));

[ozchadl]

I have tried that code and no results were displayed.
Thanks for your help