If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
getting a report from a crosstab query
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 |
#3
|
|||
|
|||
getting a report from a crosstab query
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 |
#4
|
|||
|
|||
getting a report from a crosstab query
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 |
#5
|
|||
|
|||
getting a report from a crosstab query
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 |
#6
|
|||
|
|||
getting a report from a crosstab query
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 |
#7
|
|||
|
|||
getting a report from a crosstab query
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 |
#8
|
|||
|
|||
getting a report from a crosstab query
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 |
#9
|
|||
|
|||
getting a report from a crosstab query
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 |
#10
|
|||
|
|||
getting a report from a crosstab query
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 |
Thread Tools | |
Display Modes | |
|
|