Table Creation

• Best-330874

  SSCrazy

  Points: 2350

  More actions

  May 3, 2007 at 2:02 pm

  #153566

  Hi. 

   

  Line #		FY 2004	FY 2005	FY 2006
  	Custodial services
  1561	Professional  services
  1562	Clerical services
  1563	Other salaries
  1564	Contracted services
  1565	Supplies
  1565	Other expenses
  	sub-total
  	Maintenance of Grounds
  1591	Professional  services
  1592	Clerical services
  1593	Other salaries
  1594	Contracted services
  1595	Supplies
  1596	Other expenses
  	sub-total
  	Extraordinary maintence
  1633	Insurance for employees
  1645	Legal expenses
  1646	Trade expenses
  	sub-total

   

   

  This is the excel sheet and according to this a web form is designed and for this i need to create a table

  The Line # that you see is predefined value in the database and the data under the custodial Services , Maintenance of Grounds etc are also the predefined values and the data in the three years are user entered data....

   

   

  Can u help me out with this

   

   

  thanks

• Mike Nuessler

  Right there with Babe

  Points: 788

  More actions

  May 3, 2007 at 2:45 pm

  #704214

  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

  ---

  In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)