435,247 Members | 1,287 Online
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

# Week no & period no

 P: n/a I need to convert the normal calendar to show the week no., the period no. and the financial year. The financial year format is as follows:- Date start: 2 May, 2005 7 days a week, 4 weeks a period and 13 periods a year. normally 52 weeks per year but one 53-week a year every 6 years. The 53th week is included in period 13. Can someone advise any idea how to construct such conversion. Thanks, Ray Nov 13 '05 #1
9 Replies

 P: n/a Ray wrote: I need to convert the normal calendar to show the week no., the period no. and the financial year. The financial year format is as follows:- Date start: 2 May, 2005 7 days a week, 4 weeks a period and 13 periods a year. normally 52 weeks per year but one 53-week a year every 6 years. The 53th week is included in period 13. Can someone advise any idea how to construct such conversion. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 The easiest, and most commonly advised, method to solve this problem is to create a calendar table w/ all the FY info. E.g.: (If you're not familiar w/ this construct it is called a DDL [data definition language] description of a table's design. Unfortunately, it will not work in a JET db; but, it will work in SQL Server.) CREATE TABLE FYCalendar ( fiscal_year INTEGER NOT NULL , [period] BYTE NOT NULL CONSTRAINT CK_period CHECK ([period] BETWEEN 1 AND 13) , [week] BYTE NOT NULL CONSTRAINT CK_week CHECK ([week] BETWEEN 1 AND 4) , start_date DATETIME NOT NULL , end_date DATETIME NOT NULL , CONSTRAINT PK_FYCalendar -- prevents duplicates PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date) , CONSTRAINT CK_DateOrder -- start has to be less than end CHECK (start_date < end_date) ) Then fill the table w/ as many year's worth of data as you need. Then when you're trying to find out which FY, period, or week a date falls in you'd use a query like this: SELECT C.fiscal_year, C.[period], C.[week] FROM FYCalendar As C, Table_with_Date As T WHERE T.date_column BETWEEN C.start_date And C.end_date -- MGFoster:::mgf00 earthlink net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQl7EGoechKqOuFEgEQKXBQCglsl1TSPfcNsV5rg7FwU6/YbpNMsAnRAT uwlUp5mCjFr5bkzAYuy8oRb7 =ivIQ -----END PGP SIGNATURE----- Nov 13 '05 #2

 P: n/a MG, Thanks for your advice. Your assumption is correct that I am unfamiliar with DDL so I am unsure if I fully understand the instructions below or not. Could you please advise further if I misinterpret your meaning. Thanks, Ray The easiest, and most commonly advised, method to solve this problem is to create a calendar table w/ all the FY info. E.g.: (If you're not familiar w/ this construct it is called a DDL [data definition language] description of a table's design. Unfortunately, it will not work in a JET db; but, it will work in SQL Server.) Access is using JET db and I am not using SQL server. Did you mean I can use it in Access or not? CREATE TABLE FYCalendar ( fiscal_year INTEGER NOT NULL , [period] BYTE NOT NULL ok CONSTRAINT CK_period CHECK ([period] BETWEEN 1 AND 13) , What does CK_period mean? Why do we want to check it? Does it generate automatically? [week] BYTE NOT NULL CONSTRAINT CK_week CHECK ([week] BETWEEN 1 AND 4) , Same query as period. Why do we restrict to 1-4 weeks? In fact, the week no is from 1 to 52 or 53. start_date DATETIME NOT NULL , end_date DATETIME NOT NULL , CONSTRAINT PK_FYCalendar -- prevents duplicates ok PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date) , How can I put PK to so many fields? I think only one field. CONSTRAINT CK_DateOrder -- start has to be less than end CHECK (start_date < end_date) ok ) Then fill the table w/ as many year's worth of data as you need. Then when you're trying to find out which FY, period, or week a date falls in you'd use a query like this: SELECT C.fiscal_year, C.[period], C.[week] FROM FYCalendar As C, Table_with_Date As T WHERE T.date_column BETWEEN C.start_date And C.end_date What is Table_with_Date As T WHERE T.date_column BETWEEN C.start_date And C.end_date and how to constuct this part? Which field should I input the date and output financial year, week no and period no? -- MGFoster:::mgf00 earthlink net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQl7EGoechKqOuFEgEQKXBQCglsl1TSPfcNsV5rg7FwU6/YbpNMsAnRAT uwlUp5mCjFr5bkzAYuy8oRb7 =ivIQ -----END PGP SIGNATURE----- Nov 13 '05 #3

 P: n/a MG, Please disregard my previous message. I put the below statement in SQL view of a new query to create the table without success. Syntax error in all CONSTRAINT clauses. CHECK or -- are highlighted. If I removed all CONSTRAINT clauses, the table can be created. In addition, I am unsure how to enter data into the table. Do I need to enter each day as a record? Can you give me an example data. On the query, I change Table_with_Date with FYCalendar and it can run. Is it correct? Thanks, Ray "MGFoster" wrote in message news:vu*****************@newsread1.news.pas.earthl ink.net... Ray wrote: I need to convert the normal calendar to show the week no., the period no. and the financial year. The financial year format is as follows:- Date start: 2 May, 2005 7 days a week, 4 weeks a period and 13 periods a year. normally 52 weeks per year but one 53-week a year every 6 years. The 53th week is included in period 13. Can someone advise any idea how to construct such conversion. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 The easiest, and most commonly advised, method to solve this problem is to create a calendar table w/ all the FY info. E.g.: (If you're not familiar w/ this construct it is called a DDL [data definition language] description of a table's design. Unfortunately, it will not work in a JET db; but, it will work in SQL Server.) CREATE TABLE FYCalendar ( fiscal_year INTEGER NOT NULL , [period] BYTE NOT NULL CONSTRAINT CK_period CHECK ([period] BETWEEN 1 AND 13) , [week] BYTE NOT NULL CONSTRAINT CK_week CHECK ([week] BETWEEN 1 AND 4) , start_date DATETIME NOT NULL , end_date DATETIME NOT NULL , CONSTRAINT PK_FYCalendar -- prevents duplicates PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date) , CONSTRAINT CK_DateOrder -- start has to be less than end CHECK (start_date < end_date) ) Then fill the table w/ as many year's worth of data as you need. Then when you're trying to find out which FY, period, or week a date falls in you'd use a query like this: SELECT C.fiscal_year, C.[period], C.[week] FROM FYCalendar As C, Table_with_Date As T WHERE T.date_column BETWEEN C.start_date And C.end_date -- MGFoster:::mgf00 earthlink net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQl7EGoechKqOuFEgEQKXBQCglsl1TSPfcNsV5rg7FwU6/YbpNMsAnRAT uwlUp5mCjFr5bkzAYuy8oRb7 =ivIQ -----END PGP SIGNATURE----- Nov 13 '05 #4

 P: n/a -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 The CONSTRAINT CK_DateOrder is a CHECK constraint. Syntax: CONSTRAINT CHECK () The DDL doesn't work in Access. I meant it as a demonstration. If you want to make it work in Access - take out the CHECK constraints and the comments (lines beginning with 2 dashes [--]). Do not remove the PK_FYCalendar constraint. After the table is created open it in design view and set the following properties of the following columns: period: Validation Rule: Between 1 and 13 Validation Text: Period must be between 1 and 13 week: Validation Rule: Between 1 and 4 Validation Text: Week must be between 1 and 4 Open the table's Properties dialog box (F4 key). Validation Rule: start_date < end_date Validation Text: The start date must be less than the end date I'll provide comments on each column in the FYCalendar table. CREATE TABLE FYCalendar ( fiscal_year INTEGER NOT NULL , [period] BYTE NOT NULL CONSTRAINT CK_period CHECK ([period] BETWEEN 1 AND 13) , [week] BYTE NOT NULL CONSTRAINT CK_week CHECK ([week] BETWEEN 1 AND 4) , start_date DATETIME NOT NULL , end_date DATETIME NOT NULL , CONSTRAINT PK_FYCalendar -- prevents duplicates PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date) , CONSTRAINT CK_DateOrder -- start has to be less than end CHECK (start_date < end_date) ) CHECK constraint: The CONSTRAINT is a CHECK constraint on the value that can be entered into the column. When you enter data the CHECK automatically runs & determines if the value satisfies the evaluation. Same as the Validation Rule property in Access table design view. fiscal_year - the year in YYYY format. E.g.: 2005. period - the FY period. The CHECK makes sure the entered number is in the range 1 thru 13. week - the week number in the period. The CHECK makes sure the entered number is in the range 1 thru 4. start_date - is the full beginning date of the period. E.g.: 5/2/2005. end_date - is the full ending date of the period. E.g.: 6/5/2005. The CONSTRAINT PK_FYCalendar is the Primary Key on the table, which prevents duplicate entries. The CONSTRAINT CK_DateOrder is a CHECK to make sure the start_date is before the end_date. Example data (each row [record] is info on one period): fiscal_year period week start_date end_date =========== ====== ==== ========== ======== 2004 12 4 12/1/2005 12/31/2005 2005 1 1 5/2/2005 6/5/2005 2005 1 2 6/6/2005 7/2/2005 2005 1 3 7/3/2005 8/1/2005 .... etc. ... For simplicity the query can be changed like this (trying to find which period 15 May 2005 is in): SELECT C.fiscal_year, C.[period], C.[week] FROM FYCalendar As C WHERE #5/15/2005# BETWEEN C.start_date And C.end_date Will return (using the above example data): fiscal_year period week ----------- ------ ---- 2005 1 1 If you want to use a table, use my original query - just change the name of the table "Table_with_Date" to whatever table you are using that has the comparison date. -- MGFoster:::mgf00 earthlink net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQmAJvYechKqOuFEgEQJtzgCgnJ1WV/abUfc12yti5WuEIcG+lXcAoJ3N aKG3bLS0s56xzOxZL9gDOjIW =RgdW -----END PGP SIGNATURE----- Ray wrote: MG, Please disregard my previous message. I put the below statement in SQL view of a new query to create the table without success. Syntax error in all CONSTRAINT clauses. CHECK or -- are highlighted. If I removed all CONSTRAINT clauses, the table can be created. In addition, I am unsure how to enter data into the table. Do I need to enter each day as a record? Can you give me an example data. On the query, I change Table_with_Date with FYCalendar and it can run. Is it correct? Thanks, Ray "MGFoster" wrote in message news:vu*****************@newsread1.news.pas.earthl ink.net...Ray wrote:I need to convert the normal calendar to show the week no., the periodno. and the financial year. The financial year format is as follows:-Date start: 2 May, 20057 days a week, 4 weeks a period and 13 periods a year.normally 52 weeks per year but one 53-week a year every 6 years. The53th week is included in period 13.Can someone advise any idea how to construct such conversion.-----BEGIN PGP SIGNED MESSAGE-----Hash: SHA1The easiest, and most commonly advised, method to solve this problem isto create a calendar table w/ all the FY info. E.g.:(If you're not familiar w/ this construct it is called a DDL [datadefinition language] description of a table's design. Unfortunately, itwill not work in a JET db; but, it will work in SQL Server.)CREATE TABLE FYCalendar ( fiscal_year INTEGER NOT NULL , [period] BYTE NOT NULL CONSTRAINT CK_period CHECK ([period] BETWEEN 1 AND 13) , [week] BYTE NOT NULL CONSTRAINT CK_week CHECK ([week] BETWEEN 1 AND 4) , start_date DATETIME NOT NULL , end_date DATETIME NOT NULL , CONSTRAINT PK_FYCalendar -- prevents duplicates PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date) , CONSTRAINT CK_DateOrder -- start has to be less than end CHECK (start_date < end_date))Then fill the table w/ as many year's worth of data as you need. Thenwhen you're trying to find out which FY, period, or week a date falls inyou'd use a query like this:SELECT C.fiscal_year, C.[period], C.[week]FROM FYCalendar As C, Table_with_Date As TWHERE T.date_column BETWEEN C.start_date And C.end_date--MGFoster:::mgf00 earthlink netOakland, CA (USA)-----BEGIN PGP SIGNATURE-----Version: PGP for Personal Privacy 5.0Charset: noconviQA/AwUBQl7EGoechKqOuFEgEQKXBQCglsl1TSPfcNsV5rg7FwU6/YbpNMsAnRATuwlUp5mCjFr5bkzAYuy8oRb7=ivIQ-----END PGP SIGNATURE----- Nov 13 '05 #5