SQL help

[StevePalmer08]

Hello,

I have a very simple database for recording attendence to classes

Table 'Player'
PlayerID AutoNumber
FirstName Text
LastName Text
...

Table 'Class'
Class AutoNumber
Date Date/Time

Table 'Player Attendence'
PlayerAttendence AutoNumber
Attended Yes/No
Class Number
Player Number

'Player Attendence'.Class and 'Player Attendence'.Player are foreign keys to the Class and Player tables.

I have the following query:

SELECT Player.FirstName, Player.LastName, [Player Attendence].Attended, Player.[Class Type], [Player Attendence].Class
FROM Player LEFT OUTER JOIN [Player Attendence] ON Player.PlayerID = [Player Attendence].Player
WHERE (((Player.[Class Type])=1));

What I would like is to default [Player Attendence].Class to the max value in the table Class i.e

SELECT Max(Class.Class)
FROM Class;

In this way to record attendance to a class the user would add a new entry to the 'Class' table and then fire the modified query. This would return a list of Players pre-defaulting the Class to max:

FirstName LastName Attended Class
Fred Fredericks null 3
...

Is this possible? As you can see I am new to access to please excuse if I am missing the obvious.

Thanks

 

[namliam]

You give your own answer...

SELECT Player.FirstName, Player.LastName, [Player Attendence].Attended, Player.[Class Type], [Player Attendence].Class
FROM Player LEFT OUTER JOIN [Player Attendence] ON Player.PlayerID = [Player Attendence].Player
WHERE (((Player.[Class Type])=(SELECT Max(Class.Class)
FROM Class)));

Finaly a word to the wize... Do not use spaces in tables names or any names for that mater...

 

[StevePalmer08]

If only!

The query I posted gives me all the Players by virtue of the left out join. When I complicate the query to:

SELECT Player.FirstName, Player.LastName, [Player Attendence].Attended, Player.[Class Type], [Player Attendence].Class
FROM Player LEFT JOIN [Player Attendence] ON Player.PlayerID = [Player Attendence].Player
WHERE (((Player.[Class Type])=1))
AND [Player Attendence].Class =(SELECT Max(Class.Class)
FROM Class);

I get no rows back. I am missing something!

 

[namliam]

That is probably cause there are no players in that (new) class yet?

 

[StevePalmer08]

You are correct. However, to my mind using a left outer join should bring back all rows in the left table (Players) even where they do not exist in the right hand table. this works with the first query but not when I complicate with a sub-query.

Maybe I should step back to my requirement. i want to list all players and have a tick box so that they can self register for a class. Given that the players are 6 and up I do not want them to have to do more than tick a box. Am I coming at this wrong or should my outer join SQL work?

 

[namliam]

Not if you are putting a constraint on the outer join table... which you are doing...

SELECT Player.FirstName, Player.LastName, [Player Attendence].Attended, Player.[Class Type], [Player Attendence].Class
FROM Player LEFT JOIN [Player Attendence] ON Player.PlayerID = [Player Attendence].Player
WHERE (((Player.[Class Type])=1))
AND [Player Attendence].Class =(SELECT Max(Class.Class) FROM Class);

See you are demanding the "[Player Attendence].Class" to have a value... where in the outer join this field would be NULL.... Change it to...

AND ([Player Attendence].Class =(SELECT Max(Class.Class) FROM Class)
     OR [Player Attendence].Class is null);

and you should find more like what you are looking for....

Greets

 

[StevePalmer08]

namliam,

Thanks for the support on this. I am not there though. If I do as you say then Player Fred will not appear on class 3 if they attended class 2. The reason being that [Player Attendence].Class = 2 not null. What I am trying to acheive is every class wiping the slate clean and creating a complete list of players. I think what I have is a design problem. Help appreciated!

 

[namliam]

If what you are saying is, that fred is not supposed to attend class 3... therefor should not show up there... Yes you have a design issue there... as you have currently (or not known to me) no way of telling who is supposed to attend which classes....

The query tho is sound...(IMHO)

 

[StevePalmer08]

Start again,

I have a list of Players who are all eligable to attend a class (stored in table Player). I have a table Class which holds the instance of the class. Whenever a class run (Monday, Tuesday ...) I want to produce a complete list of the children with a tick box next to their name. If they attend that instance of the class then that should result in an record being writtent to the table 'Player Attendence'. The table 'Player Attendence' has FK's from the tables Class and Player.

I hoped that using an outer join I would be able to list all the players and have a check box. I can do that using:

SELECT Player.FirstName, Player.LastName, [Player Attendence].Attended, Player.[Class Type], [Player Attendence].Class
FROM Player LEFT OUTER JOIN [Player Attendence] ON Player.PlayerID = [Player Attendence].Player:

However, I also need to set the [Player Attendence].Class to equal the highest record in the class table. Is it possible to do that and still have a complete list of players i.e. outer join will show all the potential players?

 

[namliam]

It is if you change the Select max() thing to the a subselect where you do the outer join... I you get what I mean...