Crosstab

[Beto1967 via AccessMonster.com]

Hi I need to create a Crosstab query...The table that I will use has these
COL-MONTH,COL Manager,COL-Super Name,COL-Location, Col-Yes,Col-No....I need
the crosstab query too look like this:
COL-
MONTH-July COL-MONTH-August
COL Manager COL-Super Name COL-Location Col-Yes= 12 Col-No=8 Col-
Yes=3 Col-No=7

Thank You in Advance

 

[KARL DEWEY]

The post wrapped so it is hard to figure.
Do all of your fields have a prefix of 'COL-' and do you have separate
fields for Yes & No?
What is the datatype of COL-MONTH? Text or DateTime?
Post sample data.

 

[Beto1967 via AccessMonster.com]

The post wrapped so it is hard to figure.
Do all of your fields have a prefix of 'COL-' and do you have separate
fields for Yes & No?
What is the datatype of COL-MONTH? Text or DateTime?
Post sample data.

Hi I need to create a Crosstab query...The table that I will use has these
COL-MONTH,COL Manager,COL-Super Name,COL-Location, Col-Yes,Col-No....I need

[quoted text clipped - 5 lines]

Thank You in Advance

Sorry no I just added COL to indicate they were columns... Month is text
Like July , August , ... Yes and No are two diffrent columns that will show
me how may time there was a Yes or how many times there was a No within that
month ... Thanks again

 

[KARL DEWEY]

It appears you now have a spreadsheet.
You need these fields and datatype -
ActionDate - DateTime -- derived month from datetime field
Manager - text
Super_Name - text
Location - text
Action - Yes/No - or a title you wish but not two fields

UNTESTED ---
TRANSFORM First(IIF([Action] = -1, "Yes", "No")) AS ActionType
SELECT YourTable.Manager, YourTable.Super_Name
FROM YourTable
GROUP BY YourTable.Manager, YourTable.Super_Name
PIVOT Format([ActionDate],"mmmm");

--
KARL DEWEY
Build a little - Test a little

The post wrapped so it is hard to figure.
Do all of your fields have a prefix of 'COL-' and do you have separate
fields for Yes & No?
What is the datatype of COL-MONTH? Text or DateTime?
Post sample data.

Hi I need to create a Crosstab query...The table that I will use has these
COL-MONTH,COL Manager,COL-Super Name,COL-Location, Col-Yes,Col-No....I need

[quoted text clipped - 5 lines]

Thank You in Advance

Sorry no I just added COL to indicate they were columns... Month is text
Like July , August , ... Yes and No are two diffrent columns that will show
me how may time there was a Yes or how many times there was a No within that
month ... Thanks again

 

[KARL DEWEY]

