How to sum with criteria?

  • Thread starter Thread starter Meilu
  • Start date Start date
M

Meilu

Hello,

I posted this question almost a month ago... and then
misplaced the answer! >_< Sorry...

This is the problem. I have a Table A and Table B
Table A:
ID Date Qty
Z 1/1/2003 1
Z 1/2/2003 1
Z 1/3/2003 1
Z 1/4/2003 1

Table B:
ID Qty
Z 50
B 30

I want a query that will return me this:
ID Date QtyOutstanding
Z 1/1/2003 49
Z 1/2/2003 48
Z 1/3/2003 47
Z 1/4/2003 46

I followed someones advice and made a temporary table
MyTable for calculation purposes. MyTable has the
cooresponding entries of Table A and Table B.
ie: if I was working with ID Z, MyTable would look like
this.
ID Date Qty
Z 1/1/2003 1
Z 1/2/2003 1
Z 1/3/2003 1
Z 1/4/2003 1
Z 1/4/1800 -50

This is the Query that I have right now ... it's not
working. I'm not sure why. I'd appreciate any
suggestions!

Select A.ID, A.Date, Sum(MyTable.Qty)
From A, MyTable
Where MyTable.Date <= A.Date;

Thanks in advance!
Meilu
 
Back
Top