Crosstab report for cross-month data

[Crosstab report for cross-month data]

I need to show data in a crosstab report format with Row: a/c no and a/c
name, and column: monthly balance. How can I get the difference between
current month balance and previous month balance? e.g. July and June. For
next month, I need to get the difference between Aug and July. And a/c no
may be closed and newly opened each month, and I need to show monthly balance
for each record.

Many thanks for your help in advance.

 

[KARL DEWEY]

Use this query, changing table and field names to yours, for your crosstab
query.

SELECT YourTable.[a/c no], YourTable.[a/c name],
CVDate(Format([YourTable_1].[Act_Date],"YYYY,MM,1")) AS [AC MONTH],
YourTable.QTY AS Prev_Month, YourTable_1.QTY AS [The Month],
Sum([YourTable_1].[QTY]-[YourTable].[QTY]) AS Monthly_BAL
FROM YourTable INNER JOIN YourTable AS YourTable_1 ON (YourTable.[a/c name]
= YourTable_1.[a/c name]) AND (YourTable.[a/c no] = YourTable_1.[a/c no])
WHERE
(((CVDate(Format([YourTable_1].[Act_Date],"YYYY,MM,1")))=DateAdd("M",-1,CVDate(Format([YourTable].[Act_Date],"YYYY,MM,1")))))
GROUP BY YourTable.[a/c no], YourTable.[a/c name],
CVDate(Format([YourTable_1].[Act_Date],"YYYY,MM,1")), YourTable.QTY,
YourTable_1.QTY;