Left join query problem

J

Jabret

I am trying to create a report that has teacher class schedules periods 0-6.
Where there is no record of a class, I want a blank to appear in the
teacher's shedule. There are two semesters so I did a crosstab to get the
class name to appear under each term; however, I cannot get periods 0-6 to
show up for each teacher. Here is the latest attempt:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
FROM Periods LEFT JOIN [extract-2] ON Periods.Period =
[extract-2].sectionScheduleperiodStart
GROUP BY [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
PIVOT Periods.Term;

Please help...Thanks!
 
J

Jabret

I'm having trouble converting your answer to a crosstab query; however I did
remove the join to create a cartesian effect. It was successful in getting
periods 0-6 to show for each teacher however, classes were duplicated and no
blanks appear. Here is what I did:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
FROM [extract-2], Periods
GROUP BY [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
PIVOT [extract-2].sectionScheduletermStart;

Do I need to create another query or can I use this one?

Thanks


KARL DEWEY said:
You need a Cartesian effect. Use the solution I gave to another -

Subject: RE: Returning zero in a query? 6/10/2009 8:51 PM PST

By: KARL DEWEY In: microsoft.public.access.queries


First you need a query to give you all Departments and month combinations ---
[Volunteer Hours Tracking Table_All_Days]
SELECT [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked]
FROM [Volunteer Hours Tracking Table], [Volunteer Hours Tracking Table] AS
[Volunteer Hours Tracking Table_1]
GROUP BY [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked];

SELECT DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1) AS
[Month], [Volunteer Hours Tracking Table_All_Days].Department,
IIf((Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) Is
Null,0,(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])))
AS Total
FROM [Volunteer Hours Tracking Table_All_Days] LEFT JOIN [Volunteer Hours
Tracking Table] ON ([Volunteer Hours Tracking Table_All_Days].Department =
[Volunteer Hours Tracking Table].Department) AND ([Volunteer Hours Tracking
Table_All_Days].[Date Worked] = [Volunteer Hours Tracking Table].[Date
Worked])
GROUP BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1),
[Volunteer Hours Tracking Table_All_Days].Department
ORDER BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1)
DESC;



Jabret said:
I am trying to create a report that has teacher class schedules periods 0-6.
Where there is no record of a class, I want a blank to appear in the
teacher's shedule. There are two semesters so I did a crosstab to get the
class name to appear under each term; however, I cannot get periods 0-6 to
show up for each teacher. Here is the latest attempt:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
FROM Periods LEFT JOIN [extract-2] ON Periods.Period =
[extract-2].sectionScheduleperiodStart
GROUP BY [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
PIVOT Periods.Term;

Please help...Thanks!
Click to expand...
Click to expand...
 
K

KARL DEWEY

My solution was to create a cartesian effect query then left join it within
the crosstab query.

Jabret said:
I'm having trouble converting your answer to a crosstab query; however I did
remove the join to create a cartesian effect. It was successful in getting
periods 0-6 to show for each teacher however, classes were duplicated and no
blanks appear. Here is what I did:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
FROM [extract-2], Periods
GROUP BY [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
PIVOT [extract-2].sectionScheduletermStart;

Do I need to create another query or can I use this one?

Thanks


KARL DEWEY said:
You need a Cartesian effect. Use the solution I gave to another -

Subject: RE: Returning zero in a query? 6/10/2009 8:51 PM PST

By: KARL DEWEY In: microsoft.public.access.queries


First you need a query to give you all Departments and month combinations ---
[Volunteer Hours Tracking Table_All_Days]
SELECT [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked]
FROM [Volunteer Hours Tracking Table], [Volunteer Hours Tracking Table] AS
[Volunteer Hours Tracking Table_1]
GROUP BY [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked];

SELECT DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1) AS
[Month], [Volunteer Hours Tracking Table_All_Days].Department,
IIf((Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) Is
Null,0,(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])))
AS Total
FROM [Volunteer Hours Tracking Table_All_Days] LEFT JOIN [Volunteer Hours
Tracking Table] ON ([Volunteer Hours Tracking Table_All_Days].Department =
[Volunteer Hours Tracking Table].Department) AND ([Volunteer Hours Tracking
Table_All_Days].[Date Worked] = [Volunteer Hours Tracking Table].[Date
Worked])
GROUP BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1),
[Volunteer Hours Tracking Table_All_Days].Department
ORDER BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1)
DESC;



