Crosstab Query

[Guest]

Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data, how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?

 

[George Nicholson]

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,

 

[Guest]

I have over 47K records....is there an easy way to add the Settlement month
and year?

 

[Guest]

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Guest]

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

I have over 47K records....is there an easy way to add the Settlement month
and year?

 

[Guest]

It is just adding an other field using your date in a year format to give
this as a results ---
Year Acct Jan Feb Mar Apr ......
2006 X 0 1 3 4 .....
2006 Y 1 5 2 6 ....
2007 X 9 0 5 0 ..
2007 Y 4 5 7 2 .

Create your crosstab query as usual, save, open in design view. Add the
formated date field and set the Crosstab row as Row Heading.

--
KARL DEWEY
Build a little - Test a little

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

I have over 47K records....is there an easy way to add the Settlement month
and year?

:

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,


Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data,
how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?

 

[George Nicholson]

is there an easy way to add the Settlement month

and year?

Very easy. I'll rephrase what I said before:

-Create a new query based on the table/query you are currently using in your
crosstab.
(again, i'm assuming that SettlementMonth and Settlementyear are
different fields).
-Add a new field to this query that will combine those 2 fields:
SettlementMonthYear: [SettlementMonth] & " " & [SettlementYear]

Now, use that query in your cross tab in place of the current table/query,
and use the new SettlementMonthYear field in place of SettlementMonth. Jan
2006 and Jan 2007 (and any other Month/year combination) should appear in
separate columns.

HTH,

 

[Guest]

Thanks for the response. The only thing is, I have one field for the
month/day/year. It is displayed as 01/01/2007. I don't understand why
Access would separate the month and then discount the year.

is there an easy way to add the Settlement month
and year?

Very easy. I'll rephrase what I said before:

-Create a new query based on the table/query you are currently using in your
crosstab.
(again, i'm assuming that SettlementMonth and Settlementyear are
different fields).
-Add a new field to this query that will combine those 2 fields:
SettlementMonthYear: [SettlementMonth] & " " & [SettlementYear]

Now, use that query in your cross tab in place of the current table/query,
and use the new SettlementMonthYear field in place of SettlementMonth. Jan
2006 and Jan 2007 (and any other Month/year combination) should appear in
separate columns.

HTH,

I have over 47K records....is there an easy way to add the Settlement month
and year?

 

[Guest]

Thanks for your response Karl. The only thing is, if I use the settlement
date within my crosstab query as a row heading, it won't allow me to use it
again for the column headings. I can't even finish the cross-tab because
without the dates it won't display the month for each column.

It is just adding an other field using your date in a year format to give
this as a results ---
Year Acct Jan Feb Mar Apr ......
2006 X 0 1 3 4 .....
2006 Y 1 5 2 6 ....
2007 X 9 0 5 0 ..
2007 Y 4 5 7 2 .

Create your crosstab query as usual, save, open in design view. Add the
formated date field and set the Crosstab row as Row Heading.

--
KARL DEWEY
Build a little - Test a little

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


:

I have over 47K records....is there an easy way to add the Settlement month
and year?

:

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,


Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data,
how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?

 

[Guest]

Apparently you did not read my post close enough.
You CAN use the same field twice.
First build it with wizard then open in design view to add the field a
second time.

In answer to George you said ---In access you can display the same datetime field all sorts of ways either
in the same output or in many different output.

--
KARL DEWEY
Build a little - Test a little

Thanks for your response Karl. The only thing is, if I use the settlement
date within my crosstab query as a row heading, it won't allow me to use it
again for the column headings. I can't even finish the cross-tab because
without the dates it won't display the month for each column.

It is just adding an other field using your date in a year format to give
this as a results ---
Year Acct Jan Feb Mar Apr ......
2006 X 0 1 3 4 .....
2006 Y 1 5 2 6 ....
2007 X 9 0 5 0 ..
2007 Y 4 5 7 2 .

