revers minus

[mh_amri]

hi guyz,we have little office and want to make some regular thing machinary
here it is:
we sell stuffs(for example think us as a newspaper company) , we give
newspaper to our seller and then they return the amount of newspaper that
remains at the end of the week , and because every day have a special rate
for income its important to calculate the sell amount from beginning
for example i give 150,100,250,400 newspaper to the seller and at the end of
the week he claim 300 of them has been sold, it means he sold out(*) 150 in
first day , and 100 in 2nd day and 50 in 3rd day9(**), and must return 200
for 3rd day and 400 for the 4th day
for this purpos i made a query that collect data about the day and the
amount that we gave the seller, and designed a form that take the amount of
that the seller sold out(this amount is a number for a whole week, for
example 300 ) , the problem is calculating the sold amount for each day(see
from * to **) , because each day have a rate value that it has read from
another table and multiply by the amount that sold on that day
i mean i want to do generate a table like this:
[seller] [day] [give amount] [sold amount] [retun amount]
1 1 150 150 0
1 2 100 100 0
1 3 250 50 200
1 4 400 0
400

my problem is calculating the the [sold amount] , the weekly returned amount
has entered in a form (here is 300) i have to give 300 to access and generate
the [sold amount]
i used many ways, but because the calculation of each row need to access the
last row data all of that way failed
anyway , any comment will appreciate

 

[Clifford Bass via AccessMonster.com]

Hi,

Interesting question. Here is one way. I made the presumption that you
will be using actual dates, an not day numbers. It assumes only one type of
item. You can expand to allow for multiple types.

tblDistributions
Seller Distribution_Date Items_Distributed
1 12/26/2009 600
1 12/28/2009 150
1 12/29/2009 100
1 12/30/2009 250
1 12/31/2009 400
1 1/3/2010 235

tblSales
Seller Report_Date Items_Sold
1 12/27/2009 400
1 1/4/2010 300
1 1/10/2010 200

Report_Date is the date on which the seller reports the total sales for
the week and is assumed to be after the last distribution date for the week.
So for the distribution week of 12/27/2009-1/2/2010 it will use the first
report date after 1/2/2010, which would be in this case the 1/2/2010 row.

The query:

PARAMETERS [Start Date:] DateTime;
SELECT A.Seller, A.Distribution_Date, A.Items_Distributed, C.Items_Sold AS
Total_Sold, Switch([C].[Items_Sold]>=Sum(.[Items_Distributed]),[A].
[Items_Distributed],Sum(.[Items_Distributed])-[A].[Items_Distributed]<[C].
[Items_Sold],[C].[Items_Sold]+[A].[Items_Distributed]-Sum(.
[Items_Distributed]),True,0) AS Sold_Amount, Switch([C].[Items_Sold]>=Sum(.
[Items_Distributed]),0,Sum(.[Items_Distributed])-[A].[Items_Distributed]<
[C].[Items_Sold],Sum(.[Items_Distributed])-[C].[Items_Sold],True,[A].
[Items_Distributed]) AS Return_Amount
FROM (tblDistributions AS A INNER JOIN tblDistributions AS B ON A.Seller = B.
Seller) INNER JOIN tblSales AS C ON A.Seller = C.Seller
WHERE (((A.Distribution_Date) Between [Start Date:] And DateAdd("d",6,[Start
Date:])) AND ((B.Distribution_Date)>=[Start Date:] And (B.Distribution_Date)
<=[A].[Distribution_Date]) AND ((C.Report_Date)=(select Min(D.Report_Date)
from tblSales as D where D.Seller = C.Seller and D.Report_Date > DateAdd("d",
6,[Start Date:]))))
GROUP BY A.Seller, A.Distribution_Date, A.Items_Distributed, C.Items_Sold;

This will prompt for a start date. If you enter 12/27/2009 you will get
these results with the above data:

Seller Distribution_Date Items_Distributed Total_Sold Sold_Amount
Return_Amount
1 12/28/2009 150 300
150 0
1 12/29/2009 100 300
100 0
1 12/30/2009 250 300
50 200
1 12/31/2009 400 300
0 400

Hope that helps,

Clifford Bass

hi guyz,we have little office and want to make some regular thing machinary
here it is:
we sell stuffs(for example think us as a newspaper company) , we give
newspaper to our seller and then they return the amount of newspaper that
remains at the end of the week , and because every day have a special rate
for income its important to calculate the sell amount from beginning
for example i give 150,100,250,400 newspaper to the seller and at the end of
the week he claim 300 of them has been sold, it means he sold out(*) 150 in
first day , and 100 in 2nd day and 50 in 3rd day9(**), and must return 200
for 3rd day and 400 for the 4th day
for this purpos i made a query that collect data about the day and the
amount that we gave the seller, and designed a form that take the amount of
that the seller sold out(this amount is a number for a whole week, for
example 300 ) , the problem is calculating the sold amount for each day(see
from * to **) , because each day have a rate value that it has read from
another table and multiply by the amount that sold on that day
i mean i want to do generate a table like this:
[seller] [day] [give amount] [sold amount] [retun amount]
1 1 150 150 0
1 2 100 100 0
1 3 250 50 200
1 4 400 0
400

my problem is calculating the the [sold amount] , the weekly returned amount
has entered in a form (here is 300) i have to give 300 to access and generate
the [sold amount]
i used many ways, but because the calculation of each row need to access the
last row data all of that way failed
anyway , any comment will appreciate