How to Calculate-> total = Qty - RunningSum

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

Meilu

I don't know if this is possible ... but I have to get a
2nd opinion.

I have 2 tables (simplified to just illustrate my problem)

A
Company, Qty
Tommy 100

B
Date, GroupID, Company, QtyMade
1/1 11 Tommy 10
1/1 12 Tommy 10
1/2 11 Tommy 10
1/2 12 Tommy 10
1/3 11 Tommy 10
1/3 12 Tommy 10

I want a Qry that will return
B.Date, B.Company, A.Qty - Sum*B.QtyMade

So for the above input, I would like the following output:
1/1 Tommy 80
1/2 Tommy 60
1/3 Tommy 40

I tried this:
Select B.Date, B.Company, A.Qty-Sum*B.QtyMade
From B, A
Where B.Company = A.Company and B.Date<=B.Date
Group By B.Company, B.Date

It doesn't work ....

Any idea how to do this?

-Meilu
 
Hi,

Close, somehow. A.Qty is not aggregated, neither grouped, and some minor
syntax error, but, something like:


SELECT A.Date, A.Company, SUM(A.Qty) - SUM(B.QtyMade)
FROM B INNER JOIN A ON B.Company=A.Company
WHERE B.Date<=A.Date
GROUP BY A.Company, A.Date


should do.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,
Thanks for your advice. But ... there's no date field
for the table A. The field linking A and B is Company.
So your suggestion wouldn't work. Do you have any other
ideas?

Thanks again! :-)
Meilu
 
Hi,


Merge the record of the first table into the second table, giving a negative
value for the quantity and associate it a date in the past. Use only the
resulting table, myTable:

Date, GroupID, Company, QtyMade
0/0 00 Tommy -100
1/1 11 Tommy 10
1/1 12 Tommy 10
1/2 11 Tommy 10
1/2 12 Tommy 10
1/3 11 Tommy 10
1/3 12 Tommy 10



Then, use the query:


SELECT A.Date, A.Company, - SUM(B.QtyMade)
FROM myTable As B INNER JOIN myTable As A
ON B.Company=A.Company
WHERE B.Date<=A.Date
GROUP BY A.Company, A.Date



Hoping it may help,
Vanderghast, Access MVP
 
Dear Michel,

Thanks! I think it'll work that way! I just need to
figure out how to "merge" the record into the table. I
think I just need to use a "create table" qry right?

:-D I'm SO HAPPY to finally make some head way.

Thanks again :-)
Meilu
 
Back
Top