How to not let sum of child field not exceed parent field in MS Access

Question

I have MS Access database file with following tables

Table In

Id | Quantity
------------------------
1  | 8

Table Out

Id | Parent    | Quantity
-------------------------
1  | 1         | 2
2  | 1         | 5

Table In is a parent table and Table Out is related table.

I need following constraint to be enforced (pseudo formula):

In.Quantity >= Sum(Out.Quantity)

That is: Sum of outgoing quantities cannot be greater than incoming quantity

I am developing a Winforms application. The user will make changes (Insert, Update and delete) to both In and Out

Rethink your db model. Usually you have one table with stock movements (out is negative). Then check at where the user edits, inserts data that inputs are valid. If you would use a database with triggers (like SQL-Server; ...), that could be made at database level. — ComputerVersteher, Aug 09 '19 at 13:36
Note that Access supports triggers (in the form of data macros) and cross-table check constraints just fine. While there are tasks that require SQL server's better trigger capability, validating input by comparing to a different table isn't one of them. It looks, though, like the OP wants a C# solution — Erik A, Aug 09 '19 at 13:48
@Erik A, I want a solution at database level for Winform (C#) application — DotNet Developer, Aug 09 '19 at 13:53
@ErikA Can we rely on data-macro triggers? I don't rely on any access backend! That's why I always suggest using a different database. Input should be validated to same table(in/out in one table) or don't you agree on that? — ComputerVersteher, Aug 09 '19 at 13:54
@ComputerVersteher I certainly agree that SQL server as a back end is more reliable than Access as a back end. There are situations one chooses Access anyway, mainly for cost/ease of use. I prefer to rely on constraints over data macros for validation. In/out in one table depends on the exact situation but is generally preferable. However, if you have a clear 1-many relationship with in-out, it might make sense to store them separately. — Erik A, Aug 09 '19 at 14:19

score 2 · Accepted Answer · edited Aug 09 '19 at 14:47

You can use a CHECK constraint to validate the condition.

ALTER TABLE Out 
ADD CONSTRAINT NoNegativeTotal
CHECK(
    NOT EXISTS(
        SELECT 1
        FROM In
        INNER JOIN Out ON In.Id = Out.Id
        GROUP BY In.Id, In.Quantity
        HAVING SUM(Out.Quantity) > In.Quantity
    )
)

You might want to add the same constraint to the In table, since if someone changes the In.Quantity field to violate the constraint, that'll lock down all editing in the Out table except edits that make the constraint valid again.

Check constraints need to be executed either in ANSI 92 compatible mode, or using ADO (e.g. using CurrentProject.Connection.Execute). More details here.

score 0 · Answer 2 · answered Aug 09 '19 at 12:47

0

Use the BeforeUpdate event of the subform to compare the sum of field Quantity with Me.Parent!Quantity.Value.

If exceeding, set Cancel = True and pop a message.

answered Aug 09 '19 at 12:47

Gustav

53,498
7
29
55

How to not let sum of child field not exceed parent field in MS Access

2 Answers2