getting a report from a crosstab query

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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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" wrote:

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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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.

KARL DEWEY wrote:
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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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


"abourg8646 via AccessMonster.com" wrote:

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.

KARL DEWEY wrote:
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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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.

Duane Hookom wrote:
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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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


"abourg8646 via AccessMonster.com" wrote:

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.

Duane Hookom wrote:
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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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

Duane Hookom wrote:
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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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


"abourg8646 via AccessMonster.com" wrote:

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

Duane Hookom wrote:
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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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

Duane Hookom wrote:
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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

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


"abourg8646 via AccessMonster.com" wrote:

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

Duane Hookom wrote:
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];

--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1