How do I set up a report using dates as my report header?

I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on my variables
but How do I print this sorting the dates going across the page and keeping
the data with the Name field. I will be using a Beginning date and Ending
date for the report.

Thanks for all your help

Robin

Copying my response from your similar question in another thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news
I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on my
variables
but How do I print this sorting the dates going across the page and
keeping
the data with the Name field. I will be using a Beginning date and
Ending
date for the report.

Thanks for all your help

Robin

Duane,
I am still a little lost on how to do the report. Here is my goal. The
report is to print students who are ordering Hot lunches(H) or Salads (S).
The students order their lunches everyday. Now I need to produce a report
that will print out the students name in one column and the order date in the
other columns. Under the order date field it will print what ever the
student ordered (H or S). I will total the number of H or S for each date.
I'm sorry if I made this confusing. The first report that I designed listed
the students in date order going down the page. I need it to look more like
a spreadsheet report with dates going across the page.

Thanks again for all your help

Robin
"Duane Hookom" wrote:

Copying my response from your similar question in another thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news
I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on my
variables
but How do I print this sorting the dates going across the page and
keeping
the data with the Name field. I will be using a Beginning date and
Ending
date for the report.

Thanks for all your help

Robin

Could you share your current table structure with actual table and field
names as well as some records? I also need to know how many date columns you
expect to display and if these are always based on the current date.

--
Duane Hookom
MS Access MVP
--

"Robin" wrote in message
news
Duane,
I am still a little lost on how to do the report. Here is my goal. The
report is to print students who are ordering Hot lunches(H) or Salads (S).
The students order their lunches everyday. Now I need to produce a report
that will print out the students name in one column and the order date in
the
other columns. Under the order date field it will print what ever the
student ordered (H or S). I will total the number of H or S for each
date.
I'm sorry if I made this confusing. The first report that I designed
listed
the students in date order going down the page. I need it to look more
like
a spreadsheet report with dates going across the page.

Thanks again for all your help

Robin
"Duane Hookom" wrote:

Copying my response from your similar question in another thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You could possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news
I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on my
variables
but How do I print this sorting the dates going across the page and
keeping
the data with the Name field. I will be using a Beginning date and
Ending
date for the report.

Thanks for all your help

Robin

Hello,

Table: Lunch
Fields: LunchID AutoNumbering
StudentName Text
LunchType Text
Date Number
Grade Number

Records:
LunchID Student Name Grade Date LunchType
1 Joe Black 1 10/11/04 H
2 Joe Black 1 10/12/04 H
3 Joe Black 1 10/13/04 H
4 Joe Black 1 10/14/04 H
5 Joe Black 1 10/15/04 H
6 Joe Black 1 10/18/04 S
7 Joe Black 1 10/19/04 S
8 Kyle Hampton 2 10/11/04 H
9 Kyle Hampton 2 10/13/04 H
10 Kyle Hampton 2 10/14/04 S
11 Kyle Hampton 2 10/18/04 H

I will be using 5 date columns (M T W TH F). I want to sort the report by
grade and then list the students in alpha order with the dates going across
the page along with the students name. I will put in a beginning date and
ending date. That way I will be able to print a weekly report based on the
days I entered. At the end of the report I will have a total of how many H
or S I have per date. A total for each column.

If this is not enough information please let me know.

Thanks,

Robin


"Duane Hookom" wrote:

Could you share your current table structure with actual table and field
names as well as some records? I also need to know how many date columns you
expect to display and if these are always based on the current date.

--
Duane Hookom
MS Access MVP
--

"Robin" wrote in message
news
Duane,
I am still a little lost on how to do the report. Here is my goal. The
report is to print students who are ordering Hot lunches(H) or Salads (S).
The students order their lunches everyday. Now I need to produce a report
that will print out the students name in one column and the order date in
the
other columns. Under the order date field it will print what ever the
student ordered (H or S). I will total the number of H or S for each
date.
I'm sorry if I made this confusing. The first report that I designed
listed
the students in date order going down the page. I need it to look more
like
a spreadsheet report with dates going across the page.

Thanks again for all your help

Robin
"Duane Hookom" wrote:

Copying my response from your similar question in another thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You could possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on my
variables
but How do I print this sorting the dates going across the page and
keeping
the data with the Name field. I will be using a Beginning date and
Ending
date for the report.

Thanks for all your help

Robin

