Problems with my crosstab query.....

[Robin]

I am trying to run a crosstab query on a text field (LunchType) and the data
will be "H" or "M". When the query runs If a student has two records that
one has an "H" and one that has an "M" it creates two lines of data in the
crosstab instead of haveing the H & M on the same line. The Lunchtype field
is the one that set up in the query to be calculated for each row and column
intersection and I used the count for the method. My columning heading is
my date field. Here is an example of the way I would like the data to be:

Student Name Date1 Date2 Date3 Date4 Date5
Joe Black H H M H

Here is the way it prints now:

Student Name Date1 Date2 Date3 Date4 Date5
Joe Black H H H
Joe Black M

Is there a way to fix this??

Thanks

 

[[MVP] S.Clark]

There could be many solutions, but w/o knowing the table structure from
which the data originated, and the query(or queries) used to populate the
crosstab, it would be difficult to offer a solid guess.

 

[Robin]

Here is the SQL for the StudentAndLunch Query:

SELECT DISTINCTROW [Students And Lunch].StudentLunchID, [Students And
Lunch].StudentNumber, [Students And Lunch].LunchID, [Students And
Lunch].LunchType, [Students Update Query].Grade, [Students And
Lunch].AmtCollected, [Students Update Query].LastName, [Students Update
Query].FirstName, [Students Update Query].LunchBarCode, [Students Update
Query].Lunch, Lunch.Day, [Students Update Query].[LastName] & ", " &
[FirstName] AS [Student Name], [Students And Lunch].InstructorID, [Students
And Lunch].Comments
FROM Lunch LEFT JOIN ([Students And Lunch] LEFT JOIN [Students Update Query]
ON [Students And Lunch].StudentNumber = [Students Update
Query].StudentNumber) ON Lunch.LunchID = [Students And Lunch].LunchID
WHERE ((([Students And Lunch].StudentNumber)>0) AND ((Lunch.Day) Between
[Forms]![frmReportSelect]![txtStartDate] And
[Forms]![frmReportSelect]![txtEndDate]));


Here is the SQL for the Crosstab query that I run from the query above.


PARAMETERS Forms!frmReportSelect!txtStartDate DateTime,
Forms!frmReportSelect!txtEndDate DateTime;
TRANSFORM [Students And Lunch Query].LunchType
SELECT [Students And Lunch Query].StudentNumber, [Students And Lunch
Query].FirstName, [Students And Lunch Query].LastName, [Students And Lunch
Query].LunchBarCode, [Students And Lunch Query].LunchType, [Students And
Lunch Query].Grade, [LastName] & ", " & [FirstName] AS [Student Name],
[Students And Lunch Query].InstructorID, Count([Students And Lunch
Query].LunchType) AS [Total Of LunchType]
FROM [Students And Lunch Query]
WHERE ((([Students And Lunch Query].LunchType)="H" Or ([Students And Lunch
Query].LunchType)="M") AND (([Lunch].[Day]) Between
[Forms]![frmReportSelect]![txtStartDate] And
[Forms]![frmReportSelect]![txtEndDate]))
GROUP BY [Students And Lunch Query].StudentNumber, [Students And Lunch
Query].FirstName, [Students And Lunch Query].LastName, [Students And Lunch
Query].LunchBarCode, [Students And Lunch Query].LunchType, [Students And
Lunch Query].Grade, [LastName] & ", " & [FirstName], [Students And Lunch
Query].InstructorID
PIVOT "D" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("D0","D1","D2","D3","D4");

I hope this make sense.

Thanks

 

[Robin]

Did I not send you the right information to help me on the crosstab query.
I really need to need to resolve this issue. You mentioned that there were
several ways to correct this. Please help!!

 

[Robin]

I was wondering if someone could please help me with my crosstab query. If
I need to send you more information please let me know.

Thanks,