Access2000: Creating a calculated index

[Arvi Laanemets]

Hi


In some table I have primary key (ID) structured as CNNNNNNNNNNN. The first
letter of key determines the group, the item is belonging to. All groups,
along with their names, are listed in another table. I'm trying to
estabilish the relationship between two tables, but I haven't found a way to
define an index for first table, calculated as LEFT(ID,1). Is there a way
to do this, or I have to split primary key field ID, and use an composed
index as primary key?


Thanks in advance!

 

[Baz]

Hi


In some table I have primary key (ID) structured as CNNNNNNNNNNN. The first
letter of key determines the group, the item is belonging to. All groups,
along with their names, are listed in another table. I'm trying to
estabilish the relationship between two tables, but I haven't found a way to
define an index for first table, calculated as LEFT(ID,1). Is there a way
to do this, or I have to split primary key field ID, and use an composed
index as primary key?


Thanks in advance!

Split it into two fields, it will be much better in the long run.

 

[peregenem]

In some table I have primary key (ID) structured as CNNNNNNNNNNN. The first
letter of key determines the group, the item is belonging to. All groups,
along with their names, are listed in another table. I'm trying to
estabilish the relationship between two tables, but I haven't found a way to
define an index for first table, calculated as LEFT(ID,1).

You can use a CHECK constraint to enforce this business rule e.g.

CREATE TABLE Groups (
group_ID CHAR(1) NOT NULL PRIMARY KEY,
CHECK (group_ID LIKE '[A-Z]')
)
;
INSERT INTO Groups VALUES ('A')
;
CREATE TABLE Test (
ID CHAR(12) NOT NULL PRIMARY KEY,
CHECK (ID LIKE
'[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
CHECK (1 <= (
SELECT COUNT(*)
FROM Groups
WHERE Groups.group_ID = LEFT$(Test.ID, 1)
))
)
;
INSERT INTO Test (ID) VALUES ('A12345678901')
;
--success: 'A' exists in Groups

INSERT INTO Test (ID) VALUES ('D12345678901')
;
--fails: 'D' does not exist in Groups