Use my previous response but change month stuff to date/day stuff. If you
always want 5 days then use only the ending date since the beginning date
can be calculated. You column headings would use and expression like
ColHead: "D" & DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate])
Set the column headings property to
D0,D1,D2,..D4
Change some of the other stuff from months to days.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Hello,

Table: Lunch
Fields: LunchID AutoNumbering
StudentName Text
LunchType Text
Date Number
Grade Number

Records:
LunchID Student Name Grade Date LunchType
1 Joe Black 1 10/11/04 H
2 Joe Black 1 10/12/04 H
3 Joe Black 1 10/13/04 H
4 Joe Black 1 10/14/04 H
5 Joe Black 1 10/15/04 H
6 Joe Black 1 10/18/04 S
7 Joe Black 1 10/19/04 S
8 Kyle Hampton 2 10/11/04 H
9 Kyle Hampton 2 10/13/04 H
10 Kyle Hampton 2 10/14/04 S
11 Kyle Hampton 2 10/18/04 H

I will be using 5 date columns (M T W TH F). I want to sort the report by
grade and then list the students in alpha order with the dates going
across
the page along with the students name. I will put in a beginning date and
ending date. That way I will be able to print a weekly report based on
the
days I entered. At the end of the report I will have a total of how many
H
or S I have per date. A total for each column.

If this is not enough information please let me know.

Thanks,

Robin


"Duane Hookom" wrote:

Could you share your current table structure with actual table and field
names as well as some records? I also need to know how many date columns
you
expect to display and if these are always based on the current date.

--
Duane Hookom
MS Access MVP
--

"Robin" wrote in message
news
Duane,
I am still a little lost on how to do the report. Here is my goal.
The
report is to print students who are ordering Hot lunches(H) or Salads
(S).
The students order their lunches everyday. Now I need to produce a
report
that will print out the students name in one column and the order date
in
the
other columns. Under the order date field it will print what ever the
student ordered (H or S). I will total the number of H or S for each
date.
I'm sorry if I made this confusing. The first report that I designed
listed
the students in date order going down the page. I need it to look
more
like
a spreadsheet report with dates going across the page.

Thanks again for all your help

Robin
"Duane Hookom" wrote:

Copying my response from your similar question in another thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You could
possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from
the
same month as the ending date on your form. Mth1 is the previous
month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need
column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on my
variables
but How do I print this sorting the dates going across the page and
keeping
the data with the Name field. I will be using a Beginning date
and
Ending
date for the report.

Thanks for all your help

Robin

Duane,

I am sorry, but I am still having trouble. I went to the crosstab query and
clicked on the field Date and typed the expression in the column heading
space. This is what I typed: ColHead: "D" &
DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate]).

After I type that in, I try to open the query to check the data before I go
any further. I get an error msg. Syntax error (missing operator) in query
expression. What am I'm doing wrong. My input form name is called Lunch. I
have listed previously the field names that are in the table. Am I'm using
the right names in the above expression for the columning headings and also
did I type it in the right place in the qurey?

Thanks,

Robin

"Duane Hookom" wrote:

Use my previous response but change month stuff to date/day stuff. If you
always want 5 days then use only the ending date since the beginning date
can be calculated. You column headings would use and expression like
ColHead: "D" & DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate])
Set the column headings property to
D0,D1,D2,..D4
Change some of the other stuff from months to days.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Hello,

Table: Lunch
Fields: LunchID AutoNumbering
StudentName Text
LunchType Text
Date Number
Grade Number

Records:
LunchID Student Name Grade Date LunchType
1 Joe Black 1 10/11/04 H
2 Joe Black 1 10/12/04 H
3 Joe Black 1 10/13/04 H
4 Joe Black 1 10/14/04 H
5 Joe Black 1 10/15/04 H
6 Joe Black 1 10/18/04 S
7 Joe Black 1 10/19/04 S
8 Kyle Hampton 2 10/11/04 H
9 Kyle Hampton 2 10/13/04 H
10 Kyle Hampton 2 10/14/04 S
11 Kyle Hampton 2 10/18/04 H

I will be using 5 date columns (M T W TH F). I want to sort the report by
grade and then list the students in alpha order with the dates going
across
the page along with the students name. I will put in a beginning date and
ending date. That way I will be able to print a weekly report based on
the
days I entered. At the end of the report I will have a total of how many
H
or S I have per date. A total for each column.

If this is not enough information please let me know.

Thanks,

Robin


"Duane Hookom" wrote:

