Cross Tab Query & Dates

B

Baffee

TRANSFORM Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS
SumOfSumOfWhours
SELECT [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client,
Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS [Total Of
SumOfWhours]
FROM [subqry:BillableNonBillableDetailbyEmployee]
GROUP BY [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client
PIVOT Format([PriorYear],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Above is the SQL view of my crosstab query. I have a form where the begin
and end dates are entered into a subquery. If the berinning date is entered
as 07/01/04 and the end date is enteted as 06/30/05 tge qyeru shows the monts
acress as Jan, Feb, etc. I would like to have the months appear as Jul 04,
Aug 04, Sep 04...Jan 05...Jun 05.

How do I do this?

Thanks!
Barb
 
D

Duane Hookom

Search Google Groups on
Mth1 hookom group:*access.reports*
This solution uses relative months as column headings. You can easily
display month names in your report.
 
B

Baffee

I'm sorry, but I don't understand what you are telling me.

Duane Hookom said:
Search Google Groups on
Mth1 hookom group:*access.reports*
This solution uses relative months as column headings. You can easily
display month names in your report.

--
Duane Hookom
MS Access MVP
--

Baffee said:
TRANSFORM Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS
SumOfSumOfWhours
SELECT [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client,
Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS [Total Of
SumOfWhours]
FROM [subqry:BillableNonBillableDetailbyEmployee]
GROUP BY [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client
PIVOT Format([PriorYear],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Above is the SQL view of my crosstab query. I have a form where the begin
and end dates are entered into a subquery. If the berinning date is
entered
as 07/01/04 and the end date is enteted as 06/30/05 tge qyeru shows the
monts
acress as Jan, Feb, etc. I would like to have the months appear as Jul
04,
Aug 04, Sep 04...Jan 05...Jun 05.

How do I do this?

Thanks!
Barb
Click to expand...
Click to expand...
 
D

Duane Hookom

Go to http://groups.google.com/ and enter the line of text beginning Mth1...

--
Duane Hookom
MS Access MVP


Baffee said:
I'm sorry, but I don't understand what you are telling me.

Duane Hookom said:
Search Google Groups on
Mth1 hookom group:*access.reports*
This solution uses relative months as column headings. You can easily
display month names in your report.

--
Duane Hookom
MS Access MVP
--

Baffee said:
TRANSFORM Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours)
AS
SumOfSumOfWhours
SELECT [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client,
Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS [Total
Of
SumOfWhours]
FROM [subqry:BillableNonBillableDetailbyEmployee]
GROUP BY [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client
PIVOT Format([PriorYear],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Above is the SQL view of my crosstab query. I have a form where the
begin
and end dates are entered into a subquery. If the berinning date is
entered
as 07/01/04 and the end date is enteted as 06/30/05 tge qyeru shows the
monts
acress as Jan, Feb, etc. I would like to have the months appear as Jul
04,
Aug 04, Sep 04...Jan 05...Jun 05.

How do I do this?

Thanks!
Barb
Click to expand...
Click to expand...
Click to expand...
 
B

Baffee

I went to this site and did what was suggested and it worked great. Did the
following:
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 works fine when you use a beginning date and an end date that cover any
12 month. However if I try to run the report with from 09/01/05 to 9/30/05
my
headers appear as:
Oct 04, Nov 04, Dec 04, Jan 05, Feb 05...Sep 05
The data appears in the first colum

Running it for 03/01/05 to 05/31/05 produces the follwing results:
Jun 04, Jul 04, Aug 04, Sep 04...Dec04, Jan 05...May 05.
The data appears in the first 3 columns.

I have tried everything to work out the logic, but I've had no luck. Any
suggiestions??

Thanks!







Duane Hookom said:
Go to http://groups.google.com/ and enter the line of text beginning Mth1...

--
Duane Hookom
MS Access MVP


Baffee said:
I'm sorry, but I don't understand what you are telling me.

Duane Hookom said:
Search Google Groups on
Mth1 hookom group:*access.reports*
This solution uses relative months as column headings. You can easily
display month names in your report.

--
Duane Hookom
MS Access MVP
--

TRANSFORM Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours)
AS
SumOfSumOfWhours
SELECT [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client,
Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS [Total
Of
SumOfWhours]
FROM [subqry:BillableNonBillableDetailbyEmployee]
GROUP BY [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client
PIVOT Format([PriorYear],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Above is the SQL view of my crosstab query. I have a form where the
begin
and end dates are entered into a subquery. If the berinning date is
entered
as 07/01/04 and the end date is enteted as 06/30/05 tge qyeru shows the
monts
acress as Jan, Feb, etc. I would like to have the months appear as Jul
04,
Aug 04, Sep 04...Jan 05...Jun 05.

How do I do this?

Thanks!
Barb
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Either make a 12 month report and throw out the starting date or figure a
method to hide un-necessary columns in the report.

The data should show under the proper column no matter what.

--
Duane Hookom
MS Access MVP
--

Baffee said:
I went to this site and did what was suggested and it worked great. Did the
following:
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 works fine when you use a beginning date and an end date that cover
any
12 month. However if I try to run the report with from 09/01/05 to
9/30/05
my
headers appear as:
Oct 04, Nov 04, Dec 04, Jan 05, Feb 05...Sep 05
The data appears in the first colum

Running it for 03/01/05 to 05/31/05 produces the follwing results:
Jun 04, Jul 04, Aug 04, Sep 04...Dec04, Jan 05...May 05.
The data appears in the first 3 columns.

I have tried everything to work out the logic, but I've had no luck. Any
suggiestions??

Thanks!







Duane Hookom said:
Go to http://groups.google.com/ and enter the line of text beginning
Mth1...

--
Duane Hookom
MS Access MVP


Baffee said:
I'm sorry, but I don't understand what you are telling me.

:

Search Google Groups on
Mth1 hookom group:*access.reports*
This solution uses relative months as column headings. You can easily
display month names in your report.

--
Duane Hookom
MS Access MVP
--

TRANSFORM
Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours)
AS
SumOfSumOfWhours
SELECT [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client,
Sum([subqry:BillableNonBillableDetailbyEmployee].SumOfWhours) AS
[Total
Of
SumOfWhours]
FROM [subqry:BillableNonBillableDetailbyEmployee]
GROUP BY [subqry:BillableNonBillableDetailbyEmployee].ID,
[subqry:BillableNonBillableDetailbyEmployee].Emplname,
[subqry:BillableNonBillableDetailbyEmployee].DeptName,
[subqry:BillableNonBillableDetailbyEmployee].Client
PIVOT Format([PriorYear],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Above is the SQL view of my crosstab query. I have a form where the
begin
and end dates are entered into a subquery. If the berinning date is
entered
as 07/01/04 and the end date is enteted as 06/30/05 tge qyeru shows
the
monts
acress as Jan, Feb, etc. I would like to have the months appear as
Jul
04,
Aug 04, Sep 04...Jan 05...Jun 05.

How do I do this?

Thanks!
Barb
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Relative months In reports with cross tab query 1
UNION and Crosstab query 5
Crosstab returns blank fields 6
Combine 2 Charts 2007 Excel 1
Crosstab Returns Empty Fields 2
Count Dates in a range based on Column Header Id without using sum-product 1
Find longest consecutive winning streaks 7
When building a chart Dates are been included that are not in thedate range 2

Top