slight change in problem

  • 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:
Group ID Date Qty
1 Z 1/1/2003 1
2 Z 1/1/2003 1
1 Z 1/2/2003 1
2 Z 1/2/2003 1
1 Z 1/3/2003 1
2 Z 1/3/2003 1
1 Z 1/4/2003 1
2 Z 1/4/2003 1

Table B:
ID Qty
Z 50
B 30

I want a query that will return me the following:
ie: Working with ID Z
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

I also have a Query: DetailSum that sums up Table A into:
ID Date QtyOutstanding
Z 1/1/2003 2
Z 1/2/2003 2
Z 1/3/2003 2
Z 1/4/2003 2

The following are two different approaches I tried to
solve my problem. Neither approach really works.

First try:

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


Second try:

Select DetailSum.ID, DetailSum.Date, Sum(MyTable.Qty)
From DetailSum, MyTable
Wehre MyTable.Date <= DetailSum.Date
Group By DetailSum.ID, DetailSum.Date;

Any suggestions on why this isn't working?

Thanks in advance!
Meilu
 
Hi,


The first try is almost the right one, you just miss a - in front of SUM.

SELECT a.ID, a.Date, -Sum(b.Qty) AS SumOfQty
FROM MyTable AS a , MyTable AS b
WHERE a.ID=b.ID and a.Date>=b.Date
GROUP BY a.ID, a.Date;


returns me the result:

Query29
ID Date SumOfQty
1 1800-01-04 50
1 2003-01-01 49
1 2003-01-02 48
1 2003-01-03 47
1 2003-01-04 46



when I start with myTable as:

MyTable
ID Date Qty
1 2003-01-01 1
1 2003-01-02 1
1 2003-01-03 1
1 2003-01-04 1
1 1800-01-04 -50




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top