May 3, 2007 at 2:45 pm
You could use a single table for this, but where's the normalized relational fun in that? This would make grouping and subtotals a little easier and more scalable in the long run I would imagine. Hope this helps somewhat.
Create Table tblAccountType(
AccountTypeId int Identity(1,1) Not Null,
AccountTypeName varchar(100) Not Null
)
-- Add Indexes
Go
Create Table tblAccount(
AccountId int Not Null,
AccountTypeId int Not Null, -- Add FK to tblAccountType.AccountTypeId
AccountNo int Not Null,
AccountDesc varchar(100) Not Null
)
-- Add indexes
Go
Create Table tblMaintenance(
MaintenanceId int Identity(1,1) Not Null,
AccountId int Not Null, --Add FK to tblAccount.AccountId
FY2004 decimal(9,2) Null, -- adjust numerics as required
FY2005 decimal(9,2) Null, -- ditto
FY2006 decimal(9,2) Null, -- ditto^2
)
-- Add indexes
Go
Declare @id int
-- Add the Custodial Services accounts
Insert Into tblAccountType(AccountTypeName) Select 'Custodial Services'
Select @id = scope_identity() -- use this for Account insertions below
Insert Into tblAccount(AccountTypeId, AccountNo, AccountDesc) Select @id,1561,'Professional Services'
/* ... etc (do for all Custodial services) ... */
Insert Into tblAccount(AccountTypeId, AccountNo, AccountDesc) Select @id,1565,'Other Expenses'
-- Add the Maintenance of Grounds accounts
Insert into tblAccountType(AccountTypeName) Select 'Maintenance of Grounds'
Select @id = scope_identity() -- use this for Account insertions below
Insert Into tblAccount(AccountTypeId, AccountNo, AccountDesc) Select @id,1591,'Professional Services'
/* ETC */
-- Add Extraordinary Maintenance same as above two --
-- Once this is done then create a maintenance record for each account
Insert Into tblMaintenance(AccountId) Select AccountId From tblAccount
-- Then the interface can be used to enter in appropriate FY2004,FY2005,FY2006 values
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply