Crosstab By Month with only last 6 months data

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
 
D

Duane Hookom

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");
 
G

Guest

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

Van T. Dinh

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

Duane Hookom

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

Guest

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

Guest

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

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

UNION and Crosstab query 5
Group by Range in Crosstab Query 4
Custom Format for crosstab headings 2
Crosstab By Date (Month) 5
Crosstab returns blank fields 6
CrossTab Query Format 2
Crosstab qry not adding up 1
Showing Crosstab Totals Column in Chart 1

Top