محاسبه اطلاعات سطر قبل در سطر جدید در کوئری

[m_bargostavan_66]

سلام دوستان
میخوام بدونم چجوری تو اکسس قسمت کوئری اطلاعات یک یا دو ستون سطر قبل رو تو سطر بعد بیارم
یا کلی بگم محاسبات هر سطر نیاز به اطلاعات سطر قبل داره
فایل رو بصورت اکسل با فرمول ها گذاشتم که راحت تر منظورم رو متوجه بشید
ببنید با وارد کردن اطلاعات سطر های آبی محاسبات تو سطرهای سبز به صورت عادی جواب میده
ولی جواب سطرهای زرد نیاز به اطلاعات سطر قبل داره که تو اکسل راحت میشه فرمول داد ولی نمیدونم تو کوئری تو اکسس چطوری فرمول بدم که اطلاعات سطر قبل رو استفاده کنم
اگر جوابش رو بدید ممنون میشم

[mazoolagh]

1- یک تاپیک مشابه داشتیم برای running sum در فرم که اونجا یک لینک به مایکروسافت یکی از دوستان معرفی کرده بود که همون روش رو شما هم میتونین به کار ببرین.
https://barnamenevis.org/showthread.php?559415-جمع-زدن-مقدار-رکوردهای-قبلی-در-رکورد-جاری-(Query)

البته همونجا هم نوشتم روش عالی و بی اشکالی نیست ولی برای تعداد رکورد کم مسئله ای نیست.
در کل بهتر هست که برای محاسبات انباشته فیلدهای مناسب به جدول اضافه بشه و محاسبات هم هنگام data entry انجام بشه و نه با اجرای کوئری.
فرصت کنم نمونه میگذارم ولی کافی هست چند تابع ساده برای update و insert و delete نوشته بشه و برای دسترسی به مقادیر رکوردهای قبلی هم از رکوردست استفاده کرد.
کدش شاید 20-30 خط بیشتر نشه و برای چند ده هزار رکورد هم مشکل پرفورمنس نداره.

2- اگر کاری در اکسل راحتتر و بهتر و اصولی انجام میشه باید در همون اکسل پیاده بشه و نه اکسس (و بالعکس) ، ماهیت این دو با هم فرق میکنه و هر کدوم برای یک سری نیازها ساخته شده:
Use the right tools for the right job

[mazoolagh]

از data macro هم میشه استفاده کرد ولی هم اجراش سخت تر هست و هم با تعداد رکورد زیاد مشکل پرفورمنس بروز میکنه

[m_bargostavan_66]

1- یک تاپیک مشابه داشتیم برای running sum در فرم که اونجا یک لینک به مایکروسافت یکی از دوستان معرفی کرده بود که همون روش رو شما هم میتونین به کار ببرین.
https://barnamenevis.org/showthread.php?559415-جمع-زدن-مقدار-رکوردهای-قبلی-در-رکورد-جاری-(Query)

البته همونجا هم نوشتم روش عالی و بی اشکالی نیست ولی برای تعداد رکورد کم مسئله ای نیست.
در کل بهتر هست که برای محاسبات انباشته فیلدهای مناسب به جدول اضافه بشه و محاسبات هم هنگام data entry انجام بشه و نه با اجرای کوئری.
فرصت کنم نمونه میگذارم ولی کافی هست چند تابع ساده برای update و insert و delete نوشته بشه و برای دسترسی به مقادیر رکوردهای قبلی هم از رکوردست استفاده کرد.
کدش شاید 20-30 خط بیشتر نشه و برای چند ده هزار رکورد هم مشکل پرفورمنس نداره.

2- اگر کاری در اکسل راحتتر و بهتر و اصولی انجام میشه باید در همون اکسل پیاده بشه و نه اکسس (و بالعکس) ، ماهیت این دو با هم فرق میکنه و هر کدوم برای یک سری نیازها ساخته شده:
Use the right tools for the right job
تو اون صفحه دیدم ولی چیزی متوجه نشدم
چون اطلاعات زیاد هست و تعداد شیت اکسل زیاد میشه فکرکنم اکسس بهتره و میتونم یکسری گزارشات بیشتر بگیرم
میشه طریقه رکوردست رو که فرمودید توضیح بدید چجوریه
در ضمن اگه نمونه بگذارید که خیلی ممنون میشم

[mazoolagh]

در طراحی دیتابیس معمولا توصیه میشه اطلاعات تکراری نگهداری نشه ولی در این مورد ما برای راحتی و سرعت محاسبات، اطلاعات رکوردهای قبل رو در رکورد جدید هم ذخیره میکنیم.

در نمونه پیوست این اطلاعات فقط جمع فیلدی به نام number هست که در فیلد lastsum نگهداری میکنیم.
یک فیلد calculated هم داریم : runningsum=number+lastsum

151318

[mazoolagh]

اول جدول رو با دیتا تست پر میکنیم (1000 رکورد) که خودتون میتونین بنا به نیاز برای تست های مختلف که دارین مقدارش رو در کد پایین تغییر بدین و پرفورمنس رو چک کنین.

Sub Fill_Table1()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Table1")
Dim i, SUM As Long
SUM = 0
For i = 1 To 1000
rs.AddNew
rs!Number = RndX(0, 20) - 10
rs!LastSum = SUM
SUM = SUM + rs!Number
rs!RowNumber = i
rs.Update
Next i
End Sub


