If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
revers minus
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 |
#2
|
|||
|
|||
revers minus
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([b].[Items_Distributed]),[A]. [Items_Distributed],Sum([b].[Items_Distributed])-[A].[Items_Distributed][C]. [Items_Sold],[C].[Items_Sold]+[A].[Items_Distributed]-Sum([b]. [Items_Distributed]),True,0) AS Sold_Amount, Switch([C].[Items_Sold]=Sum([b]. [Items_Distributed]),0,Sum([b].[Items_Distributed])-[A].[Items_Distributed] [C].[Items_Sold],Sum([b].[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 mh_amri wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1 |
Thread Tools | |
Display Modes | |
|
|