עזרה - כיצד לעשות יתרה מצטברת בטופס אקסס

[חייםיודלביץ]

שלום וברכה

אני מעוניין לעשות בטופס אקסס בתצורת גליון נתונים דבר כזה

איך אפשר בכלל להתייחס באקסס ליתרת הפקד ברשומה הקודמת?

אשמל לעזרה ומידע
תודה

[OdedDvir]

@חייםיודלביץ למרבה הצער, בטופס אקסס אין פונקציה מובנית לסכומים מצטברים.
האלטרנטיבות הן:

אפשרות א: להשתמש בדוח במקום בטופס - בדוחות יש אפשרות להוספת חישוב סכומים מצטברים.
יתרונות:

• הכי קל לביצוע.
• נתונים עקביים: החישוב מבוצע בכל תצוגה מחדש.

חסרונות:

• הדוח לא ניתן לעריכה.
• מאוד לא יעיל מבחינת חישובים, במיוחד בטבלאות עם הרבה רשומות.

אפשרות ב: ליצור שאילתת קיבוץ במקור של הטופס ובה להוסיף שדה (מחושב) של סכום מצטבר - את הנוסחא לשדה הזה בונים על ידי פונקציית סיכום Sum על כל הרשומות בטבלה, מהרשומה הראשונה בעמודה עד הרשומה הנוכחית. יש צורך במיון של השדות לפי סדר קבוע, מומלץ לפי שדה מספור אוטומטי ID, או תאריך (פחות מומלץ)

יתרונות:

• עקיבות: חישוב מדויק של הסכום המצטבר, כי הוא מחושב בזמן אמת.

חסרונות:

• השאילתא לא ניתנת לעריכה (קריאה בלבד).
• מאד לא יעיל מבחינת חישובים, במיוחד בטבלאות עם הרבה רשומות.

אפשרות ג: לחשב את הסכומים המצטברים ולשמור בשדה נוסף - בטבלה הזו, על ידי שאילתא או קוד שרץ אוטומטית בכל פרק זמן.

יתרונות:

• הטופס שמתקבל ניתן לעריכה.
• יעילות בחישוב, כי הוא מבוצע רק במקרה של הוספת או מחיקת רשומה, ולא בכל פתיחה של הטופס.
• ניתן לייעל את החישוב יותר על ידי חישוב רק הרשומות הרלוונטיות, כלומר: במחיקה לחשב רק הרשומות שהושפעו מהמחיקה (מהרשומה שנמחקה והלאה), ובהוספה לחשב רק את השורה החדשה.

חסרונות:

• חוסר עקביות: במקרה של מחיקת (ביטול) רשומה כלשהיא, אם עדיין לא הרצת את החישוב לשדה מחדש, הסכום המצטבר לא מעודכן, ובתצוגה יציג את היתרה לפני המחיקה. כלומר עליך האחריות לשמור את הנתונים עקביים על ידי הרצת החישוב בכל פעם שנדרש.

אני נוהג להשתמש באפשרות השלישית.

[חייםיודלביץ]

@OdedDvir וואו איזה תשובה מפורטת ובהירה תודה רבה!

נראה שאכן כמו שכתבת האפשרות השלישית היא הכי טובה

אפשר לעשות אחרי מחיקה או אחרי שינוי נתון בפקד רענון לפקד המצטבר ואז זה פותר את הבעיה, נכון?

אשמח אם תוכל להסביר לי כיצד לבצע את האפשרות השלישית.

[OdedDvir]

@חייםיודלביץ אמר בעזרה - כיצד לעשות יתרה מצטברת בטופס אקסס:

אפשר לעשות אחרי מחיקה או אחרי שינוי נתון בפקד רענון לפקד המצטבר ואז זה פותר את הבעיה, נכון?

אם אתה מתכוון לאפשרות ב: נכון שצריך לרענן כמובן אחרי מחיקה או שינוי, אבל זה לא פותר את הבעיות שציינתי באפשרות ב (אין אפשרות עריכה, לא יעיל)

אשמח אם תוכל להסביר לי כיצד לבצע את האפשרות השלישית.

