Aged Debt grand totals in SQL

[Darren]

Hi,

I am trying to write an Aged Debtors report in MSquery, I have created
all of the columns sucessfully, I just need to have totals of each
column at the bottom of the report - how can this be done?

this id the code that I have so far: -

select
cucode,
cuname,
cuphone,
cubalance,

sum(case when st_date between (getdate()-30) and Getdate() then
st_gross end )as '0-30days',
sum( case when st_date between (getdate()-60) and (Getdate()-30) then
st_gross end) as '30-60days',
sum( case when st_date between (getdate()-90) and (Getdate()-60) then
st_gross end )as '60-90days',
sum( case when st_date between (getdate()-120) and (Getdate()-90) then
st_gross end )as '90-120days',
sum( case when st_date between (getdate()-180) and (Getdate()-120) then
st_gross end )as '120-180days',
sum( case when st_date > (getdate()-180) then st_gross end) as
'180days +'

from sl_accounts, sl_transactions

where
cucode = st_copycust
and cubalance <> 0

group by cucode, cuname, cuphone, cubalance
order by cucode

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Standard SQL doesn't provide a column total function. Use the data from
the query in a reporting application like Crystal Reports, Access or
Excel. They have provisions for column totaling functions.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlrRmIechKqOuFEgEQLOhQCg46aS2zVSNhEAfG5HgPeV2K kN4z0AnjsB
sUbC6lWuSpi3X84Un4K6mGTV
=H11y
-----END PGP SIGNATURE-----
Darren wrote:

Hi,

I am trying to write an Aged Debtors report in MSquery, I have created
all of the columns sucessfully, I just need to have totals of each
column at the bottom of the report - how can this be done?

this id the code that I have so far: -

select
cucode,
cuname,
cuphone,
cubalance,

sum(case when st_date between (getdate()-30) and Getdate() then
st_gross end )as '0-30days',
sum( case when st_date between (getdate()-60) and (Getdate()-30) then
st_gross end) as '30-60days',
sum( case when st_date between (getdate()-90) and (Getdate()-60) then
st_gross end )as '60-90days',
sum( case when st_date between (getdate()-120) and (Getdate()-90) then
st_gross end )as '90-120days',
sum( case when st_date between (getdate()-180) and (Getdate()-120) then
st_gross end )as '120-180days',
sum( case when st_date > (getdate()-180) then st_gross end) as
'180days +'

from sl_accounts, sl_transactions

where
cucode = st_copycust
and cubalance <> 0

group by cucode, cuname, cuphone, cubalance
order by cucode

[Alan Webb]

Darren,
Access on its own won't do what you want in just a query. SQL Server,
though, through TransactSQL, can build a temporary table with the totals as
the last row in the table. First use an insert statement to add all the
rows to your temporary table from the result set. Then run a seperate
insert statement to insert the last row with the totals. JetSQL doesn't
support scripted SQL--multiple SQL statements in the same query object
(dang)--so if you do want to implement this in Access VBA combined with SQL
is the best choice (macros--yuck).
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Darren" <da*******@ntlworld.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hi,

I am trying to write an Aged Debtors report in MSquery, I have created
all of the columns sucessfully, I just need to have totals of each
column at the bottom of the report - how can this be done?

this id the code that I have so far: -

select
cucode,
cuname,
cuphone,
cubalance,

sum(case when st_date between (getdate()-30) and Getdate() then
st_gross end )as '0-30days',
sum( case when st_date between (getdate()-60) and (Getdate()-30) then
st_gross end) as '30-60days',
sum( case when st_date between (getdate()-90) and (Getdate()-60) then
st_gross end )as '60-90days',
sum( case when st_date between (getdate()-120) and (Getdate()-90) then
st_gross end )as '90-120days',
sum( case when st_date between (getdate()-180) and (Getdate()-120) then
st_gross end )as '120-180days',
sum( case when st_date > (getdate()-180) then st_gross end) as
'180days +'

from sl_accounts, sl_transactions

where
cucode = st_copycust
and cubalance <> 0

group by cucode, cuname, cuphone, cubalance
order by cucode