Tricky query question

[Jack]

slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Duane Hookom]

It looks like the Total is the sum of all crosstab column values. If this is
the case, you can create another Row Heading using Sum of the field used for
the Value of the crosstab.

If you can't figure this out, come back with the SQL view of your query.

 

[John Spencer]

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jack]

Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Duane Hookom]

Crosstabs always display the derived columns on the right. You can't get a
Row Heading to display on the right without displaying in another query,
report, or form.

You can calculate a Row Heading YTD. It would have been great if you had
provided your SQL view. Looking at John's suggestion, you could create a new
Row Heading with:
Sum(Abs(Year(SomedateField) = Year(Date())) * Amount) As AmtYTD
Again, this can't display in the crosstab on the right.


--
Duane Hookom
Microsoft Access MVP

Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[John Spencer]

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Jack]

Thanks Duane and John for the new ideas. I am going to work on it.
J

Crosstabs always display the derived columns on the right. You can't get a
Row Heading to display on the right without displaying in another query,
report, or form.

You can calculate a Row Heading YTD. It would have been great if you had
provided your SQL view. Looking at John's suggestion, you could create a new
Row Heading with:
Sum(Abs(Year(SomedateField) = Year(Date())) * Amount) As AmtYTD
Again, this can't display in the crosstab on the right.


--
Duane Hookom
Microsoft Access MVP

Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:

slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Crosstab report for cross-month data]

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Duane Hookom]

Could you please provide more information such as table structure(s), sample
data, and desired display?


--
Duane Hookom
Microsoft Access MVP

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

:

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Crosstab report for cross-month data]

Dear Duane

Below is my data structure which is in crosstab and it can calculate the sum
of balance amount. Assume there is only 'Jun' and 'July' value in column
[month], how can I add one more column to calculate balance difference (i.e.
July data minus June data) in a crosstab query?

Thank you.

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance]) AS [SumOfBalance]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name], Sum([total-update-mapping-Jun n Jul].[Balance]) AS
Grandtotal
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].month;

Could you please provide more information such as table structure(s), sample
data, and desired display?


--
Duane Hookom
Microsoft Access MVP

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

:

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Duane Hookom]

You could calculate the difference in a report by subtracting one month from
the other.

Since you are pivoting by month, I would suggest you take a look at the
dynamic monthly crosstab report solution found at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP

Dear Duane

Below is my data structure which is in crosstab and it can calculate the sum
of balance amount. Assume there is only 'Jun' and 'July' value in column
[month], how can I add one more column to calculate balance difference (i.e.
July data minus June data) in a crosstab query?

Thank you.

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance]) AS [SumOfBalance]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name], Sum([total-update-mapping-Jun n Jul].[Balance]) AS
Grandtotal
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].month;

Could you please provide more information such as table structure(s), sample
data, and desired display?


--
Duane Hookom
Microsoft Access MVP

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.


:

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

:

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[George]

Dear Duane

Thanks for your reply. I would like to calculate the difference (by
substracting one month from the other) in a crosstab query, instead of doing
it in report / form. It is because we would like to have result in datasheet
format, which could be used for data calculation...

Would you please help? Thanks!

You could calculate the difference in a report by subtracting one month from
the other.

Since you are pivoting by month, I would suggest you take a look at the
dynamic monthly crosstab report solution found at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP

Dear Duane

Below is my data structure which is in crosstab and it can calculate the sum
of balance amount. Assume there is only 'Jun' and 'July' value in column
[month], how can I add one more column to calculate balance difference (i.e.
July data minus June data) in a crosstab query?

Thank you.

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance]) AS [SumOfBalance]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name], Sum([total-update-mapping-Jun n Jul].[Balance]) AS
Grandtotal
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].month;

Could you please provide more information such as table structure(s), sample
data, and desired display?


--
Duane Hookom
Microsoft Access MVP


:

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.


:

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

:

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Duane Hookom]

If you only have 2 months, you should be able to use something like this
which assumes the values for Month are either "Jun" or "July" as per a
previous post.

SELECT T.Pointer, T.[main a/c], T.[main a/c name],
T.[sub a/c], T.[sub a/c name], Sum(T.[Balance]) AS Grandtotal,
Sum(Abs(T.Month = "Jun") * Balance) As June,
Sum(Abs(T.Month = "July") * Balance) As July,
Sum(Abs(T.Month = 7) * Balance) - Sum(Abs(T.Month = 6) * Balance) As TheDiff
FROM [total-update-mapping-Jun n Jul] T
GROUP BY T.Pointer, T.[main a/c], T.[main a/c name],
T.[sub a/c], T.[sub a/c name]

--
Duane Hookom
Microsoft Access MVP

Dear Duane

Thanks for your reply. I would like to calculate the difference (by
substracting one month from the other) in a crosstab query, instead of doing
it in report / form. It is because we would like to have result in datasheet
format, which could be used for data calculation...