Could you share your current table structure with actual table and field
names as well as some records? I also need to know how many date columns
you
expect to display and if these are always based on the current date.

--
Duane Hookom
MS Access MVP
--

"Robin" wrote in message
news Duane,
I am still a little lost on how to do the report. Here is my goal.
The
report is to print students who are ordering Hot lunches(H) or Salads
(S).
The students order their lunches everyday. Now I need to produce a
report
that will print out the students name in one column and the order date
in
the
other columns. Under the order date field it will print what ever the
student ordered (H or S). I will total the number of H or S for each
date.
I'm sorry if I made this confusing. The first report that I designed
listed
the students in date order going down the page. I need it to look
more
like
a spreadsheet report with dates going across the page.

Thanks again for all your help

Robin
"Duane Hookom" wrote:

Copying my response from your similar question in another thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You could
possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from
the
same month as the ending date on your form. Mth1 is the previous
month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need
column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on my
variables
but How do I print this sorting the dates going across the page and
keeping
the data with the Name field. I will be using a Beginning date
and
Ending
date for the report.

Thanks for all your help

Robin

Do you have a form open named frmDates with a text box named txtEndDate? Can
you paste the SQL of your current query into a reply?

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Duane,

I am sorry, but I am still having trouble. I went to the crosstab query
and
clicked on the field Date and typed the expression in the column heading
space. This is what I typed: ColHead: "D" &
DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate]).

After I type that in, I try to open the query to check the data before I
go
any further. I get an error msg. Syntax error (missing operator) in
query
expression. What am I'm doing wrong. My input form name is called Lunch.
I
have listed previously the field names that are in the table. Am I'm
using
the right names in the above expression for the columning headings and
also
did I type it in the right place in the qurey?

Thanks,

Robin

"Duane Hookom" wrote:

Use my previous response but change month stuff to date/day stuff. If
you
always want 5 days then use only the ending date since the beginning
date
can be calculated. You column headings would use and expression like
ColHead: "D" & DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate])
Set the column headings property to
D0,D1,D2,..D4
Change some of the other stuff from months to days.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Hello,

Table: Lunch
Fields: LunchID AutoNumbering
StudentName Text
LunchType Text
Date Number
Grade Number

Records:
LunchID Student Name Grade Date LunchType
1 Joe Black 1 10/11/04 H
2 Joe Black 1 10/12/04 H
3 Joe Black 1 10/13/04 H
4 Joe Black 1 10/14/04 H
5 Joe Black 1 10/15/04 H
6 Joe Black 1 10/18/04 S
7 Joe Black 1 10/19/04 S
8 Kyle Hampton 2 10/11/04 H
9 Kyle Hampton 2 10/13/04 H
10 Kyle Hampton 2 10/14/04 S
11 Kyle Hampton 2 10/18/04 H

I will be using 5 date columns (M T W TH F). I want to sort the
report by
grade and then list the students in alpha order with the dates going
across
the page along with the students name. I will put in a beginning date
and
ending date. That way I will be able to print a weekly report based
on
the
days I entered. At the end of the report I will have a total of how
many
H
or S I have per date. A total for each column.

If this is not enough information please let me know.

Thanks,

Robin


"Duane Hookom" wrote:

Could you share your current table structure with actual table and
field
names as well as some records? I also need to know how many date
columns
you
expect to display and if these are always based on the current date.

--
Duane Hookom
MS Access MVP
--

"Robin" wrote in message
news Duane,
I am still a little lost on how to do the report. Here is my
goal.
The
report is to print students who are ordering Hot lunches(H) or
Salads
(S).
The students order their lunches everyday. Now I need to produce
a
report
that will print out the students name in one column and the order
date
in
the
other columns. Under the order date field it will print what ever
the
student ordered (H or S). I will total the number of H or S for
each
date.
I'm sorry if I made this confusing. The first report that I
designed
listed
the students in date order going down the page. I need it to look
more
like
a spreadsheet report with dates going across the page.

Thanks again for all your help

Robin
"Duane Hookom" wrote:

Copying my response from your similar question in another
thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You could
possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab
report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales
from
the
same month as the ending date on your form. Mth1 is the previous
month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need
column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on
my
variables
but How do I print this sorting the dates going across the page
and
keeping
the data with the Name field. I will be using a Beginning
date
and
Ending
date for the report.

Thanks for all your help

Robin

Hello,

This is my second time posting this message. It gave me errors during the
first posting and was worried that it did not go through.


