How to Calculate-> total = Qty - RunningSum

[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

 

[Michel Walsh]

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

 

[Guest]

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

 

[Michel Walsh]

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

 

[Meilu]

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