getting a report from a crosstab query

[abourg8646 via AccessMonster.com]

I have a crosstab query and it pulls in all the data that I need. When I make
a report then it asks for the start and end date twice and then gives this
error. Microsoft Access Database engin does not recognize " as a valid field
name or expression. My SQL View looks like this:

PARAMETERS [Start date] DateTime, [End Date] DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
WHERE [Date] Between [Start Date] And [End Date]
GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[KARL DEWEY]

Do you have [Start date] and [End Date] in [Employee Query] also? If so
check the typing/spelling as it must be identical to what is in the crosstab.

 

[abourg8646 via AccessMonster.com]

No my employee query just pulls in all the data and the cross tab query
allows me to pull in just the dates that I want for that week. My problem is
when I try to make a report from the crosstab query then it doesn't show me
any fields to select from.

Do you have [Start date] and [End Date] in [Employee Query] also? If so
check the typing/spelling as it must be identical to what is in the crosstab.

I have a crosstab query and it pulls in all the data that I need. When I make
a report then it asks for the start and end date twice and then gives this

[quoted text clipped - 8 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

I think you need to provide some details on how you are creating your report.
I expect your issue is that you are opening the crosstab in a recordset or
something.

I think there is a better solution for creating crosstabs where the PIVOT is
on a date interval. I use relative dates.

--
Duane Hookom
Microsoft Access MVP

No my employee query just pulls in all the data and the cross tab query
allows me to pull in just the dates that I want for that week. My problem is
when I try to make a report from the crosstab query then it doesn't show me
any fields to select from.

Do you have [Start date] and [End Date] in [Employee Query] also? If so
check the typing/spelling as it must be identical to what is in the crosstab.

I have a crosstab query and it pulls in all the data that I need. When I make
a report then it asks for the start and end date twice and then gives this

[quoted text clipped - 8 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

I have several reports that need to be made. The 1st is a weekly schedule.
The crosstab query has all the data that I need for the past several months
in it. I need the report to pull data for certain dates. That is the
parameter statement in the SQL veiw. I need for the dates to be displayed
from left to right. Without the parameter statement in there then I can get
the report wizard to to pull in the report but it displays all the data
instead of just the week I want. With the parameter statement in there then
when I go to report wizard and select the crosstab query then there are no
fields to select.

I think you need to provide some details on how you are creating your report.
I expect your issue is that you are opening the crosstab in a recordset or
something.

I think there is a better solution for creating crosstabs where the PIVOT is
on a date interval. I use relative dates.

No my employee query just pulls in all the data and the cross tab query
allows me to pull in just the dates that I want for that week. My problem is

[quoted text clipped - 9 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

If you are pivot by any date interval, you might want to consider the
solution at http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP

I have several reports that need to be made. The 1st is a weekly schedule.
The crosstab query has all the data that I need for the past several months
in it. I need the report to pull data for certain dates. That is the
parameter statement in the SQL veiw. I need for the dates to be displayed
from left to right. Without the parameter statement in there then I can get
the report wizard to to pull in the report but it displays all the data
instead of just the week I want. With the parameter statement in there then
when I go to report wizard and select the crosstab query then there are no
fields to select.

I think you need to provide some details on how you are creating your report.
I expect your issue is that you are opening the crosstab in a recordset or
something.

I think there is a better solution for creating crosstabs where the PIVOT is
on a date interval. I use relative dates.

No my employee query just pulls in all the data and the cross tab query
allows me to pull in just the dates that I want for that week. My problem is

[quoted text clipped - 9 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

I have been to that link twice. I need to be able to pull in 7 days in a row
and they will change each week. the individuals that will be using this
database don't know anything about Access

If you are pivot by any date interval, you might want to consider the
solution at http://www.tek-tips.com/faqs.cfm?fid=5466.

I have several reports that need to be made. The 1st is a weekly schedule.
The crosstab query has all the data that I need for the past several months

[quoted text clipped - 18 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

The FAQ can be modified to pull 7 days based on any ending date. User won't
have to do much other than specify the end date and click a button.
--
Duane Hookom
Microsoft Access MVP

I have been to that link twice. I need to be able to pull in 7 days in a row
and they will change each week. the individuals that will be using this
database don't know anything about Access

If you are pivot by any date interval, you might want to consider the
solution at http://www.tek-tips.com/faqs.cfm?fid=5466.

I have several reports that need to be made. The 1st is a weekly schedule.
The crosstab query has all the data that I need for the past several months

[quoted text clipped - 18 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

cam you give me an example other then the on on Tek-tips. That one looks like
greek to me

The FAQ can be modified to pull 7 days based on any ending date. User won't
have to do much other than specify the end date and click a button.

I have been to that link twice. I need to be able to pull in 7 days in a row
and they will change each week. the individuals that will be using this

[quoted text clipped - 8 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

Let's assume you want 7 days of the schedule ending on Forms!frmDate!txtEndDate

PARAMETERS Forms!frmDate!txtEndDate DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT "D" & DateDiff("d",[Date],Forms!frmDate!txtEndDate)
IN ("D6","D5","D4","D3","D2","D1","D0") ;

--
Duane Hookom
Microsoft Access MVP

cam you give me an example other then the on on Tek-tips. That one looks like
greek to me

The FAQ can be modified to pull 7 days based on any ending date. User won't
have to do much other than specify the end date and click a button.

I have been to that link twice. I need to be able to pull in 7 days in a row
and they will change each week. the individuals that will be using this

[quoted text clipped - 8 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

that worked great but is there any way to display the date instead of DO,D1,
D2

Let's assume you want 7 days of the schedule ending on Forms!frmDate!txtEndDate

PARAMETERS Forms!frmDate!txtEndDate DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT "D" & DateDiff("d",[Date],Forms!frmDate!txtEndDate)
IN ("D6","D5","D4","D3","D2","D1","D0") ;

cam you give me an example other then the on on Tek-tips. That one looks like
greek to me

[quoted text clipped - 6 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

The previous link explained how to display the actual dates as column
headings in your report. You use text boxes with control sources like:
=DateAdd("d",-6,Forms!frmDate!txtEndDate)
=DateAdd("d",-5,Forms!frmDate!txtEndDate)
=DateAdd("d",-4,Forms!frmDate!txtEndDate)
=DateAdd("d",-3,Forms!frmDate!txtEndDate)
=DateAdd("d",-2,Forms!frmDate!txtEndDate)
=DateAdd("d",-1,Forms!frmDate!txtEndDate)
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

--
Duane Hookom
Microsoft Access MVP

that worked great but is there any way to display the date instead of DO,D1,
D2

Let's assume you want 7 days of the schedule ending on Forms!frmDate!txtEndDate

PARAMETERS Forms!frmDate!txtEndDate DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT "D" & DateDiff("d",[Date],Forms!frmDate!txtEndDate)
IN ("D6","D5","D4","D3","D2","D1","D0") ;

cam you give me an example other then the on on Tek-tips. That one looks like
greek to me

[quoted text clipped - 6 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

Its still not showing the date in the column and when I add the line below
then it removes the value.

The previous link explained how to display the actual dates as column
headings in your report. You use text boxes with control sources like:
=DateAdd("d",-6,Forms!frmDate!txtEndDate)
=DateAdd("d",-5,Forms!frmDate!txtEndDate)
=DateAdd("d",-4,Forms!frmDate!txtEndDate)
=DateAdd("d",-3,Forms!frmDate!txtEndDate)
=DateAdd("d",-2,Forms!frmDate!txtEndDate)
=DateAdd("d",-1,Forms!frmDate!txtEndDate)
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

that worked great but is there any way to display the date instead of DO,D1,
D2

[quoted text clipped - 14 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

What's not showing the date in the column where? Are you still messing around
in the query or are you now working in your report?

I don't understand what line you added where and what value it removes.

How about telling us what you have done?

--
Duane Hookom
Microsoft Access MVP

Its still not showing the date in the column and when I add the line below
then it removes the value.

The previous link explained how to display the actual dates as column
headings in your report. You use text boxes with control sources like:
=DateAdd("d",-6,Forms!frmDate!txtEndDate)
=DateAdd("d",-5,Forms!frmDate!txtEndDate)
=DateAdd("d",-4,Forms!frmDate!txtEndDate)
=DateAdd("d",-3,Forms!frmDate!txtEndDate)
=DateAdd("d",-2,Forms!frmDate!txtEndDate)
=DateAdd("d",-1,Forms!frmDate!txtEndDate)
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

that worked great but is there any way to display the date instead of DO,D1,
D2

[quoted text clipped - 14 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

I copied the SQL and place that in my crosstab SQL. This displayed D6,D5nD4,
D3,D2,D1,D0 as column headers. I then went to reports and 1st I treid to copy
the DateDiff line into the control source on the form marked D6. That didn't
work. 2nd I tried to add text boxes to the report an use the datediff
statement in rhe control source. That still didn't work.

What's not showing the date in the column where? Are you still messing around
in the query or are you now working in your report?

I don't understand what line you added where and what value it removes.

How about telling us what you have done?

Its still not showing the date in the column and when I add the line below
then it removes the value.

[quoted text clipped - 14 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[John Spencer]

Are you putting just ONE of the expressions as the source for each
control? I know that may be a basic question, but I've seen people do
just that and then be confused about why something did not work.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I copied the SQL and place that in my crosstab SQL. This displayed D6,D5nD4,
D3,D2,D1,D0 as column headers. I then went to reports and 1st I treid to copy
the DateDiff line into the control source on the form marked D6. That didn't
work. 2nd I tried to add text boxes to the report an use the datediff
statement in rhe control source. That still didn't work.

What's not showing the date in the column where? Are you still messing around
in the query or are you now working in your report?

I don't understand what line you added where and what value it removes.

How about telling us what you have done?

Its still not showing the date in the column and when I add the line below
then it removes the value.

[quoted text clipped - 14 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

yes. Was I suppose to put all of them in D0.

Are you putting just ONE of the expressions as the source for each
control? I know that may be a basic question, but I've seen people do
just that and then be confused about why something did not work.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I copied the SQL and place that in my crosstab SQL. This displayed D6,D5nD4,
D3,D2,D1,D0 as column headers. I then went to reports and 1st I treid to copy

[quoted text clipped - 14 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[John Spencer]

NO, just one in each of the controls.

And you do need the equal sign.

You need to add controls in the report that replace the labels for your
date columns and set the control source for each new control to a
variation of the following:
=DateAdd("d",-6,Forms!frmDate!txtEndDate)

If that does not work then you need to tell us what you mean by does not
work. Are you getting an error value, the wrong information, no
information? If an error what exactly does the error say? If the wrong
value, what value are you getting and why is it wrong.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

yes. Was I suppose to put all of them in D0.

Are you putting just ONE of the expressions as the source for each
control? I know that may be a basic question, but I've seen people do
just that and then be confused about why something did not work.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I copied the SQL and place that in my crosstab SQL. This displayed D6,D5nD4,
D3,D2,D1,D0 as column headers. I then went to reports and 1st I treid to copy

[quoted text clipped - 14 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

It is still leaving D6,D5,D4,D3,D2,D1,D0 as column manes. When I put =DateAdd
statement then I get columns of #Name?.

NO, just one in each of the controls.

And you do need the equal sign.

You need to add controls in the report that replace the labels for your
date columns and set the control source for each new control to a
variation of the following:
=DateAdd("d",-6,Forms!frmDate!txtEndDate)

If that does not work then you need to tell us what you mean by does not
work. Are you getting an error value, the wrong information, no
information? If an error what exactly does the error say? If the wrong
value, what value are you getting and why is it wrong.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

yes. Was I suppose to put all of them in D0.

[quoted text clipped - 14 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

This shouldn't be that difficult. The detail section of your report should
have text boxes bound to the fields/column generated by your crosstab which
includ [d0], [d2],...

In the page header (or other section), you can add text boxes to use a
column titles with control sources like:
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

Did you use these form and control names? Is the form remaining open when
the report is opened?

Do you see the correct values in the detail section?

What do you see the the text boxes with the DateAdd() functions?

--
Duane Hookom
Microsoft Access MVP

It is still leaving D6,D5,D4,D3,D2,D1,D0 as column manes. When I put =DateAdd
statement then I get columns of #Name?.

NO, just one in each of the controls.

And you do need the equal sign.

You need to add controls in the report that replace the labels for your
date columns and set the control source for each new control to a
variation of the following:
=DateAdd("d",-6,Forms!frmDate!txtEndDate)

If that does not work then you need to tell us what you mean by does not
work. Are you getting an error value, the wrong information, no
information? If an error what exactly does the error say? If the wrong
value, what value are you getting and why is it wrong.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

yes. Was I suppose to put all of them in D0.

[quoted text clipped - 14 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];