Re: Custom data type

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


Rick Brandt wrote:
> > eg ABC|Date|autonumber
> >
> > where "ABC" is a static value
> > where "date" is appended to ABC eg ABC20102005
> > where autonumber is an incrementing number that starts at "0" and
> > increments until the date is changed. After the date has changed the
> > number resets to "0" This number is appended to the previous
> > combination of ABC|Date.
>
> If IsNull(Me.IDNum) = True Then
> Me.IDNum = Nz(DMax("IDNum","TableName", "DateField = Date()"), 0) + 1
> End If

If this were the 'forms' group then that approach would be fine <g>.
Also, don't you agree that calculated values shouldn't normally be
stored in tables?

In the OP's spec, I read "until the date is changed" to refer to a
stored date value rather than the current *system* date. So, for the
tablesdbdesign group, here's my proposed design:

CREATE TABLE KeyDateTable (
key_date DATETIME
DEFAULT DATE()
NOT NULL UNIQUE)
;
CREATE TABLE TestTable (
key_int INTEGER IDENTITY(1,1) NOT NULL,
key_date DATETIME NOT NULL
REFERENCES KeyDateTable (key_date)
ON DELETE CASCADE
ON UPDATE CASCADE,
UNIQUE(key_date, key_int),
data_col VARCHAR(10) NOT NULL,
CONSTRAINT key_date_must_be_highest_available
CHECK(TestTable.key_date = (
SELECT MAX(KeyDateTable.key_date)
FROM KeyDateTable)
))
;

-- Test data:
INSERT INTO KeyDateTable VALUES (#2004-01-01#)
;
INSERT INTO TestTable (key_date, data_col)
VALUES (#2004-01-01#, 'One')
;
INSERT INTO TestTable (key_date, data_col)
VALUES (#2004-01-01#, 'Two')
;
-- the date is changed:
INSERT INTO KeyDateTable VALUES (#2005-01-01#)
;
-- try old date:
INSERT INTO TestTable (key_date, data_col)
VALUES (#2004-01-01#, 'Three')
;
-- 2004-01-01 fails to meet CHECK, try new date
INSERT INTO TestTable (key_date, data_col)
VALUES (#2005-01-01#, 'Three')
;
-- success!

Now the desired key values may be generated in a VIEW (a.k.a. Query
object), where calculated values normally belong:

CREATE VIEW Test (
key_value, data_value)
AS
SELECT 'ABC' &
FORMAT$(T1.key_date, 'ddmmyyyy')
& (
SELECT COUNT(*)
FROM TestTable
WHERE key_date = T1.key_date
AND key_int < T1.key_int
) AS key_value,
T1.data_col AS data_value
FROM TestTable AS T1
;

SELECT key_value, data_value FROM Test
;

.

Relevant Pages

  • Re: Deriving unique rows from historical data
    ... ON UPDATE CASCADE, ... REFERENCES Locations ... start_time DATETIME NOT NULL, ... Google how to code for this schema. ...
    (comp.databases.ms-sqlserver)
  • Re: Overriding derived values
    ... CREATE TABLE Discounts ... REFERENCES Players ... ON UPDATE CASCADE, ... start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, ...
    (comp.databases.theory)
  • Re: Modeling independent attributes on m:n links
    ... NOT NULL PRIMARY KEY, ... ON UPDATE CASCADE ... bookmark_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, ... -- this is the history what web sites were visited chronologically ...
    (comp.databases)