asked on

Create SQL Results table

I am trying to add some new functionality to an existing database, and I am not sure how to proceed. I have an Access front end to a SQL 2005 database. I am attempting to add the abilitiy to run a series of calculations on an existing table and then create a results table that is named something like tbCalculations01_09262013, tbCalculations02_09262013, tbCalculations03_09272013, etc.

The table will have the following schema:
CREATE TABLE [dbo].[tbCalculations01_09262013](
      [OBJECTID] [int] IDENTITY(1,1) NOT NULL,
      [SDEID] [nvarchar](18) NULL,
      [CONDCAT01] [int] NULL,
      [CONDCAT02] [int] NULL,
      [CONDCAT03] [int] NULL,
      [CONDCAT04] [int] NULL,
      [CONDCAT05] [int] NULL,
      [CONDCAT06] [int] NULL,
      [CONDCAT07] [int] NULL,
      [CONDCAT08] [int] NULL,
      [CONDCAT09] [int] NULL,
      [CONDCAT10] [int] NULL,
      [CALCBY] [nvarchar](20) NULL,
      [CALCDATE] [datetime] NULL,
      [VALIDFROM] [datetime] NULL,
      [VALIDTO] [datetime] NULL,
CONSTRAINT [PK_tblData_WaterMainIndex] PRIMARY KEY CLUSTERED

I'm thinking that I should create the table, append the SDEID values and then perform the calculations. Ideally, I would like for the user to be able to be able to execute this from the Access front end.

Is this possible?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aaron Greene

ASKER

That is a good idea, and will probably make reporting the data a simpler process. The calculations are pretty simple. For example, I have a function that returns a rating of the asset based on its age. For example an asset that is 50 years old will get a rating of 10. I have views that show these results, so I really need to be able to append a view to the table.

SOLUTION

PatHartman