Would you please help? Thanks!

You could calculate the difference in a report by subtracting one month from
the other.

Since you are pivoting by month, I would suggest you take a look at the
dynamic monthly crosstab report solution found at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP

Dear Duane

Below is my data structure which is in crosstab and it can calculate the sum
of balance amount. Assume there is only 'Jun' and 'July' value in column
[month], how can I add one more column to calculate balance difference (i.e.
July data minus June data) in a crosstab query?

Thank you.

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance]) AS [SumOfBalance]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name], Sum([total-update-mapping-Jun n Jul].[Balance]) AS
Grandtotal
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].month;


:

Could you please provide more information such as table structure(s), sample
data, and desired display?


--
Duane Hookom
Microsoft Access MVP


:

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.


:

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

:

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[George]

Dear Duane

Many thanks for your reply. I am in fact a baby programmer and cannot get
exactly what you mean...I have tried the below but got some error running
query...would you please help again?? thanks!!!

Error message: does not recognize balance as a valid field name...

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance as of Jun09]) AS
[SumOfBalance as of Jun09]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name],
Sum(Abs([total-update-mapping-Jun n Jul].Month = "Jun") * Balance) As June,
Sum(Abs([total-update-mapping-Jun n Jul].Month = "July") * Balance) As July,
Sum(Abs([total-update-mapping-Jun n Jul].Month = 7) * Balance) -
Sum(Abs([total-update-mapping-Jun n Jul].Month = 6) * Balance) As TheDiff
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].Month;

Dear Duane

Thanks for your reply. Your method suggested below is related to form /
report level (correct me if I am wrong). But, due to the no. of records and
for further data modification, I would like to calculate the difference in
the crosstab query…

Do you think it is possible?

Thanks in advance.

You could calculate the difference in a report by subtracting one month from
the other.

Since you are pivoting by month, I would suggest you take a look at the
dynamic monthly crosstab report solution found at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP

Dear Duane

Below is my data structure which is in crosstab and it can calculate the sum
of balance amount. Assume there is only 'Jun' and 'July' value in column
[month], how can I add one more column to calculate balance difference (i.e.
July data minus June data) in a crosstab query?

Thank you.

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance]) AS [SumOfBalance]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name], Sum([total-update-mapping-Jun n Jul].[Balance]) AS
Grandtotal
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].month;


:

Could you please provide more information such as table structure(s), sample
data, and desired display?


--
Duane Hookom
Microsoft Access MVP


:

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.


:

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

:

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Duane Hookom]

Did you try paste what I suggested into the SQL view? My SQL statement wasn't
a crosstab. Why did you make yours a crosstab?

--
Duane Hookom
Microsoft Access MVP

Dear Duane

Many thanks for your reply. I am in fact a baby programmer and cannot get
exactly what you mean...I have tried the below but got some error running
query...would you please help again?? thanks!!!

Error message: does not recognize balance as a valid field name...

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance as of Jun09]) AS
[SumOfBalance as of Jun09]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name],
Sum(Abs([total-update-mapping-Jun n Jul].Month = "Jun") * Balance) As June,
Sum(Abs([total-update-mapping-Jun n Jul].Month = "July") * Balance) As July,
Sum(Abs([total-update-mapping-Jun n Jul].Month = 7) * Balance) -
Sum(Abs([total-update-mapping-Jun n Jul].Month = 6) * Balance) As TheDiff
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].Month;

Dear Duane

Thanks for your reply. Your method suggested below is related to form /
report level (correct me if I am wrong). But, due to the no. of records and
for further data modification, I would like to calculate the difference in
the crosstab query…

Do you think it is possible?

Thanks in advance.

You could calculate the difference in a report by subtracting one month from
the other.

Since you are pivoting by month, I would suggest you take a look at the
dynamic monthly crosstab report solution found at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP


:

Dear Duane

Below is my data structure which is in crosstab and it can calculate the sum
of balance amount. Assume there is only 'Jun' and 'July' value in column
[month], how can I add one more column to calculate balance difference (i.e.
July data minus June data) in a crosstab query?

Thank you.

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance]) AS [SumOfBalance]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name], Sum([total-update-mapping-Jun n Jul].[Balance]) AS
Grandtotal
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].month;


:

Could you please provide more information such as table structure(s), sample
data, and desired display?


--
Duane Hookom
Microsoft Access MVP


:

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.


:

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

:

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks

 

[Duane Hookom]

I also noticed I had edited only part of my SQL after reading an earlier post
from you that stated your Month values were "Jun" and "July" so this might be
better:

SELECT T.Pointer, T.[main a/c], T.[main a/c name],
T.[sub a/c], T.[sub a/c name], Sum(T.[Balance]) AS Grandtotal,
Sum(Abs(T.Month = "Jun") * Balance) As June,
Sum(Abs(T.Month = "July") * Balance) As July,
Sum(Abs(T.Month = "July") * Balance) - Sum(Abs(T.Month = "Jun") * Balance)
As TheDiff
FROM [total-update-mapping-Jun n Jul] T
GROUP BY T.Pointer, T.[main a/c], T.[main a/c name],
T.[sub a/c], T.[sub a/c name]

