Subquery Sum by Date Range

[cincyn12]

Hi,

I'm really new to access. (Am only a beginner in SQL) I'm trying to
create subqueries similar to SQL+ for a sum using case when within date
ranges. (Or Decode by month sum)

i.e. Trying to get total shipped qty for customers based on quarters.
10-01-04 through 12-31-04, then 01-01-05 thru 03-31-05... etc

Current access table look like:

Cust# Ship Qty Trx Date Cost ... etc
A01 1000 01-01-05
A01 1500 11-01-04
A01 100 01-15-05
A01 80 02-05-05
A01 500 01-01-05
B05 60 03-16-05
B05 1560 10-23-04
B05 25 01-15-05
B05 600 02-05-05
B05 450 01-01-05

Need for it to look something like:

Cust# Q1 Qty Q2 Qty Cost ... etc
A01 1500 1680
B05 1560 1235

We can't use cross tab as there are 3 queries linked to the main query
table we would like to run this subquery on. There are also 12 columns
to the right of cost. Crosstab report won't link all that info.

If it were in SQL, then it would look something like this:
, CASE
when trx_date BETWEEN '01-OCT-2004' AND '31-DEC-2004' then sum ship_qty

when a.trx_date BETWEEN '01-JAN-2005' AND '31-MAR-2005' then sum
ship_qty
END
Or in decode:
,-1*(DECODE(TO_CH AR(A.TRX_DATE,' MON-YY'),TO_CHAR(SY SDATE,'MON-YY'),(SUM(SHIP_ QTY)),0))
QTY1
,-1*(DECODE(TO_CH AR(A.TRX_DATE,' MON-YY'),TO_CHAR(SY SDATE,'MON-YY'),(SUM(SHIP_ QTY)),0))
QTY1

Any help, suggestions would be greatly appreciated.

Thank you.

Cindy

ci******@yahoo. com

[MGFoster]

ci******@yahoo. com wrote:

Hi,

I'm really new to access. (Am only a beginner in SQL) I'm trying to
create subqueries similar to SQL+ for a sum using case when within date
ranges. (Or Decode by month sum)

i.e. Trying to get total shipped qty for customers based on quarters.
10-01-04 through 12-31-04, then 01-01-05 thru 03-31-05... etc

Current access table look like:

Cust# Ship Qty Trx Date Cost ... etc
A01 1000 01-01-05
A01 1500 11-01-04
A01 100 01-15-05
A01 80 02-05-05
A01 500 01-01-05
B05 60 03-16-05
B05 1560 10-23-04
B05 25 01-15-05
B05 600 02-05-05
B05 450 01-01-05

Need for it to look something like:

Cust# Q1 Qty Q2 Qty Cost ... etc
A01 1500 1680
B05 1560 1235

We can't use cross tab as there are 3 queries linked to the main query
table we would like to run this subquery on. There are also 12 columns
to the right of cost. Crosstab report won't link all that info.

If it were in SQL, then it would look something like this:
, CASE
when trx_date BETWEEN '01-OCT-2004' AND '31-DEC-2004' then sum ship_qty

when a.trx_date BETWEEN '01-JAN-2005' AND '31-MAR-2005' then sum
ship_qty
END
Or in decode:
,-1*(DECODE(TO_CH AR(A.TRX_DATE,' MON-YY'),TO_CHAR(SY SDATE,'MON-YY'),(SUM(SHIP_ QTY)),0))
QTY1
,-1*(DECODE(TO_CH AR(A.TRX_DATE,' MON-YY'),TO_CHAR(SY SDATE,'MON-YY'),(SUM(SHIP_ QTY)),0))
QTY1

Any help, suggestions would be greatly appreciated.

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

In Access you'd do something like this:

Sum(IIf(a.trx_d ate BETWEEN #1/1/2005# AND #3/31/2005#, ship_qty)) As
SumShipQty

The IIf() (Immediate If) function has this syntax:

IIf(evaluation expression, true expression, false expression)

If the evaluation is true the true expression is returned; otherwise,
the false expression is returned. When using IIf() in Access' SQL if
the false expression is left off and the evaluation yields False, then a
NULL is returned. The SUM() will sum the ship_qty only when the
evaluation expression returns True; otherwise it will sum NULL, which
returns zero.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQzwMXYechKq OuFEgEQKTrACg5R lDOKQbWz6wtV+sJ bupTdGb4p8AniTD
OvyNxqQfNzrmYdj Zc5PDlgy4
=eLXN
-----END PGP SIGNATURE-----