Crosstab query has more problems

G

Guest

I am sorry to post this question again but I do belive S. Clark forgot to
respond back. I truly need help in solving this problem. You are the
experts and I have learned so much from this discussion group. Again Iam
sorry if I offended S. Clark because he never came back to my response. I
am going to try it again. Please let me know if you need additional
information.

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 column 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??





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




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??
 
J

John Viescas

Robin-

Have you tried:

PARAMETERS Forms!frmReportSelect!txtStartDate DateTime,
Forms!frmReportSelect!txtEndDate DateTime;
TRANSFORM FIRST([Students And Lunch Query].LunchType) As LType
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].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].Grade,
[LastName] & ", " & [FirstName], [Students And Lunch Query].InstructorID
PIVOT "D" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("D0","D1","D2","D3","D4");

???
--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Robin said:
I am sorry to post this question again but I do belive S. Clark forgot to
respond back. I truly need help in solving this problem. You are the
experts and I have learned so much from this discussion group. Again Iam
sorry if I offended S. Clark because he never came back to my response.
I
am going to try it again. Please let me know if you need additional
information.

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 column 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??





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




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??
Click to expand...
 
G

Guest

I want to thank you for helping me. Everything worked our great.

Thanks again.



John Viescas said:
Robin-

Have you tried:

PARAMETERS Forms!frmReportSelect!txtStartDate DateTime,
Forms!frmReportSelect!txtEndDate DateTime;
TRANSFORM FIRST([Students And Lunch Query].LunchType) As LType
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].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].Grade,
[LastName] & ", " & [FirstName], [Students And Lunch Query].InstructorID
PIVOT "D" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("D0","D1","D2","D3","D4");

???
--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Robin said:
I am sorry to post this question again but I do belive S. Clark forgot to
respond back. I truly need help in solving this problem. You are the
experts and I have learned so much from this discussion group. Again Iam
sorry if I offended S. Clark because he never came back to my response.
I
am going to try it again. Please let me know if you need additional
information.

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 column 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??





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




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??
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

Problems with my crosstab query..... 4
Improving append query speed 1
Headache on Outer Join with Crosstab 1
Value in Crosstab Query 1
I have a text box with multiple values 1
Crosstab? 5
Update Query problem 5
Help please on Record sets 18

Top