Hm, tricky query there.. but I think something like this might work:

SELECT Team.HomeName, SUM(IIf(Team.HomeId = Games.HomeId, Games.HomeScore, Games.AwayScore))
FROM (Games LEFT JOIN Team ON (Games.HomeId = Team.HomeId OR Games.AwayId = Team.AwayId)) LEFT JOIN Schedule ON (Games.DateId = Schedule.DateId)
WHERE Schedule.ScheduleDate <= #4/30/2006# AND Schedule.ScheduleDate >= #4/1/2006#;

I tried the example kindler and I get Microsoft Access message

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

I got the same message when I use the Month function.

Hi,Surfer

  why con't u try with sample database "Northwind sample" because i tried lots of think and i got the remedies



bye
------------------------
pap...

Oops! I forgot a line there, knew it looked a little bit off. Try this:

SELECT Team.HomeName, SUM(IIf(Team.HomeId = Games.HomeId, Games.HomeScore, Games.AwayScore))
FROM (Games LEFT JOIN Team ON (Games.HomeId = Team.HomeId OR Games.AwayId = Team.AwayId)) LEFT JOIN Schedule ON (Games.DateId = Schedule.DateId)
WHERE Schedule.ScheduleDate <= #4/30/2006# AND Schedule.ScheduleDate >= #4/1/2006#
GROUP BY Team.HomeName;