Changing data under the column heading in a crosstab query

R

Robin

How do I print the correct information under the right column heading if I
only need information for 3days. I have a crosstab query set up with a date
control where I enter a start and end date. This report is set up to run
weekly (5days). My column headings are fixed where my report prints
M,T,W,TH,F. If I need to run the report for 3days the information is under
the wrong colunmn headings. The crosstab print the information like this
for a five day report:

D0 D1 D2 D3 D4
12 13 16 17 10

If I only want three days worth of information if gives it to me like this:

D0 D1 D2 D3 D4
16 17 10

Is there a way to conrol what column to print on a report?

Thanks,


Robin
 
M

Michel Walsh

Hi,


Try to change your PIVOT to something like:



PIVOT "D" & ( DatePart("w", MyDateTimeField) - 2 ) IN( "D"0", "D1", "D2",
"D3", "D4")




Hoping it may help,
Vanderghast, Access MVP
 
R

Robin

Hello;

I tried the statement you suggested. I think this only applies if I am only
running the report for 3 days. I should have made it more clearer . I do
not want to limit the report to 5 days. The report my be printed 1, 2, 3, or
4 dyas. But no more than 5 days. If running the report for 5 days , this
gives me the weekly report for all 5 days. Which is what I need as well. I
also need to run the report for selective days within the same week. If I
ran the report on the first day, that report should reflect Monday. It the
report was printed on the second day, that would show data for Monday and
Tuesday and so on. I hope this make more sense.

I have copied my pivot statement :

PIVOT "A" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("A0","A1","A2","A3","A4");

Thanks,

Robin
 
M

Michel Walsh

Hi,


It should work if your table hold data for just the appropriate week. You
can add a WHERE clause to be sure of that:


WHERE 0 = DateDiff("ww", [Day], [Forms]![frmReportSelect]![txtEndDate] )



Hoping it may help,
Vanderghast, Access MVP




Robin said:
Hello;

I tried the statement you suggested. I think this only applies if I am
only
running the report for 3 days. I should have made it more clearer . I do
not want to limit the report to 5 days. The report my be printed 1, 2, 3,
or
4 dyas. But no more than 5 days. If running the report for 5 days , this
gives me the weekly report for all 5 days. Which is what I need as well.
I
also need to run the report for selective days within the same week. If
I
ran the report on the first day, that report should reflect Monday. It
the
report was printed on the second day, that would show data for Monday and
Tuesday and so on. I hope this make more sense.

I have copied my pivot statement :

PIVOT "A" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("A0","A1","A2","A3","A4");

Thanks,

Robin

Michel Walsh said:
Hi,


Try to change your PIVOT to something like:



PIVOT "D" & ( DatePart("w", MyDateTimeField) - 2 ) IN( "D"0", "D1",
"D2",
"D3", "D4")




Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
R

Robin

Hello,

I changed my WHERE clause with the one you suggested and also changed the
PIVOT statement. When I run the query for 4 days, it leaves the fourth day
off. If I run the query for two days, It show data like this:


A0 A1 A2 A3 A4
.40 .40

I don't think it is working right,. What did I do wrong??

I have attached my SQL statement below I hope it makes since to you. I
have to warn you., I am pretty new to this. I learn a lot my examples.

Thanks,

Robin



Michel Walsh said:
Hi,


It should work if your table hold data for just the appropriate week. You
can add a WHERE clause to be sure of that:


WHERE 0 = DateDiff("ww", [Day], [Forms]![frmReportSelect]![txtEndDate] )



Hoping it may help,
Vanderghast, Access MVP




Robin said:
Hello;

I tried the statement you suggested. I think this only applies if I am
only
running the report for 3 days. I should have made it more clearer . I do
not want to limit the report to 5 days. The report my be printed 1, 2, 3,
or
4 dyas. But no more than 5 days. If running the report for 5 days , this
gives me the weekly report for all 5 days. Which is what I need as well.
I
also need to run the report for selective days within the same week. If
I
ran the report on the first day, that report should reflect Monday. It
the
report was printed on the second day, that would show data for Monday and
Tuesday and so on. I hope this make more sense.

I have copied my pivot statement :

PIVOT "A" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("A0","A1","A2","A3","A4");

Thanks,

Robin

Michel Walsh said:
Hi,


Try to change your PIVOT to something like:



PIVOT "D" & ( DatePart("w", MyDateTimeField) - 2 ) IN( "D"0", "D1",
"D2",
"D3", "D4")




Hoping it may help,
Vanderghast, Access MVP



How do I print the correct information under the right column heading
if I
only need information for 3days. I have a crosstab query set up with a
date
control where I enter a start and end date. This report is set up to
run
weekly (5days). My column headings are fixed where my report prints
M,T,W,TH,F. If I need to run the report for 3days the information is
under
the wrong colunmn headings. The crosstab print the information like
this
for a five day report:

D0 D1 D2 D3 D4
12 13 16 17 10

If I only want three days worth of information if gives it to me like
this:

D0 D1 D2 D3 D4
16 17 10

Is there a way to conrol what column to print on a report?

Thanks,


Robin
Click to expand...
Click to expand...
Click to expand...
 
R

Robin

Hello,