Public Function RndX(MIN As Long, Max As Long) As Long
Randomize
RndX = Int((Max - MIN + 1) * Rnd + MIN)
End Function

[mazoolagh]

جدول با دیتا نمونه

151319

[mazoolagh]

روی این جدول یک فرم از نوع continuous میسازیم

151320

[mazoolagh]

اگر دقت کنید در جدول یک فیلد به نام rownumber داریم که قرار هست شماره ردیف هر رکورد رو در خودش نگه داره.
این شماره ردیف باید با هر رکورد جدید بصورت خودکار یکی بهش اضافه بشه و با حذف رکورد، رکوردهای بعدی باید شمارشون اصلاح بشه.
در واقع به اینکار نیاز نداریم و یک فیلد autonumber هم برای کار ما کافی هست، ولی اینجا همزمان یک نکته برای یادگیری اضافه کردیم.

[mazoolagh]

حالا میریم سروقت کدنویسی؛
1- رخداد form insert رو برای تشخیص رکورد جدید استفاده میکنیم
خب برای رکورد اول باید rownumber=1 و lastsum=0 باشه
و برای رکوردهای بعدی باید اینها رو از روی اطلاعات آخرین رکورد محاسبه کنیم

Private Sub Form_AfterInsert()
If Recordset.RecordCount = 1 Then
RowNumber = 1
LastSum = 0
Else
With RecordsetClone
.MoveLast
.MovePrevious
RowNumber = !RowNumber + 1
LastSum = !RunningSum
End With
End If
End Sub

[mazoolagh]

2- وقتی مقدار number تغییر میکنه ، به جای اینکه تمام رکوردهای بعدی رو دوباره محاسبه کنیم کافی هست میزان تغییرات رو در اونها اعمال کنیم.
برای اینکار از رخداد after update فیلد استفاده میکنیم:

Private Sub Number_AfterUpdate()
Number = Nz(Number, 0)
If NewRecord Then Exit Sub
Dim D As Long
D = Number - Nz(Number.OldValue, 0)
If D = 0 Then Exit Sub
DoCmd.RunSQL ("UPDATE TABLE1 SET LastSum=LastSum+" & D & " WHERE RowNumber>" & RowNumber)
End Sub

[mazoolagh]

3- وقتی یک رکورد حذف میشه، در تمام رکوردهای بعدی باید rownumber و lastsum اصلاح بشن

Private Sub BTN_Delete_Click()
If NewRecord Then Exit Sub
If Dirty Then Dirty = False
Dim N, RN As Long
N = Number
RN = RowNumber
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.RunSQL ("UPDATE TABLE1 SET RowNumber=RowNumber-1 , LastSum=LastSum-" & N & " WHERE RowNumber>" & RN)
End Sub

[mazoolagh]

من همه حالتهای ممکن رو چک نکردم و ممکنه موردی از دستم در رفته باشه.
آزمایش کنین و خبر ش رو بدین.

[m_bargostavan_66]

من همه حالتهای ممکن رو چک نکردم و ممکنه موردی از دستم در رفته باشه.
آزمایش کنین و خبر ش رو بدین.
خیلی ممنون بابت راهنمایی و فایلتون
طبق فایل و کدهای شما تقریبا اون چیزی که میخواستم بود

1) من یه ستون که میانگین قیمت رو میخواستم ایجاد کردم و طبق کدهای شما درست دراومد ، ولی بعد از تغییر یک قیمت ، اون تغییر رو میانگین بقیه قیمتها در ادامه تاثیر نمیزاره ، ولی تو فیلد تعداد که فرمول شما بود درست اجرا میشه

2) مهمترین سوال من اینه مثلا ما تعداد کالاهای متفاوت داشته باشیم (مثلا 4 تا رکورد برای یه کالا و 2 تا رکورد برای یه کالای دیگه و ... ) و بخوایم اون اطلاعات جمع کل و میانگین فقط رو رکوردهای خودش اعمال بشه چیکار باید کرد ، این کدها و فرمی که شما زحمت کشیدید و گذاشتید فقط کل رکوردها رو محاسبه میکنه و نمیشه تفکیک کرد

تو فایل زیر که ادیت شده فایل خودتون هست گذاشتم اگه ببینید متوجه منظورم میشید

[mazoolagh]

بررسی میکنم و خبرش رو میدم.

time ...

[mazoolagh]

1- برنامه پیوست دقیقا پاسخ شما نبود، فقط یک ایده میداد که چگونه خواسته تون رو پیاده کنین.
تا حدی تونسته بودین اون رو پیاده کنین، ولی اسامی فیلدها چندان گویا نیست و تشخیص منطق محاسبات رو مشکل میکنه.

2- نوع معامله * چه فرقی با + داره؟ در محاسبات فقط + و - رو منظور کردین.

3- برای هر کد کالا و نوع معامله باید محاسبه جداگانه انجام بدین، بعبارت دیگه اینها هم باید در شرط های update گنجانده بشن.

4- یک چیزی شبیه به زیر برای فیلدهای جدول استفاده کنین:

id , autonumber
product id , number
trans id , text (+/-/*)
quantity , long
unit price , long
amount , calculated=iif(transid="-" , -quantity*unitprice , quantity*unitprice)
last total quantity , long (computed by code)
total quantity , calculated=iif(transid="-" , -quantity , quantity) + last total quantity
last total amount , long (computed by code)
total amount , calculated=amount+last total amount
last average amount , double/decimal , (computed by code)
average amount , calculated