[AccessD] Need some help

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]

GO

You join this to the date and you simply pass in the week
of the year and year, or the month and the year to get the
data.

Robert

At 12:00 PM 9/26/2008, you wrote:
>Date: Thu, 25 Sep 2008 13:32:37 -0500
>From: "Steve Turner" <sturner at mseco.com>
>Subject: [AccessD] Need some help.
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID:
>         <31B7F3CC8CC4454F87FCBEFF82B618920227B0B8 at PE2850-03.mseco.com>
>Content-Type: text/plain; charset="us-ascii"
>
>
>
>Hello List,
>
>I am using A2k with SQL Express, Excel, and other linked tables. The
>main DB is in SQL Express. We have a custom timesheet program that puts
>Time, Charges, Job numbers worked on in it. I used to export data to
>Lotus 2.1 using A2k and calculate cost for each Job to make entries to
>the GL. I have now written Query's to extract the data, calculate the
>cost and export to Excel so I can import to the GL. Calculations are
>done by the week because of Salaried Costs and the hours worked causes
>the Cost to change each week. I have a Query that sums the week by Job
>based on entered dates. I made a copy as a Make Table Query and a Copy
>to be an Append Query that writes the 4 or 5 weeks to a table to use
>another query to summarize by Job for the Month. I also have a Query
>that summarizes the data to 10 profit centers 0-9 as negative amounts to
>offset the GL.
>
>
>
>I know that there must be an easy way to use VBA code to automate the
>process but not quite sure how to start it as my coding ability is
>limited. We also have a Variable table built with the month start and
>month end date to run many other reports. We have a Form built with
>buttons to update the Variables table and run many reports from the
>database here. I would like to add a button that would run the code to
>build the GL Cost data from here. It will write the GL Queries to an
>Excel file.
>
>
>
>Simply I need to read Var table for month start then run Make Table
>query for 1st week.
>
>Read Var table for next week then run Append query for that week and
>continue to run the Append query for each week till month end is
>reached.
>
>Run query to sort down to Month table
>
>Then combine this table with Summary query of profit centers for GL and
>export this to Excel.
>
>  Anyone have some code I can start with to do this?
>
>
>
>
>
>Steve A. Turner