I just noticed that the SQL did not copy. Let me try it again.

PARAMETERS Forms!frmReportSelect!txtStartDate DateTime,
Forms!frmReportSelect!txtEndDate DateTime;
TRANSFORM Sum([Students And Lunch Query].AmtCollected) AS [The Value]
SELECT [Students And Lunch Query].StudentNumber, [Students And Lunch
Query].Grade, [Students And Lunch Query].Grade, [LastName] & ", " &
[FirstName] AS [Student Name], Sum([Students And Lunch Query].AmtCollected)
AS [Total Of AmtCollected]
FROM [Students And Lunch Query]
WHERE ((([Lunch].[Day]) Between [Forms]![frmReportSelect]![txtStartDate] And
[Forms]![frmReportSelect]![txtEndDate]))
GROUP BY [Students And Lunch Query].StudentNumber, [Students And Lunch
Query].Grade, [Students And Lunch Query].Grade, [LastName] & ", " &
[FirstName]
PIVOT "A" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("A0","A1","A2","A3","A4");


Robin said:
Hello,

I changed my WHERE clause with the one you suggested and also changed the
PIVOT statement. When I run the query for 4 days, it leaves the fourth day
off. If I run the query for two days, It show data like this:


A0 A1 A2 A3 A4
.40 .40

I don't think it is working right,. What did I do wrong??

I have attached my SQL statement below I hope it makes since to you. I
have to warn you., I am pretty new to this. I learn a lot my examples.

Thanks,

Robin



Michel Walsh said:
Hi,


It should work if your table hold data for just the appropriate week. You
can add a WHERE clause to be sure of that:


WHERE 0 = DateDiff("ww", [Day], [Forms]![frmReportSelect]![txtEndDate] )



Hoping it may help,
Vanderghast, Access MVP




Robin said:
Hello;

I tried the statement you suggested. I think this only applies if I am
only
running the report for 3 days. I should have made it more clearer . I do
not want to limit the report to 5 days. The report my be printed 1, 2, 3,
or
4 dyas. But no more than 5 days. If running the report for 5 days , this
gives me the weekly report for all 5 days. Which is what I need as well.
I
also need to run the report for selective days within the same week. If
I
ran the report on the first day, that report should reflect Monday. It
the
report was printed on the second day, that would show data for Monday and
Tuesday and so on. I hope this make more sense.

I have copied my pivot statement :

PIVOT "A" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("A0","A1","A2","A3","A4");

Thanks,

Robin

:

Hi,


Try to change your PIVOT to something like:



PIVOT "D" & ( DatePart("w", MyDateTimeField) - 2 ) IN( "D"0", "D1",
"D2",
"D3", "D4")




Hoping it may help,
Vanderghast, Access MVP



How do I print the correct information under the right column heading
if I
only need information for 3days. I have a crosstab query set up with a
date
control where I enter a start and end date. This report is set up to
run
weekly (5days). My column headings are fixed where my report prints
M,T,W,TH,F. If I need to run the report for 3days the information is
under
the wrong colunmn headings. The crosstab print the information like
this
for a five day report:

D0 D1 D2 D3 D4
12 13 16 17 10

If I only want three days worth of information if gives it to me like
this:

D0 D1 D2 D3 D4
16 17 10

Is there a way to conrol what column to print on a report?

Thanks,


Robin
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


Is it possible there was no record for [Day] =
Forms]![frmReportSelect]![txtEndDate] neither for [Day] =
Forms]![frmReportSelect]![txtEndDate] - 1 ?




Vanderghast, Access MVP



Robin said:
Hello,

I just noticed that the SQL did not copy. Let me try it again.

PARAMETERS Forms!frmReportSelect!txtStartDate DateTime,
Forms!frmReportSelect!txtEndDate DateTime;
TRANSFORM Sum([Students And Lunch Query].AmtCollected) AS [The Value]
SELECT [Students And Lunch Query].StudentNumber, [Students And Lunch
Query].Grade, [Students And Lunch Query].Grade, [LastName] & ", " &
[FirstName] AS [Student Name], Sum([Students And Lunch
Query].AmtCollected)
AS [Total Of AmtCollected]
FROM [Students And Lunch Query]
WHERE ((([Lunch].[Day]) Between [Forms]![frmReportSelect]![txtStartDate]
And
[Forms]![frmReportSelect]![txtEndDate]))
GROUP BY [Students And Lunch Query].StudentNumber, [Students And Lunch
Query].Grade, [Students And Lunch Query].Grade, [LastName] & ", " &
[FirstName]
PIVOT "A" & DateDiff("d",[Day],[Forms]![frmReportSelect]![txtEndDate]) In
("A0","A1","A2","A3","A4");


Robin said:
Hello,

I changed my WHERE clause with the one you suggested and also changed the
PIVOT statement. When I run the query for 4 days, it leaves the fourth
day
off. If I run the query for two days, It show data like this:


A0 A1 A2 A3 A4
.40 .40

I don't think it is working right,. What did I do wrong??

I have attached my SQL statement below I hope it makes since to you. I
have to warn you., I am pretty new to this. I learn a lot my examples.

Thanks,

Robin
Click to expand...
Click to expand...
 
Top