Thank you Wendell for your reply. It is as I had feared.
Indexed View
I've tried this to no success (so far) but I wonder if it's because I'm not doing something right. The MS Access form says that the recordset for the view is not updateable. I don't know whether that's because I haven't got the view defined properly or whether it's because the view simply is not updateable
Here is the DDL for the tables, some reference values to give you an idea of what I'm looking at, and the indexed view that I've tried. Does this look right to you?
-----------------------------------------------------------------------------------------------------------------
-- Drop Everything
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[V_Information_Request]') AND OBJECTPROPERTY(id, 'IsView') = 1)
DROP VIEW [dbo].[V_Information_Request]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[Information_Request]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[Information_Request]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Type]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[R_Information_Request_Type]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Category]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[R_Information_Request_Category]
GO
-----------------------------------------------------------------------------------------------------------------
-- R_Information_Request_Category
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Category]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[R_Information_Request_Category]
GO
CREATE TABLE [dbo].[R_Information_Request_Category] (
[DB_Key] INT IDENTITY(1,1) CONSTRAINT [PK_R_Information_Request_Category] PRIMARY KEY CLUSTERED
,[Name] VARCHAR(100) NOT NULL
,[Definition] VARCHAR(256) NOT NULL
,[Effective_Date] DATETIME NOT NULL CONSTRAINT [DF_R_Information_Request_Category_Effective_Date] DEFAULT CONVERT(VARCHAR(10), GETDATE(), 101)
,[Sort_Order] NUMERIC(6,2) NULL
,[Obsolete_Date] DATETIME NULL
,[Comment] VARCHAR(256) NULL
,[Created_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_R_Information_Request_Category_Created_By] DEFAULT [dbo].[FN_Get_User_Name]()
,[Creation_Date_Time] DATETIME NOT NULL CONSTRAINT [DF_R_Information_Request_Category_Creation_Date_Time] DEFAULT GETDATE()
,[Last_Updated_By] VARCHAR(51) NULL
,[Last_Update_Date_Time] DATETIME NULL
,[RowVersion] ROWVERSION NOT NULL
,[Corporate_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_R_Information_Request_Category_Corporate_GUID] DEFAULT NEWID()
,CONSTRAINT [UIX_R_Information_Request_Category] UNIQUE ([Name], [Obsolete_Date])
,CONSTRAINT [CK_R_Information_Request_Category_Obsolete_Date_GE_Effective_Date] CHECK ([Obsolete_Date] >= [Effective_Date])
,CONSTRAINT [CK_R_Information_Request_Category_Last_Update_Date_Time_GE_Creation_Date_Time] CHECK ([Last_Update_Date_Time] >= [Creation_Date_Time])
)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Category_Update]') AND OBJECTPROPERTY(id, 'IsTrigger ') = 1)
DROP TRIGGER [dbo].[R_Information_Request_Category_Update]
GO
CREATE TRIGGER [dbo].[R_Information_Request_Category_Update] ON [dbo].[R_Information_Request_Category] FOR UPDATE AS
SET NOCOUNT ON
IF NOT UPDATE([Last_Updated_By])
UPDATE TBL SET
TBL.[Last_Updated_By] = [dbo].[FN_Get_User_Name]()
FROM [dbo].[R_Information_Request_Category] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
IF NOT UPDATE([Last_Update_Date_Time])
UPDATE TBL SET
TBL.[Last_Update_Date_Time] = GETDATE()
FROM [dbo].[R_Information_Request_Category] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
GO
-----------------------------------------------------------------------------------------------------------------
-- R_Information_Request_Type
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Type]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[R_Information_Request_Type]
GO
CREATE TABLE [dbo].[R_Information_Request_Type] (
[DB_Key] INT IDENTITY(1,1) CONSTRAINT [PK_R_Information_Request_Type] PRIMARY KEY CLUSTERED
,[Name] VARCHAR(100) NOT NULL
,[Definition] VARCHAR(256) NOT NULL
,[Effective_Date] DATETIME NOT NULL CONSTRAINT [DF_R_Information_Request_Type_Effective_Date] DEFAULT CONVERT(VARCHAR(10), GETDATE(), 101)
,[Sort_Order] NUMERIC(6,2) NULL
,[Obsolete_Date] DATETIME NULL
,[Comment] VARCHAR(256) NULL
,[Information_Request_Category_DB_Key] INT NOT NULL CONSTRAINT [FK_R_Information_Request_Category] REFERENCES [dbo].[R_Information_Request_Category] ([DB_Key])
,[Created_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_R_Information_Request_Type_Created_By] DEFAULT [dbo].[FN_Get_User_Name]()
,[Creation_Date_Time] DATETIME NOT NULL CONSTRAINT [DF_R_Information_Request_Type_Creation_Date_Time] DEFAULT GETDATE()
,[Last_Updated_By] VARCHAR(51) NULL
,[Last_Update_Date_Time] DATETIME NULL
,[RowVersion] ROWVERSION NOT NULL
,[Corporate_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_R_Information_Request_Type_Corporate_GUID] DEFAULT NEWID()
,CONSTRAINT [UIX_R_Information_Request_Type] UNIQUE ([Name], [Obsolete_Date])
,CONSTRAINT [CK_R_Information_Request_Type_Obsolete_Date_GE_Effective_Date] CHECK ([Obsolete_Date] >= [Effective_Date])
,CONSTRAINT [CK_R_Information_Request_Type_Last_Update_Date_Time_GE_Creation_Date_Time] CHECK ([Last_Update_Date_Time] >= [Creation_Date_Time])
)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[R_Information_Request_Type_Update]') AND OBJECTPROPERTY(id, 'IsTrigger ') = 1)
DROP TRIGGER [dbo].[R_Information_Request_Type_Update]
GO
CREATE TRIGGER [dbo].[R_Information_Request_Type_Update] ON [dbo].[R_Information_Request_Type] FOR UPDATE AS
SET NOCOUNT ON
IF NOT UPDATE([Last_Updated_By])
UPDATE TBL SET
TBL.[Last_Updated_By] = [dbo].[FN_Get_User_Name]()
FROM [dbo].[R_Information_Request_Type] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
IF NOT UPDATE([Last_Update_Date_Time])
UPDATE TBL SET
TBL.[Last_Update_Date_Time] = GETDATE()
FROM [dbo].[R_Information_Request_Type] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
GO
-----------------------------------------------------------------------------------------------------------------
-- Information_Request
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[Information_Request]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [dbo].[Information_Request]
GO
CREATE TABLE [dbo].[Information_Request] (
[DB_Key] INT IDENTITY(1,1) CONSTRAINT [PK_Information_Request] PRIMARY KEY CLUSTERED
,[Start_Date] DATETIME NOT NULL CONSTRAINT [DF_Information_Request_Start_Date] DEFAULT GETDATE()
,[End_Date] DATETIME NULL
,[Milestone_Dates_DB_Key] INT NOT NULL CONSTRAINT [FK_Information_Request_to_Milestone_Dates] REFERENCES [dbo].[Milestone_Dates] ([DB_Key])
,[Information_Request_Type_DB_Key] INT NOT NULL CONSTRAINT [FK_Information_Request_to_R_Information_Request_Type] REFERENCES [dbo].[R_Information_Request_Type] ([DB_Key])
,[Created_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Information_Request_Created_By] DEFAULT [dbo].[FN_Get_User_Name]()
,[Creation_Date_Time] DATETIME NOT NULL CONSTRAINT [DF_Information_Request_Creation_Date_Time] DEFAULT GETDATE()
,[Last_Updated_By] VARCHAR(51) NULL
,[Last_Update_Date_Time] DATETIME NULL
,[RowVersion] ROWVERSION NOT NULL
,[Corporate_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_Information_Request_Corporate_GUID] DEFAULT NEWID()
,CONSTRAINT [CK_Information_Request_Last_Update_Date_Time_GE_Creation_Date_Time] CHECK ([Last_Update_Date_Time] >= [Creation_Date_Time])
)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[Information_Request_Update]') AND OBJECTPROPERTY(id, 'IsTrigger ') = 1)
DROP TRIGGER [dbo].[Information_Request_Update]
GO
CREATE TRIGGER [dbo].[Information_Request_Update] ON [dbo].[Information_Request] FOR UPDATE AS
SET NOCOUNT ON
IF NOT UPDATE([Last_Updated_By])
UPDATE TBL SET
TBL.[Last_Updated_By] = [dbo].[FN_Get_User_Name]()
FROM [dbo].[Information_Request] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
IF NOT UPDATE([Last_Update_Date_Time])
UPDATE TBL SET
TBL.[Last_Update_Date_Time] = GETDATE()
FROM [dbo].[Information_Request] TBL
JOIN inserted INS
ON TBL.[DB_Key] = INS.[DB_Key]
GO
-----------------------------------------------------------------------------------------------------------------
-- Indexed view
-----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('[dbo].[V_Information_Request]') AND OBJECTPROPERTY(id, 'IsView') = 1)
DROP VIEW [dbo].[V_Information_Request]
GO
CREATE VIEW [dbo].[V_Information_Request] WITH SCHEMABINDING AS (
SELECT
IR.[DB_Key]
,IR.[Start_Date]
,IR.[End_Date]
,IR.[Milestone_Dates_DB_Key]
,IR.[Information_Request_Type_DB_Key]
,IR.[Created_By]
,IR.[Creation_Date_Time]
,IR.[Last_Updated_By]
,IR.[Last_Update_Date_Time]
,IR.[RowVersion]
,IR.[Corporate_GUID]
,IRT.[Name] AS [IR_Type_Name]
,IRT.[Information_Request_Category_DB_Key] AS [IRC_DB_Key]
,IRC.[Name] AS [IR_Category_Name]
FROM [dbo].[Information_Request] IR
JOIN [dbo].[R_Information_Request_Type] IRT
ON IRT.[DB_Key] = IR.[Information_Request_Type_DB_Key]
JOIN [dbo].[R_Information_Request_Category] IRC
ON IRC.[DB_Key] = IRT.[Information_Request_Category_DB_Key]
)
GO
CREATE UNIQUE CLUSTERED INDEX IX_V_Information_Request ON [dbo].[V_Information_Request] ([DB_Key])
--------------------------------------------------------------------------------------------
-- Load information request Categories
--------------------------------------------------------------------------------------------
INSERT INTO [dbo].[R_Information_Request_Category] ([Name], [Created_By], [Definition], [Sort_Order]) VALUES ('Internal Referral', 'Initial Load', 'Referral to a business unit within the AER', 1)
INSERT INTO [dbo].[R_Information_Request_Category] ([Name], [Created_By], [Definition], [Sort_Order]) VALUES ('External Referral', 'Initial Load', 'Referral to a regulatory authority outside of the AER', 2)
INSERT INTO [dbo].[R_Information_Request_Category] ([Name], [Created_By], [Definition], [Sort_Order]) VALUES ('Applicant', 'Initial Load', 'Referral back to the applicant', 3)
INSERT INTO [dbo].[R_Information_Request_Category] ([Name], [Created_By], [Definition], [Sort_Order]) VALUES ('Hold', 'Initial Load', 'A hold on the Application for some reason not included in any other category', 4)
--------------------------------------------------------------------------------------------
-- Load information request Types
--------------------------------------------------------------------------------------------
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('SIR', 'Initial Load', 'Supplemental Information Request', 1, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Applicant'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Draft Approval Review', 'Initial Load', 'Supplemental Information Request', 2, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Applicant'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Geology Referral', 'Initial Load', 'A Referral to the geology business unit for geological input/advice regarding the application', 3, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Hydrology Referral', 'Initial Load', 'A Referral to the Hydrology business unit for hydrological input/advice regarding the application', 4, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Engineering Referral', 'Initial Load', 'A Referral to the Enginerring business unit for engineering input/advice regarding the application', 5, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Law Referral', 'Initial Load', 'A Referral to the Law business unit for legal input/advice regarding the application', 6, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('EIA Referral', 'Initial Load', 'A Referral to the EIA business unit for environmental impact assessment input/advice regarding the application', 7, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('ESRD', 'Initial Load', 'A Referral to Alberta Environment and Sustainable Resources for input/advice regarding the application', 8, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Internal Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Ducks Unlimited', 'Initial Load', 'A Referral to the Ducks Unlimited for input/advice regarding the application', 9, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'External Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Fisheries and Oceans', 'Initial Load', 'A Referral to the Fisheries and Oceans for input/advice regarding the application', 11, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'External Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('Related application', 'Initial Load', 'Being held becuase of a dependency on or relationship to another application', 12, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'Hold'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('FNC adequacy confirmation', 'Initial Load', 'Awaiting confirmation that FNC is adequate', 13, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'External Referral'))
INSERT INTO R_Information_Request_Type ([Name], [Created_By], [Definition], [Sort_Order], [Information_Request_Category_DB_Key]) VALUES ('ACO', 'Initial Load', 'A Referral to the Aboriginal Consultation Office for input/advice regarding the application', 10, (SELECT DB_Key FROM [dbo].[R_Information_Request_Category] WHERE [NAME] = 'External Referral'))
Denormalization
Yes, I have experimented with it and this would be my fallback approach. Still use the two reference tables for defining Request Categories and Request Types but define the rowsource for the in the Information Request which refers to the Information Request Type to display both columns (Request Category and Request Type). My only hesitation is that this would display the full list of Information Request Types which I fear could grow quite large (40-60 rows) over time which is a bit unwieldy for a dropdown list, hence the desire to have cascading combo boxes.
As I'm writing this it occurs to me that if the Information Request Table contained a FK to Request Category AS WELL AS a FK to Request Type I could probably use the FK the user selects in Request Category to filter the rowsource for Request Type. Is that what you were thinking of by denormalization? I will try this and report back.
Any advice you could provide would be much appreciated.