Jabret said:
I am trying to create a report that has teacher class schedules periods 0-6.
Where there is no record of a class, I want a blank to appear in the
teacher's shedule. There are two semesters so I did a crosstab to get the
class name to appear under each term; however, I cannot get periods 0-6 to
show up for each teacher. Here is the latest attempt:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
FROM Periods LEFT JOIN [extract-2] ON Periods.Period =
[extract-2].sectionScheduleperiodStart
GROUP BY [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
PIVOT Periods.Term;

Please help...Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

Jabret

Sorry I'm not understanding. I a cartesian effect query that pulls all
teachers with periods 0-6 for each teacher. Then I used the left join to the
crosstab query and I get duplicated classes and no blanks. I tried it with a
left join on teacher name, and I tried it with a left join on period.
Neither one is giving me the desired result. What am I doing wrong? Can you
please be more specific as to what I should have in my cartesian query and
what I should link to...for some reason I really stumped.

Thanks.

KARL DEWEY said:
My solution was to create a cartesian effect query then left join it within
the crosstab query.

Jabret said:
I'm having trouble converting your answer to a crosstab query; however I did
remove the join to create a cartesian effect. It was successful in getting
periods 0-6 to show for each teacher however, classes were duplicated and no
blanks appear. Here is what I did:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
FROM [extract-2], Periods
GROUP BY [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
PIVOT [extract-2].sectionScheduletermStart;

Do I need to create another query or can I use this one?

Thanks


KARL DEWEY said:
You need a Cartesian effect. Use the solution I gave to another -

Subject: RE: Returning zero in a query? 6/10/2009 8:51 PM PST

By: KARL DEWEY In: microsoft.public.access.queries


First you need a query to give you all Departments and month combinations ---
[Volunteer Hours Tracking Table_All_Days]
SELECT [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked]
FROM [Volunteer Hours Tracking Table], [Volunteer Hours Tracking Table] AS
[Volunteer Hours Tracking Table_1]
GROUP BY [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked];

SELECT DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1) AS
[Month], [Volunteer Hours Tracking Table_All_Days].Department,
IIf((Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) Is
Null,0,(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])))
AS Total
FROM [Volunteer Hours Tracking Table_All_Days] LEFT JOIN [Volunteer Hours
Tracking Table] ON ([Volunteer Hours Tracking Table_All_Days].Department =
[Volunteer Hours Tracking Table].Department) AND ([Volunteer Hours Tracking
Table_All_Days].[Date Worked] = [Volunteer Hours Tracking Table].[Date
Worked])
GROUP BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1),
[Volunteer Hours Tracking Table_All_Days].Department
ORDER BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1)
DESC;



:

I am trying to create a report that has teacher class schedules periods 0-6.
Where there is no record of a class, I want a blank to appear in the
teacher's shedule. There are two semesters so I did a crosstab to get the
class name to appear under each term; however, I cannot get periods 0-6 to
show up for each teacher. Here is the latest attempt:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
FROM Periods LEFT JOIN [extract-2] ON Periods.Period =
[extract-2].sectionScheduleperiodStart
GROUP BY [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
PIVOT Periods.Term;

Please help...Thanks!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I tried it with a left join on teacher name, and I tried it with a left
join on period.
You have to left join both in the crosstab query.

I can not tell from your field names which is the teacher. Post the SQL
of your cartesian effect query that pulls all teachers with periods 0-6 for
each teacher.


Jabret said:
Sorry I'm not understanding. I a cartesian effect query that pulls all
teachers with periods 0-6 for each teacher. Then I used the left join to the
crosstab query and I get duplicated classes and no blanks. I tried it with a
left join on teacher name, and I tried it with a left join on period.
Neither one is giving me the desired result. What am I doing wrong? Can you
please be more specific as to what I should have in my cartesian query and
what I should link to...for some reason I really stumped.

Thanks.

KARL DEWEY said:
My solution was to create a cartesian effect query then left join it within
the crosstab query.

Jabret said:
I'm having trouble converting your answer to a crosstab query; however I did
remove the join to create a cartesian effect. It was successful in getting
periods 0-6 to show for each teacher however, classes were duplicated and no
blanks appear. Here is what I did:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
FROM [extract-2], Periods
GROUP BY [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
PIVOT [extract-2].sectionScheduletermStart;

Do I need to create another query or can I use this one?

Thanks


:

You need a Cartesian effect. Use the solution I gave to another -

Subject: RE: Returning zero in a query? 6/10/2009 8:51 PM PST

By: KARL DEWEY In: microsoft.public.access.queries


First you need a query to give you all Departments and month combinations ---
[Volunteer Hours Tracking Table_All_Days]
SELECT [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked]
FROM [Volunteer Hours Tracking Table], [Volunteer Hours Tracking Table] AS
[Volunteer Hours Tracking Table_1]
GROUP BY [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked];

SELECT DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1) AS
[Month], [Volunteer Hours Tracking Table_All_Days].Department,
IIf((Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) Is
Null,0,(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])))
AS Total
FROM [Volunteer Hours Tracking Table_All_Days] LEFT JOIN [Volunteer Hours
Tracking Table] ON ([Volunteer Hours Tracking Table_All_Days].Department =
[Volunteer Hours Tracking Table].Department) AND ([Volunteer Hours Tracking
Table_All_Days].[Date Worked] = [Volunteer Hours Tracking Table].[Date
Worked])
GROUP BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1),
[Volunteer Hours Tracking Table_All_Days].Department
ORDER BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1)
DESC;



:

I am trying to create a report that has teacher class schedules periods 0-6.
Where there is no record of a class, I want a blank to appear in the
teacher's shedule. There are two semesters so I did a crosstab to get the
class name to appear under each term; however, I cannot get periods 0-6 to
show up for each teacher. Here is the latest attempt:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
FROM Periods LEFT JOIN [extract-2] ON Periods.Period =
[extract-2].sectionScheduleperiodStart
GROUP BY [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
PIVOT Periods.Term;

Please help...Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

Jabret

I will try that will two joins...meanwhile here is the cartesian query.
SectionInfoTeacherDisplay is the teacher name. Data is being pulled from
another database.

Thanks.

SELECT Periods.Period, [extract-2].sectionInfoteacherDisplay
FROM [extract-2], Periods;


KARL DEWEY said:
join on period.
You have to left join both in the crosstab query.

I can not tell from your field names which is the teacher. Post the SQL
of your cartesian effect query that pulls all teachers with periods 0-6 for
each teacher.


Jabret said:
Sorry I'm not understanding. I a cartesian effect query that pulls all
teachers with periods 0-6 for each teacher. Then I used the left join to the
crosstab query and I get duplicated classes and no blanks. I tried it with a
left join on teacher name, and I tried it with a left join on period.
Neither one is giving me the desired result. What am I doing wrong? Can you
please be more specific as to what I should have in my cartesian query and
what I should link to...for some reason I really stumped.

Thanks.

KARL DEWEY said:
My solution was to create a cartesian effect query then left join it within
the crosstab query.

:

I'm having trouble converting your answer to a crosstab query; however I did
remove the join to create a cartesian effect. It was successful in getting
periods 0-6 to show for each teacher however, classes were duplicated and no
blanks appear. Here is what I did:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
FROM [extract-2], Periods
GROUP BY [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
PIVOT [extract-2].sectionScheduletermStart;

Do I need to create another query or can I use this one?

Thanks


:

You need a Cartesian effect. Use the solution I gave to another -

Subject: RE: Returning zero in a query? 6/10/2009 8:51 PM PST

By: KARL DEWEY In: microsoft.public.access.queries


First you need a query to give you all Departments and month combinations ---
[Volunteer Hours Tracking Table_All_Days]
SELECT [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked]
FROM [Volunteer Hours Tracking Table], [Volunteer Hours Tracking Table] AS
[Volunteer Hours Tracking Table_1]
GROUP BY [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked];

SELECT DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1) AS
[Month], [Volunteer Hours Tracking Table_All_Days].Department,
IIf((Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) Is
Null,0,(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])))
AS Total
FROM [Volunteer Hours Tracking Table_All_Days] LEFT JOIN [Volunteer Hours
Tracking Table] ON ([Volunteer Hours Tracking Table_All_Days].Department =
[Volunteer Hours Tracking Table].Department) AND ([Volunteer Hours Tracking
Table_All_Days].[Date Worked] = [Volunteer Hours Tracking Table].[Date
Worked])
GROUP BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1),
[Volunteer Hours Tracking Table_All_Days].Department
ORDER BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1)
DESC;



:

I am trying to create a report that has teacher class schedules periods 0-6.
Where there is no record of a class, I want a blank to appear in the
teacher's shedule. There are two semesters so I did a crosstab to get the
class name to appear under each term; however, I cannot get periods 0-6 to
show up for each teacher. Here is the latest attempt:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
FROM Periods LEFT JOIN [extract-2] ON Periods.Period =
[extract-2].sectionScheduleperiodStart
GROUP BY [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
PIVOT Periods.Term;

Please help...Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

Jabret

You are brilliant! It works with the two joins...thank you so much!

Jabret said:
I will try that will two joins...meanwhile here is the cartesian query.
SectionInfoTeacherDisplay is the teacher name. Data is being pulled from
another database.

Thanks.

SELECT Periods.Period, [extract-2].sectionInfoteacherDisplay
FROM [extract-2], Periods;


KARL DEWEY said:
I tried it with a left join on teacher name, and I tried it with a left
Click to expand...
join on period.
You have to left join both in the crosstab query.

I can not tell from your field names which is the teacher. Post the SQL
of your cartesian effect query that pulls all teachers with periods 0-6 for
each teacher.


Jabret said:
Sorry I'm not understanding. I a cartesian effect query that pulls all
teachers with periods 0-6 for each teacher. Then I used the left join to the
crosstab query and I get duplicated classes and no blanks. I tried it with a
left join on teacher name, and I tried it with a left join on period.
Neither one is giving me the desired result. What am I doing wrong? Can you
please be more specific as to what I should have in my cartesian query and
what I should link to...for some reason I really stumped.

Thanks.

:

My solution was to create a cartesian effect query then left join it within
the crosstab query.

:

I'm having trouble converting your answer to a crosstab query; however I did
remove the join to create a cartesian effect. It was successful in getting
periods 0-6 to show for each teacher however, classes were duplicated and no
blanks appear. Here is what I did:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
FROM [extract-2], Periods
GROUP BY [extract-2].sectionInfoteacherDisplay,
[extract-2].courseInfodepartmentName, Periods.Period
PIVOT [extract-2].sectionScheduletermStart;

Do I need to create another query or can I use this one?

Thanks


:

You need a Cartesian effect. Use the solution I gave to another -

Subject: RE: Returning zero in a query? 6/10/2009 8:51 PM PST

By: KARL DEWEY In: microsoft.public.access.queries


First you need a query to give you all Departments and month combinations ---
[Volunteer Hours Tracking Table_All_Days]
SELECT [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked]
FROM [Volunteer Hours Tracking Table], [Volunteer Hours Tracking Table] AS
[Volunteer Hours Tracking Table_1]
GROUP BY [Volunteer Hours Tracking Table].Department, [Volunteer Hours
Tracking Table_1].[Date Worked];

SELECT DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1) AS
[Month], [Volunteer Hours Tracking Table_All_Days].Department,
IIf((Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) Is
Null,0,(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])))
AS Total
FROM [Volunteer Hours Tracking Table_All_Days] LEFT JOIN [Volunteer Hours
Tracking Table] ON ([Volunteer Hours Tracking Table_All_Days].Department =
[Volunteer Hours Tracking Table].Department) AND ([Volunteer Hours Tracking
Table_All_Days].[Date Worked] = [Volunteer Hours Tracking Table].[Date
Worked])
GROUP BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1),
[Volunteer Hours Tracking Table_All_Days].Department
ORDER BY DateSerial(Year([Volunteer Hours Tracking Table_All_Days].[Date
Worked]),Month([Volunteer Hours Tracking Table_All_Days].[Date Worked]),1)
DESC;



:

I am trying to create a report that has teacher class schedules periods 0-6.
Where there is no record of a class, I want a blank to appear in the
teacher's shedule. There are two semesters so I did a crosstab to get the
class name to appear under each term; however, I cannot get periods 0-6 to
show up for each teacher. Here is the latest attempt:

TRANSFORM First([extract-2].courseInfocourseName) AS
FirstOfcourseInfocourseName
SELECT [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
FROM Periods LEFT JOIN [extract-2] ON Periods.Period =
[extract-2].sectionScheduleperiodStart
GROUP BY [extract-2].sectionInfoteacherDisplay, Periods.Period,
[extract-2].courseInfodepartmentName, [extract-2].sectionScheduleperiodStart
PIVOT Periods.Term;

Please help...Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

joining crosstab queries 1
Another Date Query 2
Left Join Queries 0
Crosstab query totals 3
Code Help 2
Joining 2 Left Join Queries 2
2 Left Join Queries 0
Top N Records minus lower scores 8

Top