1. לשם פשטות, נניח שיש לך בטבלה MyTable את השדות הבאים: ID, Amount כאשר ID הוא מספור אוטומטי (וממילא נותן את סדר הרשומות הנכון) ו-Amount הוא סכום הפעולה ויכול להיות שלילי או חיובי. (במקרה שלך Amount=זכות פחות חובה)

2. תוסיף שדה חדש (מספר) בטבלה בשם RunningSum.

3. צור שאילתא חדשה בשם uqryUpdateAllRunningSum, עבור לתצוגת SQL ותדביק את הפקודה הבאה:

UPDATE MyTable SET MyTable.RunningSum = DSum("Amount","MyTable","ID<=" & [ID]);

שמור, והרץ את השאילתא.
שים לב שזהו פתרון בסיסי, שמחשב מחדש את כל הרשומות. כמו כן התעצלתי ליצור שאילתא מקוננת ולכן השתמשתי בפונקציה המובנית באקסס DSum שהיא פחות יעילה מפונקצית Sum של SQL.

אם נרצה לשפר את היעילות ולחשב רק מ-ID מסוים ואילך, צריך להוסיף פרמטר לשאילתא בשם prmStartID ולאתחל אותו ב-ID שממנו נרצה לחשב.
צור שאילתא שניה בשם uqryUpdateRunningSumFromStartID, והדבק את הפקודה הבאה:

PARAMETERS prmStartID Long;
UPDATE MyTable SET MyTable.RunningSum = DSum("Amount","MyTable","ID<=" & [ID])
WHERE (((MyTable.ID)>=[prmStartID]));

עכשיו כשתריץ את השאילתא היא תבקש את הפרמטר prmStartID שמציין את ערך המזהה שממנו יתחיל החישוב מחדש.
את השאילתא תריץ בכל שינוי ברשומות, ואת הפרמטר ניתן לקבוע בקוד לפני הריצה.

ניתן גם לעשות הכל על ידי קוד:

Public Sub UpdateAllRunningSum()
    Dim rs As DAO.Recordset
    Dim newRunningSum As Double
    
    newRunningSum = 0 'איפוס
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable ORDER BY ID", dbOpenDynaset, dbSeeChanges)
    With rs
        While Not .EOF
            newRunningSum = newRunningSum + !Amount 'חישוב הסכום החדש
            .Edit 'פתיחת הרשומה לעריכה
            !RunningSum = newRunningSum 'שמירת הסכום החדש
            .Update 'עדכון הרשומה
            .MoveNext
        Wend
    End With
    rs.Close
End Sub

כמובן שגם את הקוד הזה אפשר לשנות ולהעביר כפרמטר את StartID כדי לחשב רק משם.

[חייםיודלביץ]

@OdedDvir תודה על ההשקעה הרבה בהסברים!

אני רוצה שיהא בעמודה נפרדת "חובה" ובעמודה נפרדת "זכות" כמו בדוגמא בהודעה הראשונה
כרגע הדוגמא היא שאני צריך לכתוב האם הסכום הוא שלילי או חיובי לא הצלחתי להוסיף במשפט ה SQL שיחשב עמודה נפרדת.

תודה רבה רבה!

[OdedDvir]

@חייםיודלביץ הדרך הכי פשוטה היא ליצור שאילתת עזר על הטבלה הקיימת, ולהוסיף בה את השדה המחושב Amount.
לדוגמא אם הטבלה שלך נקראת: MyTable, ויש לה את השדות Zchut ו-Chova, צור שאילתא qryMyTableWithAmounts עם התחביר הבא:

SELECT MyTable.*, Nz([Zchut],0)-Nz([Chova],0) AS Amount FROM MyTable

עכשיו תחליף (בשאילתות) בקריאה לפונקציה DSum את "MyTable" בשאילתא שיצרת:

DSum("Amount","qryMyTableWithAmounts","ID<=" & [ID])

עריכה: בקוד אפשר לוותר על השאילתא ולחשב את ההפרש בשדות עצמן (בשורה 9)

            newRunningSum = newRunningSum + !Zchut - !Chova 'חישוב הסכום החדש

[חייםיודלביץ]

@OdedDvir תודה רבה! עובד מצוין!!