A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

getting a report from a crosstab query



 
 
Thread Tools Display Modes
  #11  
Old June 20th, 2009, 06:36 PM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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

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


--
Arthur Bourgeois Jr

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

  #12  
Old June 20th, 2009, 09:37 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default getting a report from a crosstab query

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


"abourg8646 via AccessMonster.com" wrote:

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

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


--
Arthur Bourgeois Jr

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


  #13  
Old June 21st, 2009, 12:29 AM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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

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


--
Arthur Bourgeois Jr

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

  #14  
Old June 21st, 2009, 02:13 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default getting a report from a crosstab query

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


"abourg8646 via AccessMonster.com" wrote:

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

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


--
Arthur Bourgeois Jr

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


  #15  
Old June 21st, 2009, 12:29 PM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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.

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


--
Arthur Bourgeois Jr

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

  #16  
Old June 21st, 2009, 05:05 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 2,364
Default getting a report from a crosstab query

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
'================================================= ===


abourg8646 via AccessMonster.com wrote:
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.

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


  #17  
Old June 21st, 2009, 06:08 PM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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

John Spencer wrote:
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];


--
Arthur Bourgeois Jr

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

  #18  
Old June 21st, 2009, 07:33 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 2,364
Default getting a report from a crosstab query

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
'================================================= ===


abourg8646 via AccessMonster.com wrote:
yes. Was I suppose to put all of them in D0.

John Spencer wrote:
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];


  #19  
Old June 21st, 2009, 09:36 PM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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?.

John Spencer wrote:
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];


--
Arthur Bourgeois Jr

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

  #20  
Old June 21st, 2009, 11:51 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default getting a report from a crosstab query

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


"abourg8646 via AccessMonster.com" wrote:

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?.

John Spencer wrote:
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];


--
Arthur Bourgeois Jr

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:45 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.