Adding calculated total against an ID

'Wish the game would provide a running total per character, but it doesn't.'

Computing balances (running sums) requires the summation of all amounts up to and including the date/time of the current transaction.  This is very common with currency amounts, but the methodology applies equally to any context, so can be done in the context of a game. 

You might like to take a look at Balances.zip in my Dropbox public databases folder at: 

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

This little demo file gives examples of a number of queries used as a form's RecordSource.  The most efficient method is a join of two instances of the table, but that returns a non-updatable recordset, so would be unsuitable for a form in which data are to be inserted or edited.  To return an updatable recordset the VBA DSum function can be called in an expression in a computed column in a query.  The following is an example from the demo:

SELECT
    TransactionID,
    CustomerID,
    TransactionDate,
    TransactionAmount,
    CCur (    
        DSum (
            "TransactionAmount",
            "Transactions",
            "CustomerID = " & [CustomerID] &
            " OR TransactionDate <> #" & Format([TransactionDate], "yyyy-mm-dd") & "#)   
            AND TransactionDate <= #" & Format([TransactionDate], "yyyy-mm-dd") & "#"
        )
    ) AS Balance
FROM
    Transactions
ORDER BY
    CustomerID,
    TransactionDate 
    TransactionID;

In this case the query allows for multiple transactions on the same day, and brings the primary key TransactionID column into play as the tie breaker. If the TransactionDate column were to be indexed uniquely (no duplicates), and thus include distinct date/time values, however, this would be unnecessary, and the query could be simplified as follows:

SELECT
    TransactionID,
    CustomerID,
    TransactionDate,
    TransactionAmount,    
        DSum (
            "TransactionAmount",
            "Transactions",
            "CustomerID = " & [CustomerID] &
            " AND TransactionDate <= #" 
            & Format([TransactionDate], "yyyy-mm-dd hh:nn:ss") & "#"
        ) AS Balance
FROM
    Transactions
ORDER BY
    CustomerID,
    TransactionDate;

To insert the current date/time value into a column of date/time data type call the Now() function in the AfterUpdate event procedure of the control bound to the column in which the points are entered, e.g.

Me.[NameOfDateTimeControlGoesHere] = Now()

The control bound to the date/time column can be hidden by setting its Visible property to False (No) if you wish.

'I'm looking for a simple way to add the points to a character, and from that, calculate and display a total on that character's form entry.'

Returning to your original post, the first thing to take on board is that there should be no Total column in either the Character or Earnings tables. To avoid possible update anomalies the total points earned by a character should be computed from the Won values in the Earnings table, so in the Character form you'd simply need an unbound text box control with a ControlSource property of:

=DSum("Won", "Earnings", "CharacterID = " & [CharacterID])

The Earnings form can be a subform in a form based on the Characters table, and linked to the parent form on CharacterID. Alternatively the Earnings form can be stand-alone, and include an unbound combo box in its header to filter the form to a selected character. Either way you'd then need an unbound text box control in the Footer or Header section, with a ControlSource property of:

=Sum([Won])

If you want to return a running balance in each row in the Earnings form you can add an unbound text box control to the detail section, with a ControlSource property of:

=DSum("Won", "Earnings", "CharacterID = " & [CharacterID] & " And (WinID <= " & [WinID] &  " OR WinDate <> #" & Format([WinDate], "yyyy-mm-dd") & "#) AND WinDate <= #" & Format([WinDate], "yyyy-mm-dd") & "#")

Note how in the above expression the primary key WinID column is used as a tie breaker where there are multiple rows for the same character on a single day. PS: Note that I've changed the name of your Date column to WinDate. Date is the name of a built in function in Access and, as such, is a reserved keyword, so should not be used as an object name.

Hello Silke Juppenlatz

Welcome to Microsoft Q&A forum.

Thank you for reaching out about setting up a simple system in Microsoft Access to track points (or "wins") for characters in your game. I understand you're new to Access and want an efficient way to add points, automatically record the date/time, and display a running total on the character's form, without storing the total in the table to avoid data issues. I'll walk you through this step by step in a straightforward manner. We'll use your existing Earnings table (with some minor tweaks) and assume you have a basic Characters table (with CharacterID as an AutoNumber primary key and fields like Name).

Recommended Table Adjustments

To make things smoother:

• Earnings Table Fields:
  • WinID: AutoNumber (primary key).
  • CharacterID: Number (foreign key linking to Characters).
  • Won: Number (points earned per win).
  • WinDate: Date/Time (renamed from "Date" to avoid conflicts; set Default Value to =Now() in Design View—this auto-fills the current date/time when adding a record).
• Remove the "Total" field, we'll calculate it dynamically on the form to keep data accurate.

Step 1: Establish Relationships

1. Go to Database Tools > Relationships.
2. Add both Characters and Earnings tables.
3. Drag CharacterID from Characters to Earnings' CharacterID.
4. Enable "Enforce Referential Integrity" and create the relationship.

This ensures wins are properly linked to characters.

Step 2: Create the Character Form

1. Go to Create > Form Wizard.
2. Select the Characters table and add desired fields (e.g., CharacterID, Name).
3. Choose a layout (e.g., Columnar), name it "CharacterForm," and finish.

Step 3: Add a Subform for Wins

This allows you to enter wins directly on the CharacterForm, with auto date/time.

1. Open CharacterForm in Design View (right-click the form > Design View).
2. Under Design tab > Controls, select Subform/Subreport and drag a box onto the form (e.g., below other fields).
3. In the wizard:
   • Use existing table: Earnings.
   • Add fields: CharacterID, Won, WinDate (hide CharacterID later if desired).
   • Link on CharacterID (auto-suggested).
   • Name it "EarningsSubform."
4. In subform properties (right-click subform > Properties):
   • Data tab: Set Default View to Datasheet.
   • Format tab: Hide CharacterID column if unwanted.
5. Test in Form View: Select a character, enter points in "Won," hit Enter—WinDate auto-fills, and the record saves.

Step 4: Display the Total on the Form

1. In CharacterForm Design View, add a Text Box (Design > Controls).
2. Label it "Total Earned."
3. In properties (Data tab), set Control Source to: =DSum("Won", "Earnings", "CharacterID=" & [CharacterID]).
4. Test: Add wins via subform; the total updates (refresh with F5 if needed).

Additional Tips:

• Auto Date Issue: If WinDate doesn't fill, confirm its Default Value in Earnings table Design View.
• Character Lookup: In Earnings (or subform), change CharacterID to a Combo Box: Row Source = SELECT CharacterID, Name FROM Characters; Bound Column = 1 (select by name, store ID).
• No Subform Preference? Use an unbound "Won" field on the main form with VBA (After Update event): text

    CurrentDb.Execute "INSERT INTO Earnings (CharacterID, Won, WinDate) VALUES (" & Me.CharacterID & ", " & Me.Won & ", Now())"
  

• Always back up your database before tweaking, better safe than sorry!

This setup should save you time and keep things organized. If you have any additional concern, feel free to comment below. I be more than happy to assist.  

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.