Problems in Cross Tab Query

[EDom]

Hi
We are wanting to prepare a report which will show us month wise balances of
ledgers. However on preparing the report only those months are showing which
actually has transactions, even though I am sending a seperate table with
all the months to the report. I have even selected the Column to show months
from the Month table & not the Transaction table.

Also the Sum figure of ledger balances are some times showing in -ves. So I
want positive balances to come as Abs(balance) followed by 'Dr' & negetive
as Abs(balance) followed by 'Cr'. Can such formulas be also applied. If so,
How?

I have sent the whole transaction table to the report.

Thanks.

Regards

Prodipta

 

[jasonkester]

Hi
We are wanting to prepare a report which will show us month wise balances of
ledgers. However on preparing the report only those months are showing which
actually has transactions [...]
I have even selected the Column to show months
from the Month table & not the Transaction table.

As in this:?
SELECT months.monthName
, transactions.*
from transactions
, months
where transactions.monthid = month.monthid


That is not how SQL works. The query above will strip out any months
that have no transactions. If you want to pad out the transactionless
months with nulls, you'll have to use an OUTER JOIN, as such:


SELECT months.monthName
, transactions.*
from transactions
, months
where transactions.monthid =* month.monthid


Note the subtle difference. We're using =* instead of = to join the
tables. This will ensure that a record is returned for every month,
regardless of whether any transactions exist.

You can use *= or =*, with the * facing the table from which you would
like to see all records. There is also a syntax that explicitly says
the word OUTER JOIN that works in SQL server and Access only, but not
Oracle (though it is in fact the ANSI standard.)


Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/