--
Duane Hookom
Microsoft Access MVP

Dear Duane

Many thanks for your reply. I am in fact a baby programmer and cannot get
exactly what you mean...I have tried the below but got some error running
query...would you please help again?? thanks!!!

Error message: does not recognize balance as a valid field name...

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance as of Jun09]) AS
[SumOfBalance as of Jun09]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name],
Sum(Abs([total-update-mapping-Jun n Jul].Month = "Jun") * Balance) As June,
Sum(Abs([total-update-mapping-Jun n Jul].Month = "July") * Balance) As July,
Sum(Abs([total-update-mapping-Jun n Jul].Month = 7) * Balance) -
Sum(Abs([total-update-mapping-Jun n Jul].Month = 6) * Balance) As TheDiff
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].Month;

Dear Duane

Thanks for your reply. Your method suggested below is related to form /
report level (correct me if I am wrong). But, due to the no. of records and
for further data modification, I would like to calculate the difference in
the crosstab query…

Do you think it is possible?

Thanks in advance.

You could calculate the difference in a report by subtracting one month from
the other.

Since you are pivoting by month, I would suggest you take a look at the
dynamic monthly crosstab report solution found at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP


:

Dear Duane

Below is my data structure which is in crosstab and it can calculate the sum
of balance amount. Assume there is only 'Jun' and 'July' value in column
[month], how can I add one more column to calculate balance difference (i.e.
July data minus June data) in a crosstab query?

Thank you.

TRANSFORM Sum([total-update-mapping-Jun n Jul].[Balance]) AS [SumOfBalance]
SELECT [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun n
Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name], Sum([total-update-mapping-Jun n Jul].[Balance]) AS
Grandtotal
FROM [total-update-mapping-Jun n Jul]
GROUP BY [total-update-mapping-Jun n Jul].Pointer, [total-update-mapping-Jun
n Jul].[main a/c], [total-update-mapping-Jun n Jul].[main a/c name],
[total-update-mapping-Jun n Jul].[sub a/c], [total-update-mapping-Jun n
Jul].[sub a/c name]
PIVOT [total-update-mapping-Jun n Jul].month;


:

Could you please provide more information such as table structure(s), sample
data, and desired display?


--
Duane Hookom
Microsoft Access MVP


:

Dear ALL

Similar to previous questions, can anyone hep if I want to substract data
between 2 months in the crosstab query? instead of summation.

Thanks for your help.


:

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in datasheet view of the query. You might
try dragging the column in datasheet view and see if that will work (BIG Guess
on my part).

If you need to calculate YTD values and show that for the monthly sum, then
you need to change the query significantly. Do you want to show the monthly
sum and the monthly YTD in the same query? If so that is even tougher to do.

Post the SQL of your existing query if you want just the monthly YTD totals in
the month column. I (or someone) should be able to figure out from that how
to give you the YTD results. If the Crosstab is based on another query, you
would be wise to post it also.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
Thanks Duane, John for your help. John you code worked like a charm. Thanks
so much. Now I have couple of questions. The GrandTotal column is showing up
before each of the Months value. Is there any way I can get it pushed to the
very right column. The second is that I need to calculate YTD values. I got
it correct using separate query. However is it possible to add YTD value in
the same crosstab. E.g. if it is July then I should have YTD for sum of Month
1 to Month 6. If it is August now then the YTD value will be sum of Months1
to Month 7. This YTD value should be the last but one column prior to the
GrandTotal value. Is this possible here or do I have to do temp tables and
joins. Please let me know.

:

If all you want is to get a total for the numbers in the crosstab you can
modify the crosstab.

just add the value field to the query again
set the Total line to SUM
and the crosstab to RowHeading.

Right now the SQL of your crosstab probably looks like
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)

The SQL would be changed to this to add a grand total for the row.
TRANSFORM Sum(Amount) as AmountTOTAL
SELECT Slsman, Custnum, [Name]
, SUM(Amount) as GrandTotal
FROM SomeTable
GROUP BY Slsman, Custnum, [Name]
PIVOT Month(SomeDateField)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jack wrote:
slsman custnum Name Month01 Month02 Month03 Month04 Month05 Month06 Month07 Month08 Month09 Month10 Month11 Month12
932 171042 SIEMENS HLTHCARE
DIAG 428030 369694 427864 464207 444013 494008 346024 234660 169884 202184 141299 264910

THe above is the output from a cross tabl query.

i have another query output as follows:


slsman Total custnum Name
932 3986777 171042 SIEMENS HLTHCARE DIAG


I need to combine these two queries to get another column total to the
column in the first output. i am not sure the best way to handle this. I
would appreciate any help to resolve this. Thanks