Here is the SQL you requested I hope this helps.


TRANSFORM Count([Student Roster for Lunch form].LunchType) AS [The Value]
SELECT [Student Roster for Lunch form].Grade, [Student Roster for Lunch
form].[Student Name], [Student Roster for Lunch form].LunchType,
Count([Student Roster for Lunch form].LunchType) AS [Total Of LunchType]
FROM [Student Roster for Lunch form]
GROUP BY [Student Roster for Lunch form].Grade, [Student Roster for Lunch
form].[Student Name], [Student Roster for Lunch form].LunchType
PIVOT [Student Roster for Lunch form].Day;


The input form name is Lunch. I enter a date in the field called Day in the
input form. Did I set this up wrong?

Thanks,

Robin
"Duane Hookom" wrote:

Do you have a form open named frmDates with a text box named txtEndDate? Can
you paste the SQL of your current query into a reply?

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Duane,

I am sorry, but I am still having trouble. I went to the crosstab query
and
clicked on the field Date and typed the expression in the column heading
space. This is what I typed: ColHead: "D" &
DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate]).

After I type that in, I try to open the query to check the data before I
go
any further. I get an error msg. Syntax error (missing operator) in
query
expression. What am I'm doing wrong. My input form name is called Lunch.
I
have listed previously the field names that are in the table. Am I'm
using
the right names in the above expression for the columning headings and
also
did I type it in the right place in the qurey?

Thanks,

Robin

"Duane Hookom" wrote:

Use my previous response but change month stuff to date/day stuff. If
you
always want 5 days then use only the ending date since the beginning
date
can be calculated. You column headings would use and expression like
ColHead: "D" & DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate])
Set the column headings property to
D0,D1,D2,..D4
Change some of the other stuff from months to days.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Hello,

Table: Lunch
Fields: LunchID AutoNumbering
StudentName Text
LunchType Text
Date Number
Grade Number

Records:
LunchID Student Name Grade Date LunchType
1 Joe Black 1 10/11/04 H
2 Joe Black 1 10/12/04 H
3 Joe Black 1 10/13/04 H
4 Joe Black 1 10/14/04 H
5 Joe Black 1 10/15/04 H
6 Joe Black 1 10/18/04 S
7 Joe Black 1 10/19/04 S
8 Kyle Hampton 2 10/11/04 H
9 Kyle Hampton 2 10/13/04 H
10 Kyle Hampton 2 10/14/04 S
11 Kyle Hampton 2 10/18/04 H

I will be using 5 date columns (M T W TH F). I want to sort the
report by
grade and then list the students in alpha order with the dates going
across
the page along with the students name. I will put in a beginning date
and
ending date. That way I will be able to print a weekly report based
on
the
days I entered. At the end of the report I will have a total of how
many
H
or S I have per date. A total for each column.

If this is not enough information please let me know.

Thanks,

Robin


"Duane Hookom" wrote:

Could you share your current table structure with actual table and
field
names as well as some records? I also need to know how many date
columns
you
expect to display and if these are always based on the current date.

--
Duane Hookom
MS Access MVP
--

"Robin" wrote in message
news Duane,
I am still a little lost on how to do the report. Here is my
goal.
The
report is to print students who are ordering Hot lunches(H) or
Salads
(S).
The students order their lunches everyday. Now I need to produce
a
report
that will print out the students name in one column and the order
date
in
the
other columns. Under the order date field it will print what ever
the
student ordered (H or S). I will total the number of H or S for
each
date.
I'm sorry if I made this confusing. The first report that I
designed
listed
the students in date order going down the page. I need it to look
more
like
a spreadsheet report with dates going across the page.

Thanks again for all your help

Robin
"Duane Hookom" wrote:

Copying my response from your similar question in another
thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You could
possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab
report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales
from
the
same month as the ending date on your form. Mth1 is the previous
month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need
column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a count on
my
variables
but How do I print this sorting the dates going across the page
and
keeping
the data with the Name field. I will be using a Beginning
date
and
Ending
date for the report.

Thanks for all your help

Robin

This doesn't look at all like the original table structure you provided. Do
you actually use the word "Form" in a table or query name. This is so
confusing I can't begin to understand this (at my age).

What is your form name and ending date text box that controls/filters your
crosstab?

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Hello,

This is my second time posting this message. It gave me errors during the
first posting and was worried that it did not go through.


Here is the SQL you requested I hope this helps.


