Re: Sum a column every time a record is created
- From: "onedaywhen" <jamiecollins@xxxxxxxxxx>
- Date: 19 Jul 2006 03:45:10 -0700
ecnalch wrote:
I want to sum the Quantity field each time I create a record. I am defaulting
a 1 in the Quantity field
The DEFAULT must be a scalar (i.e. a single value) and either a literal
value (e.g. not a calculation) or a niladic function (e.g. DATE()).
Consider this table
CREATE TABLE Test (
amount INTEGER DEFAULT 0 NOT NULL,
CHECK (amount >= 0),
total_amount_when_added INTEGER DEFAULT 0 NOT NULL,
CHECK (total_amount_when_added >= 0)
);
To determine the value for the total_amount_when_added column on
insert, you could create a SQL procedure (parameterized query):
CREATE PROCEDURE TestProc (
new_amount INTEGER = 1
) AS
INSERT INTO Test (amount, total_amount_when_added)
SELECT new_amount AS amount,
IIF(SUM(amount) IS NULL, 0, SUM(amount))
+ IIF(new_amount IS NULL, 1, new_amount)
AS total_amount_when_added
FROM Test;
You would then remove INSERT permissions from the base table and grant
them to the procedure. This way, you would ensure that all inserts to
the table go via your procedure i.e. the value in the
total_amount_when_added column is under the control of the database
designer, not the front end code.
Jamie.
--
.
- Prev by Date: Re: Calculating the league age based on an end date
- Next by Date: Check number of records in subform according to listbox
- Previous by thread: Re: Sum a column every time a record is created
- Next by thread: Check number of records in subform according to listbox
- Index(es):
Relevant Pages
|
