What we are doing
I'm currently righting a competition with my brother for the coming english premier ship football season. using an access database as the back end and pushing it to the web via asp.

The problem
I'm having a problem with my sql I needed to get a list of all people that belong to a league plus there score. (sounds simple hang on moment and please read on)

The players are contain within a table called tblleaguemembers and the points are store in a table called tblpointsweekly. The link between these fields is identrant. Each user can have a maxmium of ten entries per week and the best score counts.

Players can join the competiton and not yet have a record in tblpointsweekly. The score for that week is reduced to the highest score each player has by using the max aggregate function. and it is also filter to show only people from a certain league.

I have been able to get a list of all players that have an entry in tblleaguemembers and an entry in tblpointsweekly. This does not however show the people who do not yet have an entry in tblpointsweekly.

Is there any way to do either

1) Get a list that contains all the entrants that are tblleaguemembers and there score where appropriate.
or
2)A list of all entrants that don't appear in tblpointsweekly but do in tblleaguemembers.

Either answer will do as on the second one can added to the bottom of the tempary table I have in memeory created using asp code.

There are a number of other tables that you will see in the code bellow. these are tblweeks this stores the date. tblentrants this stores entrant details and tbleagues this stores league information.

Currently work sql
SELECT tblLeagues.idLeagues, tblLeagueMembers.idEntrant, tblPointsWeekly.idEntrant, tblEntrants.FullName, tblLeagueMembers.MemberConfirmed, tblWeeks.Date, Max(tblPointsWeekly.Score) AS MaxOfScore

FROM tblWeeks INNER JOIN (tblLeagues INNER JOIN ((tblEntrants INNER JOIN tblLeagueMembers ON tblEntrants.idEntrant = tblLeagueMembers.idEntrant) INNER JOIN tblPointsWeekly ON tblEntrants.idEntrant = tblPointsWeekly.idEntrant) ON tblLeagues.idLeagues = tblLeagueMembers.idLeague) ON tblWeeks.idWeek = tblPointsWeekly.idWeek

GROUP BY tblLeagues.idLeagues, tblLeagueMembers.idEntrant, tblPointsWeekly.idEntrant, tblEntrants.FullName, tblLeagueMembers.MemberConfirmed, tblWeeks.Date

HAVING (((tblLeagues.idLeagues)=4))

ORDER BY tblPointsWeekly.idEntrant, Max(tblPointsWeekly.Score) DESC;


PS I do not wish to have queries in the datebase or to create other tables in access.

IF ANY ONE CAN HELP PLEASE. If you need any further explanation then please don't hesitate to ask.