Not using yes/no fields and still creating a table for check boxes ...

[cowboy]

Allen Browne has an page explaining how to set up a database relationship of many to many using a connection table at the following link
http://allenbrowne.com/casu-23.html

I can set up what he has done easily, but I want to be able to show the information much like the matrix he shows at the top of the page and also include in the query or form "sports" that no one has checked.



Any help would be appreciated.

[June7]

That is displaying records horizontally (left to right). A crosstab query might work. Other methods are demonstrated in:

http://forums.aspfree.com/microsoft-...ry-322123.html

and another from Allen http://allenbrowne.com/func-concat.html

[cowboy]

I couldnt get it to work with union or crosstab, I put together a copy of his example. I will attach and when I get it finally I will attach again for everyone to be able to see.StudentSports.zip

[June7]

Is this what you want:

1. query StudSport
SELECT [Surname] & ", " & [FirstName] AS StudName, Student.StudentID, Sport.Sport, StudentSport.StudentSportID
FROM Student LEFT JOIN (Sport RIGHT JOIN StudentSport ON Sport.SportID = StudentSport.SportID) ON Student.StudentID = StudentSport.StudentID;

Getting column for each sport even if noone has selected is tricky. One way is to put dummy records in StudentSport or fudge them with a UNION. Use this query as StudSport:
SELECT [Surname] & ", " & [FirstName] AS StudName, Student.StudentID, Sport.Sport, StudentSport.StudentSportID
FROM Student LEFT JOIN (Sport RIGHT JOIN StudentSport ON Sport.SportID = StudentSport.SportID) ON Student.StudentID = StudentSport.StudentID
UNION SELECT "Dummy", "0", "Basketball", 1 FROM Student
UNION SELECT "Dummy", "0", "Football", 2 FROM Student
UNION SELECT "Dummy", "0", "Baseball", 3 FROM Student
UNION SELECT "Dummy", "0", "Tennis", 4 FROM Student
UNION SELECT "Dummy", "0", "Soccer", 5 FROM Student
UNION SELECT "Dummy", "0", "Volleyball", 6 FROM Student
UNION SELECT "Dummy", "0", "Ice Hockey", 7 FROM Student
UNION SELECT "Dummy", "0", "Underwater Badmiton", 8 FROM Student;

2. query StudSport_Crosstab
TRANSFORM Count(StudSport.StudentSportID) AS CountOfStudentSportID
SELECT StudSport.StudentID, StudSport.StudName
FROM StudSport
GROUP BY StudSport.StudentID, StudSport.StudName
PIVOT StudSport.Sport;

[cowboy]

That works well for the current situation, but the whole reason for using a many to many relationship with a connection table is so that the program can be changed by adding a sport to the table and everything else falls in place. If I have to go in and put specific union code in for each sport I add, I could just as easily use check boxes from the start right?

[cowboy]

I had some help from another forum and I was able to get it working, just wanted to share the results with everyone.
StudentSports.zip

[June7]

Of course, doh! All I had to do was reverse the join type of my first query and add the GROUP BY. I felt that tickle in back of my head telling me I was missing something and glad you got it solved.