Create your crosstab query as usual, save, open in design view. Add the
formated date field and set the Crosstab row as Row Heading.

--
KARL DEWEY
Build a little - Test a little

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

:

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


:

I have over 47K records....is there an easy way to add the Settlement month
and year?

:

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,


Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data,
how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?

 

[Guest]

Sorry Karl....

I understand. However the language the you gave me to put in Design view
is, to say the least, a little confusing. I have 7 fields that Design view
wants me to fill in.

Field:
Table:
Total:
Crosstab:
Sort:
Criteria:
Or:

What do I put in those fields? Or, is there somewhere else that I'm supposed
to put it.

Apparently you did not read my post close enough.
You CAN use the same field twice.
First build it with wizard then open in design view to add the field a
second time.

In answer to George you said ---In access you can display the same datetime field all sorts of ways either
in the same output or in many different output.

--
KARL DEWEY
Build a little - Test a little

Thanks for your response Karl. The only thing is, if I use the settlement
date within my crosstab query as a row heading, it won't allow me to use it
again for the column headings. I can't even finish the cross-tab because
without the dates it won't display the month for each column.

It is just adding an other field using your date in a year format to give
this as a results ---
Year Acct Jan Feb Mar Apr ......
2006 X 0 1 3 4 .....
2006 Y 1 5 2 6 ....
2007 X 9 0 5 0 ..
2007 Y 4 5 7 2 .

Create your crosstab query as usual, save, open in design view. Add the
formated date field and set the Crosstab row as Row Heading.

--
KARL DEWEY
Build a little - Test a little


:

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

:

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


:

I have over 47K records....is there an easy way to add the Settlement month
and year?

:

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,


Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data,
how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?

 

[Guest]

Post the SQL for your crosstab query and I will put it in for you.
--
KARL DEWEY
Build a little - Test a little

Sorry Karl....

I understand. However the language the you gave me to put in Design view
is, to say the least, a little confusing. I have 7 fields that Design view
wants me to fill in.

Field:
Table:
Total:
Crosstab:
Sort:
Criteria:
Or:

What do I put in those fields? Or, is there somewhere else that I'm supposed
to put it.

Apparently you did not read my post close enough.
You CAN use the same field twice.
First build it with wizard then open in design view to add the field a
second time.

In answer to George you said ---

I don't understand why Access would separate the month and then discount the year

In access you can display the same datetime field all sorts of ways either
in the same output or in many different output.

--
KARL DEWEY
Build a little - Test a little

Thanks for your response Karl. The only thing is, if I use the settlement
date within my crosstab query as a row heading, it won't allow me to use it
again for the column headings. I can't even finish the cross-tab because
without the dates it won't display the month for each column.

:

It is just adding an other field using your date in a year format to give
this as a results ---
Year Acct Jan Feb Mar Apr ......
2006 X 0 1 3 4 .....
2006 Y 1 5 2 6 ....
2007 X 9 0 5 0 ..
2007 Y 4 5 7 2 .

Create your crosstab query as usual, save, open in design view. Add the
formated date field and set the Crosstab row as Row Heading.

--
KARL DEWEY
Build a little - Test a little


:

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

:

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


:

I have over 47K records....is there an easy way to add the Settlement month
and year?

:

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,


Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data,
how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?

 

[Guest]

Here you go.

