Re: Tricky query question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"George" wrote:

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;



"George" wrote:

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.

"Duane Hookom" wrote:

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


"Crosstab report for cross-month data" wrote:

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;


"Duane Hookom" wrote:

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


--
Duane Hookom
Microsoft Access MVP


"Crosstab report for cross-month data" wrote:

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.


"John Spencer" wrote:

I don't think it is possible to reposition the result of the grandtotal to the
right of all the monthly columns in data*** view of the query. You might
try dragging the column in data*** 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.

"John Spencer" wrote:

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


.

Quantcast