Проверка данных MS Access: общее количество дочерних записей не может превышать родительское значение

Question

В последние месяцы я создал очень простое приложение для доступа, чтобы отслеживать расходы в компании, в которой я работаю (просто для ясности: это ДЕЙСТВИТЕЛЬНО простая база данных, но она выполняет свою работу, и мои коллеги очень довольны чтобы использовать его вместо старого метода PenAndPaper, к которому они привыкли, в любом случае...) одна из функций, которыми обладает мое приложение, заключается в том, что оно распределяет все расходы на один или несколько центров затрат (например, если мы покупаем 1000€ материала, мы регистрируем расходы и затем можем зарегистрировать, что мы использовали 600€ в МВЗ A и 400€ в МВЗ B).

У меня есть 3 таблицы (я напишу только те таблицы и поля, которые имеют отношение к рассматриваемой проблеме):

Расходы (idExpense, стоимость)

Центры (idCenter, canterName)

Распределение (idAllocation, idExpense, idCenter, allocCost)

в таблице "Расходы" полевая стоимость - это то, что было потрачено, в то время как таблица "Распределение" устанавливает отношение "многие ко многим" между центрами и расходами. Итак, в приведенном выше примере мы будем иметь:

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

Теперь проблема в том, что я не знаю, как проверить данные в поле AllocatedCost: предположим, я добавляю еще один расход

Expenses
IDExpenses cost
1          1000
2           100

ничто не мешает пользователю выделить его так:

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

В этом случае я получаю неправильную запись 110€ выделяются на расходы, которые только 1000! Мне нужно предотвратить это на этапе ввода данных, так как, скорее всего, это ошибки ввода, но я понятия не имею, как это сделать. Все, что мне удалось сделать, - это не допустить, чтобы одно выделение превысило сумму расходов, и я написал простой запрос, который находит эти случаи, поэтому я могу проверить их вручную, но я хотел бы, чтобы форма проверяла их, когда данные подаются в БД.

Answer 1

Так как вы используете Access 2010, вы можете сделать так, чтобы макрос данных Перед изменением выполнял проверку. Для этого создайте сохраненный запрос с именем [TotalAllocatedCostByIDExpense] в Access

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

и затем используйте следующий макрос данных перед изменением в таблице [Allocations]