[AccessD] Need some help

Hi Charlotte

Sorry for the confusion. I should have written tinyint for Byte:

         [DAY_OF_WEEK] [varchar](50) NULL: smallint or tinyint?
         [DAY_MONTH_NO] [int] NULL: smallint or tinyint?
         [QUARTER_NO] [int] NULL: tinyint?
         [HOLIDAY_FLG] [varchar](50) NULL: bit?
         [WEEKDAY_FLG] [varchar](50) NULL: bit?
         [LAST_DAY_IN_MONTH_FLG] [varchar](50) NULL: bit?



> Varchar is a string -  i.e., "Monday"

[DAY_OF_WEEK], I guess, is the weekday number. If not, the field would be named 
  [WEEKDAY_TXT] [varchar](15) NULL 
like 
  [MONTH_TXT] [varchar](15) NULL,

> "Y", "N", "09"

But why? 

>  Int is a SmallInt

int is a VB Long and smallint is a VB Integer. Neither of these are needed to hold a dayofmonth number or a weekday number not to say a quarter number.

/gustav


>>> cfoust at infostatsystems.com 26-09-2008 23:57 >>>
Varchar is a string -  i.e., "Monday" , "Y", "N", "09", etc.  Int is a
SmallInt

You don't use bytes in this kind of table and do don't use bits,
although you could use bits in flag fields, except you would have to
deal with the tri-state issue.  

Charlotte Foust


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, September 26, 2008 2:18 PM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Need some help

Hi Robert

Some strange data types here? For example:

         [DAY_OF_WEEK] [varchar](50) NULL: SmallInt or Byte?
         [DAY_MONTH_NO] [int] NULL: SmallInt or Byte?
         [QUARTER_NO] [int] NULL: Byte?
         [HOLIDAY_FLG] [varchar](50) NULL: Bit?
         [WEEKDAY_FLG] [varchar](50) NULL: Bit?
         [LAST_DAY_IN_MONTH_FLG] [varchar](50) NULL: Bit?

/gustav

>>> robert at webedb.com 26-09-2008 19:40 >>>
Steve,

What you need to do is use a date dimension table. Something like this:

CREATE TABLE [dbo].[tblDateDimension](
         [DATE_ID] [datetime] NULL,
         [DAY_OF_WEEK] [varchar](50) NULL,
         [DAY_MONTH_NO] [int] NULL,
         [DAY_MONTH_TXT] [varchar](12) NULL,
         [DAY_YEAR_NO] [smallint] NULL,
         [DAY_OVERALL_NO] [int] NULL,
         [WEEK_YEAR_NO] [int] NULL,
         [WEEK_OVERALL_NO] [int] NULL,
         [MONTH_NO] [int] NULL,
         [MONTH_TXT] [varchar](15) NULL,
         [MONTH_OVERALL_NO] [int] NULL,
         [QUARTER_NO] [int] NULL,
         [FISCAL_PERIOD] [varchar](50) NULL,
         [HOLIDAY_FLG] [varchar](50) NULL,
         [WEEKDAY_FLG] [varchar](50) NULL,
         [LAST_DAY_IN_MONTH_FLG] [varchar](50) NULL,
         [YEAR_NO] [smallint] NULL,
         [CREATION_DATE] [datetime] NULL,
         [APP_LOGIN_CREATION] [varchar](50) NULL,
         [MODIFICATION_DATE] [datetime] NULL,
         [APP_LOGIN_MODIFICATION] [varchar](50) NULL
) ON [PRIMARY]