Try this ---
TRANSFORM Count(YourDataTable.[Action]) AS CountOfAction
SELECT Format([ActionDate],"yyyy") AS [Year], YourDataTable.[Manager],
YourDataTable.[Super_Name], Count(YourDataTable.[action]) AS Total
FROM YourDataTable
GROUP BY Format([ActionDate],"yyyy"), YourDataTable.[Manager],
YourDataTable.[Super_Name]
PIVOT Format([ActionDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

It appears you now have a spreadsheet.
You need these fields and datatype -
ActionDate - DateTime -- derived month from datetime field
Manager - text
Super_Name - text
Location - text
Action - Yes/No - or a title you wish but not two fields

UNTESTED ---
TRANSFORM First(IIF([Action] = -1, "Yes", "No")) AS ActionType
SELECT YourTable.Manager, YourTable.Super_Name
FROM YourTable
GROUP BY YourTable.Manager, YourTable.Super_Name
PIVOT Format([ActionDate],"mmmm");

--
KARL DEWEY
Build a little - Test a little

The post wrapped so it is hard to figure.
Do all of your fields have a prefix of 'COL-' and do you have separate
fields for Yes & No?
What is the datatype of COL-MONTH? Text or DateTime?
Post sample data.
Hi I need to create a Crosstab query...The table that I will use has these
COL-MONTH,COL Manager,COL-Super Name,COL-Location, Col-Yes,Col-No....I need
[quoted text clipped - 5 lines]

Thank You in Advance

Sorry no I just added COL to indicate they were columns... Month is text
Like July , August , ... Yes and No are two diffrent columns that will show
me how may time there was a Yes or how many times there was a No within that
month ... Thanks again

 

[Beto1967 via AccessMonster.com]

Hi Karl,
Thanks again for your assistance... I plug in the scripts you provided ...
Its not giving me the count of yes and no for the month it gives me the
total entries for example Aug has 7 which is the total sum of yes and no I
need it to say 3 no 4 yes total of 7 for August but we are heading in
the right direction thanks again

Try this ---
TRANSFORM Count(YourDataTable.[Action]) AS CountOfAction
SELECT Format([ActionDate],"yyyy") AS [Year], YourDataTable.[Manager],
YourDataTable.[Super_Name], Count(YourDataTable.[action]) AS Total
FROM YourDataTable
GROUP BY Format([ActionDate],"yyyy"), YourDataTable.[Manager],
YourDataTable.[Super_Name]
PIVOT Format([ActionDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

It appears you now have a spreadsheet.
You need these fields and datatype -

[quoted text clipped - 25 lines]

 

[KARL DEWEY]

Use two queries --
YourDataTable_Count --
SELECT Format([ActionDate],"yyyy") AS Expr1, Format([ActionDate],"mmm") AS
[Month], YourDataTable.Manager, YourDataTable.Super_Name,
Sum(IIf([action]=-1,1,0)) AS Yes, Sum(IIf([action]=0,1,0)) AS [No]
FROM YourDataTable
GROUP BY Format([ActionDate],"yyyy"), Format([ActionDate],"mmm"),
YourDataTable.Manager, YourDataTable.Super_Name;

TRANSFORM First("Yes - " & [Yes] & " No - " & [No]) AS Expr1
SELECT YourDataTable_Count.[Year] AS Expr3, YourDataTable_Count.Manager,
YourDataTable_Count.Super_Name
FROM YourDataTable_Count
GROUP BY YourDataTable_Count.[Year], YourDataTable_Count.Manager,
YourDataTable_Count.Super_Name
PIVOT YourDataTable_Count.[month] In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

Hi Karl,
Thanks again for your assistance... I plug in the scripts you provided ...
Its not giving me the count of yes and no for the month it gives me the
total entries for example Aug has 7 which is the total sum of yes and no I
need it to say 3 no 4 yes total of 7 for August but we are heading in
the right direction thanks again

Try this ---
TRANSFORM Count(YourDataTable.[Action]) AS CountOfAction
SELECT Format([ActionDate],"yyyy") AS [Year], YourDataTable.[Manager],
YourDataTable.[Super_Name], Count(YourDataTable.[action]) AS Total
FROM YourDataTable
GROUP BY Format([ActionDate],"yyyy"), YourDataTable.[Manager],
YourDataTable.[Super_Name]
PIVOT Format([ActionDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

It appears you now have a spreadsheet.
You need these fields and datatype -

[quoted text clipped - 25 lines]

me how may time there was a Yes or how many times there was a No within that
month ... Than

 

[Beto1967 via AccessMonster.com]

Hi Karl,
It works GREAT when I do my reports it show Yes-7 No 5 per month and
year ... I need to get the percentages for the Yes and No to be part of
it too for example Yes - 7 58% No 42% Is it possible ??

Use two queries --
YourDataTable_Count --
SELECT Format([ActionDate],"yyyy") AS Expr1, Format([ActionDate],"mmm") AS
[Month], YourDataTable.Manager, YourDataTable.Super_Name,
Sum(IIf([action]=-1,1,0)) AS Yes, Sum(IIf([action]=0,1,0)) AS [No]
FROM YourDataTable
GROUP BY Format([ActionDate],"yyyy"), Format([ActionDate],"mmm"),
YourDataTable.Manager, YourDataTable.Super_Name;

TRANSFORM First("Yes - " & [Yes] & " No - " & [No]) AS Expr1
SELECT YourDataTable_Count.[Year] AS Expr3, YourDataTable_Count.Manager,
YourDataTable_Count.Super_Name
FROM YourDataTable_Count
GROUP BY YourDataTable_Count.[Year], YourDataTable_Count.Manager,
YourDataTable_Count.Super_Name
PIVOT YourDataTable_Count.[month] In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Hi Karl,
Thanks again for your assistance... I plug in the scripts you provided ...

[quoted text clipped - 18 lines]

 

[KARL DEWEY]

Use this as
YourDataTable_Count --
SELECT Format([ActionDate],"yyyy") AS [Year], Format([ActionDate],"mmm") AS
[Month], YourDataTable.Manager, YourDataTable.Super_Name,
Sum(IIf([action]=-1,1,0)) AS Yes, Sum(IIf([action]=0,1,0)) AS [No],
Sum(IIf([action]=-1,1,0))/Sum(IIf([action]=-1,1,1))*100 AS Yes_Pct,
Sum(IIf([action]=0,1,0))/Sum(IIf([action]=-1,1,1))*100 AS No_Pct
FROM YourDataTable
GROUP BY Format([ActionDate],"yyyy"), Format([ActionDate],"mmm"),
YourDataTable.Manager, YourDataTable.Super_Name;

Add the Yes_Pct and No_Pct in to the crosstab.

--
KARL DEWEY
Build a little - Test a little

Hi Karl,
It works GREAT when I do my reports it show Yes-7 No 5 per month and
year ... I need to get the percentages for the Yes and No to be part of
it too for example Yes - 7 58% No 42% Is it possible ??

Use two queries --
YourDataTable_Count --
SELECT Format([ActionDate],"yyyy") AS Expr1, Format([ActionDate],"mmm") AS
[Month], YourDataTable.Manager, YourDataTable.Super_Name,
Sum(IIf([action]=-1,1,0)) AS Yes, Sum(IIf([action]=0,1,0)) AS [No]
FROM YourDataTable
GROUP BY Format([ActionDate],"yyyy"), Format([ActionDate],"mmm"),
YourDataTable.Manager, YourDataTable.Super_Name;

TRANSFORM First("Yes - " & [Yes] & " No - " & [No]) AS Expr1
SELECT YourDataTable_Count.[Year] AS Expr3, YourDataTable_Count.Manager,
YourDataTable_Count.Super_Name
FROM YourDataTable_Count
GROUP BY YourDataTable_Count.[Year], YourDataTable_Count.Manager,
YourDataTable_Count.Super_Name
PIVOT YourDataTable_Count.[month] In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Hi Karl,
Thanks again for your assistance... I plug in the scripts you provided ...

[quoted text clipped - 18 lines]

me how may time there was a Yes or how many times there was a No within that
month ... Than