TRANSFORM Sum(tblTradeListingGroup1.[Commission Amount]) AS [SumOfCommission
Amount]
SELECT tblTradeListingGroup1.[Account Name],
Sum(tblTradeListingGroup1.[Commission Amount]) AS [Total Of Commission Amount]
FROM tblTradeListingGroup1
GROUP BY tblTradeListingGroup1.[Account Name]
PIVOT Format([SettleDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Post the SQL for your crosstab query and I will put it in for you.
--
KARL DEWEY
Build a little - Test a little

Sorry Karl....

I understand. However the language the you gave me to put in Design view
is, to say the least, a little confusing. I have 7 fields that Design view
wants me to fill in.

Field:
Table:
Total:
Crosstab:
Sort:
Criteria:
Or:

What do I put in those fields? Or, is there somewhere else that I'm supposed
to put it.

Apparently you did not read my post close enough.
You CAN use the same field twice.
First build it with wizard then open in design view to add the field a
second time.

In answer to George you said ---
I don't understand why Access would separate the month and then discount the year
In access you can display the same datetime field all sorts of ways either
in the same output or in many different output.

--
KARL DEWEY
Build a little - Test a little


:

Thanks for your response Karl. The only thing is, if I use the settlement
date within my crosstab query as a row heading, it won't allow me to use it
again for the column headings. I can't even finish the cross-tab because
without the dates it won't display the month for each column.

:

It is just adding an other field using your date in a year format to give
this as a results ---
Year Acct Jan Feb Mar Apr ......
2006 X 0 1 3 4 .....
2006 Y 1 5 2 6 ....
2007 X 9 0 5 0 ..
2007 Y 4 5 7 2 .

Create your crosstab query as usual, save, open in design view. Add the
formated date field and set the Crosstab row as Row Heading.

--
KARL DEWEY
Build a little - Test a little


:

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

:

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


:

I have over 47K records....is there an easy way to add the Settlement month
and year?

:

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,


Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data,
how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?

 

[Guest]

Try this ---
TRANSFORM Sum(tblTradeListingGroup1.[Commission Amount]) AS [SumOfCommission
Amount]
SELECT tblTradeListingGroup1.[Account Name], Format([SettleDate],"yyyy") AS
[Year], Sum(tblTradeListingGroup1.[Commission Amount]) AS [Total Of
Commission Amount]
FROM tblTradeListingGroup1
GROUP BY tblTradeListingGroup1.[Account Name], Format([SettleDate],"yyyy")
ORDER BY Format([SettleDate],"yyyy") DESC
PIVOT Format([SettleDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

Here you go.

TRANSFORM Sum(tblTradeListingGroup1.[Commission Amount]) AS [SumOfCommission
Amount]
SELECT tblTradeListingGroup1.[Account Name],
Sum(tblTradeListingGroup1.[Commission Amount]) AS [Total Of Commission Amount]
FROM tblTradeListingGroup1
GROUP BY tblTradeListingGroup1.[Account Name]
PIVOT Format([SettleDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Post the SQL for your crosstab query and I will put it in for you.
--
KARL DEWEY
Build a little - Test a little

Sorry Karl....

I understand. However the language the you gave me to put in Design view
is, to say the least, a little confusing. I have 7 fields that Design view
wants me to fill in.

Field:
Table:
Total:
Crosstab:
Sort:
Criteria:
Or:

What do I put in those fields? Or, is there somewhere else that I'm supposed
to put it.
:

Apparently you did not read my post close enough.
You CAN use the same field twice.
First build it with wizard then open in design view to add the field a
second time.

In answer to George you said ---
I don't understand why Access would separate the month and then discount the year
In access you can display the same datetime field all sorts of ways either
in the same output or in many different output.

--
KARL DEWEY
Build a little - Test a little


:

Thanks for your response Karl. The only thing is, if I use the settlement
date within my crosstab query as a row heading, it won't allow me to use it
again for the column headings. I can't even finish the cross-tab because
without the dates it won't display the month for each column.

:

It is just adding an other field using your date in a year format to give
this as a results ---
Year Acct Jan Feb Mar Apr ......
2006 X 0 1 3 4 .....
2006 Y 1 5 2 6 ....
2007 X 9 0 5 0 ..
2007 Y 4 5 7 2 .

Create your crosstab query as usual, save, open in design view. Add the
formated date field and set the Crosstab row as Row Heading.

--
KARL DEWEY
Build a little - Test a little


:

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

:

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


:

I have over 47K records....is there an easy way to add the Settlement month
and year?

:

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,


Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data,
how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?

 

[Guest]

In the words of Jim Carrey in Ace Ventura...."Like a glove!"

Thanks man...that worked great! Thanks for your patience...

Try this ---
TRANSFORM Sum(tblTradeListingGroup1.[Commission Amount]) AS [SumOfCommission
Amount]
SELECT tblTradeListingGroup1.[Account Name], Format([SettleDate],"yyyy") AS
[Year], Sum(tblTradeListingGroup1.[Commission Amount]) AS [Total Of
Commission Amount]
FROM tblTradeListingGroup1
GROUP BY tblTradeListingGroup1.[Account Name], Format([SettleDate],"yyyy")
ORDER BY Format([SettleDate],"yyyy") DESC
PIVOT Format([SettleDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little

Here you go.

TRANSFORM Sum(tblTradeListingGroup1.[Commission Amount]) AS [SumOfCommission
Amount]
SELECT tblTradeListingGroup1.[Account Name],
Sum(tblTradeListingGroup1.[Commission Amount]) AS [Total Of Commission Amount]
FROM tblTradeListingGroup1
GROUP BY tblTradeListingGroup1.[Account Name]
PIVOT Format([SettleDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Post the SQL for your crosstab query and I will put it in for you.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Karl....

I understand. However the language the you gave me to put in Design view
is, to say the least, a little confusing. I have 7 fields that Design view
wants me to fill in.

Field:
Table:
Total:
Crosstab:
Sort:
Criteria:
Or:

What do I put in those fields? Or, is there somewhere else that I'm supposed
to put it.
:

Apparently you did not read my post close enough.
You CAN use the same field twice.
First build it with wizard then open in design view to add the field a
second time.

In answer to George you said ---
I don't understand why Access would separate the month and then discount the year
In access you can display the same datetime field all sorts of ways either
in the same output or in many different output.

--
KARL DEWEY
Build a little - Test a little


:

Thanks for your response Karl. The only thing is, if I use the settlement
date within my crosstab query as a row heading, it won't allow me to use it
again for the column headings. I can't even finish the cross-tab because
without the dates it won't display the month for each column.

:

It is just adding an other field using your date in a year format to give
this as a results ---
Year Acct Jan Feb Mar Apr ......
2006 X 0 1 3 4 .....
2006 Y 1 5 2 6 ....
2007 X 9 0 5 0 ..
2007 Y 4 5 7 2 .

Create your crosstab query as usual, save, open in design view. Add the
formated date field and set the Crosstab row as Row Heading.

--
KARL DEWEY
Build a little - Test a little


:

Karl, I gotta say. You've just went over my head in the biggest way. I can
appreciate your knowledge man. Can you give me the dummy version of what you
just said?

:

Try this approach ---
TRANSFORM Count(SDD.Number) AS CountOfNumber
SELECT Format([Date of Next Review],"yyyy") AS Expr2, SDD.[Functional
Owner], Count(SDD.Number) AS [Total Of Number]
FROM SDD
GROUP BY Format([Date of Next Review],"yyyy"), SDD.[Functional Owner]
PIVOT Format([Date of Next Review],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

--
KARL DEWEY
Build a little - Test a little


:

I have over 47K records....is there an easy way to add the Settlement month
and year?

:

Assuming Month and Year are in different fields:

1) to separate year values across: Base your cross tab on an interim query
that mimics your current datasource except for a new field that concatenates
SettlementMonth & SettlementYear: SettlementMonthYear. Use that in place of
Settlement date in the cross tab and Jan 2006 and Jan2007 will display in
separate columns.

2) to separate year values down: Include SettlementYear as a row field. This
will cause 2006 and 2007 to have separate records.

HTH,


Hey guys,

If I'm doing a cross-tab query by month and have 2 years worth of data,
how
do I distinguish the 2 within the query. The field that I'm using for the
months is called Settlement date. It's set up to show 01/01/2007. Within
the crosstab query it shows me totals for not only 01/01/2007 but also
01/01/2006. How can I separate them to show 2 different totals?