Search code examples
sqlms-access-2010data-entry

MS Access data validation: total of child records cannot exceed parent value

In the last months I've created a very simple access application to keep track of expenses in the company I work for, (just to be clear: it is a REALLY simple database, but it gets the job done and my colleagues are quite happy to use it instead of the old PenAndPaper method they were used to, anyway...) one of the feature my application has is that it allocates every expense to one or more cost center (for example, if we buy 1000€ of material, we register the expense and then we can register that we used 600€ in Cost center A and 400€ in cost center B).

I've got 3 tables (I will write only the tables and fields that are relevant for the problem at hand):

Expenses(idExpense, cost)

Centers(idCenter,canterName)

Allocation(idAllocation,idExpense,idCenter,allocatedCost)

in the "Expenses" table the field cost is what has been spent, , while the Allocation table establish a many-to-many relationship between Centers and Expenses. So, in the examble above we will have:

Center
IDCenter name
1        Center A
2        Center B

Expenses
IDExpenses cost
1          1000

Allocations
IDAllocations IDCenter IDExpense AllocatedCost
1             1        1         600
2             2        1         400

Now the problem is that I don't know how to validate the data in the AllocatedCost Field: suppose I add another expense

Expenses
IDExpenses cost
1          1000
2           100

nothing prevents the user to allocate it like this:

Allocations
IDAllocations IDCenter IDExpense AllocatedCost
1             1        1         600
2             2        1         400
3             1        2          60
4             2        2          50

In this case I end up with an invalid entry 110€ are allocated for an expense that is only of 1000! I need to prevent this in the data entry phase, since are most likely typing errors but I have no idea about how to do it. All I've managed to do is to prevent a single allocation to exceed the expense's amount and I've written a simple query that finds this cases, so I can check them manually, but I would like the form to check it when the data are fed to the DB.

Solution

  • Since you are using Access 2010 you can have a Before Change data macro perform the validation. To do that, create a saved query named [TotalAllocatedCostByIDExpense] in Access

    SELECT Allocations.IDExpense, Sum(Allocations.AllocatedCost) AS SumOfAllocatedCost
FROM Allocations
GROUP BY Allocations.IDExpense;

    and then use the following Before Change data macro on the [Allocations] table

    BeforeChange.png