Newbie but trying! Searched this site and found some things that partially addressed my question, but I couldn't make it work, so here is my problem, hope someone might be able to offer suggestions.
I'm using 2 tables to keep track of participating teachers in different schools. Every year the same (and new) teachers register. Table one records what schools are signed up and by which teacher for that particular year. Table two has pertinet data on the teacher (ie: school, phone number, years with program, etc) records are linked by unique school codes.
I've created a query that returns all teachers registered by all years. I'm attempting to create a crosstab query that returns past year and current year records so that I can see who registered last year but hasn't yet this year.
My problem is that the crosstab query returns the same record in both colums instead of leaving blanks where there is no registration date. IE:
Teacher School Year 2003 Year 2004
Miss A Happy Daze 09/09/2003 9/09/2003
Miss B First Start 10/10/2003 10/10/2003
I want the 2004 (current year colum) to be blank if a teacher hasn't registered for the year. In addition, because this data base will be ultimately used by non-techie types, I would like the query to use current year/past year rather than having to always change the dates.
This is the query that I'm using to create a crosstab query.
SELECT tblPLAYMasterList.YEAR, tblPLAYMasterList.SCHOOL, tblTeachers.Name, tblTeachers.Phone, tblPLAYMasterList.[SIGN-UP DATED]
FROM tblPLAYMasterList INNER JOIN tblTeachers ON tblPLAYMasterList.CODE = tblTeachers.[School Code]
GROUP BY tblPLAYMasterList.YEAR, tblPLAYMasterList.SCHOOL, tblTeachers.Name, tblTeachers.Phone, tblPLAYMasterList.[SIGN-UP DATED], tblPLAYMasterList.CODE
ORDER BY tblPLAYMasterList.YEAR, tblPLAYMasterList.SCHOOL, tblPLAYMasterList.[SIGN-UP DATED];
Again, any help would be most appreciated.
![[turkey]](/data/assets/smilies/turkey.gif "[turkey] [turkey]")
I'm using 2 tables to keep track of participating teachers in different schools. Every year the same (and new) teachers register. Table one records what schools are signed up and by which teacher for that particular year. Table two has pertinet data on the teacher (ie: school, phone number, years with program, etc) records are linked by unique school codes.
I've created a query that returns all teachers registered by all years. I'm attempting to create a crosstab query that returns past year and current year records so that I can see who registered last year but hasn't yet this year.
My problem is that the crosstab query returns the same record in both colums instead of leaving blanks where there is no registration date. IE:
Teacher School Year 2003 Year 2004
Miss A Happy Daze 09/09/2003 9/09/2003
Miss B First Start 10/10/2003 10/10/2003
I want the 2004 (current year colum) to be blank if a teacher hasn't registered for the year. In addition, because this data base will be ultimately used by non-techie types, I would like the query to use current year/past year rather than having to always change the dates.
This is the query that I'm using to create a crosstab query.
SELECT tblPLAYMasterList.YEAR, tblPLAYMasterList.SCHOOL, tblTeachers.Name, tblTeachers.Phone, tblPLAYMasterList.[SIGN-UP DATED]
FROM tblPLAYMasterList INNER JOIN tblTeachers ON tblPLAYMasterList.CODE = tblTeachers.[School Code]
GROUP BY tblPLAYMasterList.YEAR, tblPLAYMasterList.SCHOOL, tblTeachers.Name, tblTeachers.Phone, tblPLAYMasterList.[SIGN-UP DATED], tblPLAYMasterList.CODE
ORDER BY tblPLAYMasterList.YEAR, tblPLAYMasterList.SCHOOL, tblPLAYMasterList.[SIGN-UP DATED];
Again, any help would be most appreciated.
