Crosstab report for cross-month data

  • Thread starter Thread starter Crosstab report for cross-month data
  • Start date Start date
C

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.
 
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;
 
Back
Top