TRANSFORM Count([Student Roster for Lunch form].LunchType) AS [The Value]
SELECT [Student Roster for Lunch form].Grade, [Student Roster for Lunch
form].[Student Name], [Student Roster for Lunch form].LunchType,
Count([Student Roster for Lunch form].LunchType) AS [Total Of LunchType]
FROM [Student Roster for Lunch form]
GROUP BY [Student Roster for Lunch form].Grade, [Student Roster for Lunch
form].[Student Name], [Student Roster for Lunch form].LunchType
PIVOT [Student Roster for Lunch form].Day;


The input form name is Lunch. I enter a date in the field called Day in
the
input form. Did I set this up wrong?

Thanks,

Robin
"Duane Hookom" wrote:

Do you have a form open named frmDates with a text box named txtEndDate?
Can
you paste the SQL of your current query into a reply?

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Duane,

I am sorry, but I am still having trouble. I went to the crosstab
query
and
clicked on the field Date and typed the expression in the column
heading
space. This is what I typed: ColHead: "D" &
DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate]).

After I type that in, I try to open the query to check the data
before I
go
any further. I get an error msg. Syntax error (missing operator)
in
query
expression. What am I'm doing wrong. My input form name is called
Lunch.
I
have listed previously the field names that are in the table. Am I'm
using
the right names in the above expression for the columning headings and
also
did I type it in the right place in the qurey?

Thanks,

Robin

"Duane Hookom" wrote:

Use my previous response but change month stuff to date/day stuff.
If
you
always want 5 days then use only the ending date since the beginning
date
can be calculated. You column headings would use and expression like
ColHead: "D" & DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate])
Set the column headings property to
D0,D1,D2,..D4
Change some of the other stuff from months to days.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
...
Hello,

Table: Lunch
Fields: LunchID AutoNumbering
StudentName Text
LunchType Text
Date Number
Grade Number

Records:
LunchID Student Name Grade Date LunchType
1 Joe Black 1 10/11/04 H
2 Joe Black 1 10/12/04 H
3 Joe Black 1 10/13/04 H
4 Joe Black 1 10/14/04 H
5 Joe Black 1 10/15/04 H
6 Joe Black 1 10/18/04 S
7 Joe Black 1 10/19/04 S
8 Kyle Hampton 2 10/11/04 H
9 Kyle Hampton 2 10/13/04 H
10 Kyle Hampton 2 10/14/04 S
11 Kyle Hampton 2 10/18/04 H

I will be using 5 date columns (M T W TH F). I want to sort the
report by
grade and then list the students in alpha order with the dates
going
across
the page along with the students name. I will put in a beginning
date
and
ending date. That way I will be able to print a weekly report
based
on
the
days I entered. At the end of the report I will have a total of
how
many
H
or S I have per date. A total for each column.

If this is not enough information please let me know.

Thanks,

Robin


"Duane Hookom" wrote:

Could you share your current table structure with actual table
and
field
names as well as some records? I also need to know how many date
columns
you
expect to display and if these are always based on the current
date.

--
Duane Hookom
MS Access MVP
--

"Robin" wrote in message
news Duane,
I am still a little lost on how to do the report. Here is my
goal.
The
report is to print students who are ordering Hot lunches(H) or
Salads
(S).
The students order their lunches everyday. Now I need to
produce
a
report
that will print out the students name in one column and the
order
date
in
the
other columns. Under the order date field it will print what
ever
the
student ordered (H or S). I will total the number of H or S
for
each
date.
I'm sorry if I made this confusing. The first report that I
designed
listed
the students in date order going down the page. I need it to
look
more
like
a spreadsheet report with dates going across the page.

Thanks again for all your help

Robin
"Duane Hookom" wrote:

Copying my response from your similar question in another
thread...
Substitute dates for months in this solution.

Try not to use "absolute" column headings for dates. You
could
possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab
report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" &
DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is
sales
from
the
same month as the ending date on your form. Mth1 is the
previous
month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you
need
column
labels in your report, use text boxes with control sources
of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP


"Robin" wrote in message
news I need to print a report showing the following:

Name 8/26 8/27 8/28 8/29 8/30
Jane H S H H
Bob H H H S
Don S H H H

Total H 2 2 3 2
Total S 1 1 1


I can print the report sorting by date and giving me a
count on
my
variables
but How do I print this sorting the dates going across the
page
and
keeping
the data with the Name field. I will be using a Beginning
date
and
Ending
date for the report.

Thanks for all your help

Robin