Crosstab By Month with only last 6 months data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a crosstab query which looks like such.

Column Jan Feb Mar April and thru dec.....
Row 1 15 0 34 0
Row 2 9 10 0 23
Row 3 1 4 3 56
and such...

I wan the crosstab to limit the data to the last 6 months from current from.
How do I do this..? sequel is as follows.

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department) AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT Format([Date:],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Any help would be great...Thank you.
Lou
 
Try:

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department) AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT "Mth" & DateDiff("m",[date]. Date()) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5");
 
Hi Duane,

It gave me this error,

Invalid use of ‘.’, ‘!’, or ‘()’. In query expression “Mth†&
Datediff(“mâ€,[date]. Date())’.

Any idea..?

Thank you.
Lou

Duane Hookom said:
Try:

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department) AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT "Mth" & DateDiff("m",[date]. Date()) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5");


--
Duane Hookom
MS Access MVP



LOU said:
Hello,
I have a crosstab query which looks like such.

Column Jan Feb Mar April and thru dec.....
Row 1 15 0 34 0
Row 2 9 10 0 23
Row 3 1 4 3 56
and such...

I wan the crosstab to limit the data to the last 6 months from current
from.
How do I do this..? sequel is as follows.

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department) AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT Format([Date:],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Any help would be great...Thank you.
Lou
Click to expand...
Click to expand...
 
I think Duane meant:

Datediff("m",[date], Date())

Note the comma after [date]

--
HTH
Van T. Dinh
MVP (Access)



LOU said:
Hi Duane,

It gave me this error,

Invalid use of '.', '!', or '()'. In query expression "Mth" &
Datediff("m",[date]. Date())'.

Any idea..?

Thank you.
Lou

Duane Hookom said:
Try:

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department) AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT "Mth" & DateDiff("m",[date]. Date()) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5");


--
Duane Hookom
MS Access MVP



LOU said:
Hello,
I have a crosstab query which looks like such.

Column Jan Feb Mar April and thru dec.....
Row 1 15 0 34 0
Row 2 9 10 0 23
Row 3 1 4 3 56
and such...

I wan the crosstab to limit the data to the last 6 months from current
from.
How do I do this..? sequel is as follows.

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department)
AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT Format([Date:],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Any help would be great...Thank you.
Lou
Click to expand...
Click to expand...
Click to expand...
 
Thanks Van.

--
Duane Hookom
MS Access MVP

Van T. Dinh said:
I think Duane meant:

Datediff("m",[date], Date())

Note the comma after [date]

--
HTH
Van T. Dinh
MVP (Access)



LOU said:
Hi Duane,

It gave me this error,

Invalid use of '.', '!', or '()'. In query expression "Mth" &
Datediff("m",[date]. Date())'.

Any idea..?

Thank you.
Lou

Duane Hookom said:
Try:

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department) AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT "Mth" & DateDiff("m",[date]. Date()) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5");


--
Duane Hookom
MS Access MVP



Hello,
I have a crosstab query which looks like such.

Column Jan Feb Mar April and thru dec.....
Row 1 15 0 34 0
Row 2 9 10 0 23
Row 3 1 4 3 56
and such...

I wan the crosstab to limit the data to the last 6 months from current
from.
How do I do this..? sequel is as follows.

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department)
AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT Format([Date:],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Any help would be great...Thank you.
Lou
Click to expand...
Click to expand...
Click to expand...
 
Thank you,

That let the query run but it asks for a date?
If I click ok without putting a date in it runs but brings up only values in
the grand total column and nothing in the individual month columns?

Thank you.
Duane Hookom said:
Thanks Van.

--
Duane Hookom
MS Access MVP

Van T. Dinh said:
I think Duane meant:

Datediff("m",[date], Date())

Note the comma after [date]

--
HTH
Van T. Dinh
MVP (Access)



LOU said:
Hi Duane,

It gave me this error,

Invalid use of '.', '!', or '()'. In query expression "Mth" &
Datediff("m",[date]. Date())'.

Any idea..?

Thank you.
Lou

:

Try:

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department) AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT "Mth" & DateDiff("m",[date]. Date()) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5");


--
Duane Hookom
MS Access MVP



Hello,
I have a crosstab query which looks like such.

Column Jan Feb Mar April and thru dec.....
Row 1 15 0 34 0
Row 2 9 10 0 23
Row 3 1 4 3 56
and such...

I wan the crosstab to limit the data to the last 6 months from current
from.
How do I do this..? sequel is as follows.

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department)
AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT Format([Date:],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Any help would be great...Thank you.
Lou
Click to expand...
Click to expand...
Click to expand...
 
Thank you guys. I figured out my mistake and it works great!

Lou

Duane Hookom said:
Thanks Van.

--
Duane Hookom
MS Access MVP

Van T. Dinh said:
I think Duane meant:

Datediff("m",[date], Date())

Note the comma after [date]

--
HTH
Van T. Dinh
MVP (Access)



LOU said:
Hi Duane,

It gave me this error,

Invalid use of '.', '!', or '()'. In query expression "Mth" &
Datediff("m",[date]. Date())'.

Any idea..?

Thank you.
Lou

:

Try:

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department) AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT "Mth" & DateDiff("m",[date]. Date()) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5");


--
Duane Hookom
MS Access MVP



Hello,
I have a crosstab query which looks like such.

Column Jan Feb Mar April and thru dec.....
Row 1 15 0 34 0
Row 2 9 10 0 23
Row 3 1 4 3 56
and such...

I wan the crosstab to limit the data to the last 6 months from current
from.
How do I do this..? sequel is as follows.

TRANSFORM Count([Exceptions Table].Department) AS CountOfDepartment
SELECT [Exceptions Table].badge, Count([Exceptions Table].Department)
AS
[Total Of To]
FROM [Exceptions Table]
GROUP BY [Exceptions Table].badge
PIVOT Format([Date:],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Any help would be great...Thank you.
Lou
Click to expand...
Click to expand...
Click to expand...
 
Back
Top