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
Andre Adams said:
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");
KARL DEWEY said:
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?
