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_CHAR(A.TRX_DATE,'MON-YY'),TO_CHAR(SYSDATE,'MON-YY'),(SUM(SHIP_QTY)),0))
QTY1
,-1*(DECODE(TO_CHAR(A.TRX_DATE,'MON-YY'),TO_CHAR(SYSDATE,'MON-YY'),(SUM(SHIP_QTY)),0))
QTY1
Any help, suggestions would be greatly appreciated.
Thank you.
Cindy
ci******@yahoo.com