Problems in Cross Tab Query
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,
I have sent the whole transaction table to the report.
Re: Problems in Cross Tab Query
> 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:?
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:
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.)
Expat Software Consulting Services
|All times are GMT. The time now is 01:21 PM.|
Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.