By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,888 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Week no & period no

P: n/a
Ray
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
Share this Question
Share on Google+
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 <at> earthlink <decimal-point> 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
Ray
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 <at> earthlink <decimal-point> 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
Ray
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" <me@privacy.com> 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 <at> earthlink <decimal-point> 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 <constraint name> CHECK (<evaluation>)

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 <at> earthlink <decimal-point> 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" <me@privacy.com> 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 <at> earthlink <decimal-point> 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 #5

P: n/a
Ray
MG,

Many thanks for your detail explanation. I have modified to the following
SQL statement and created the FYCalendar table successfully.

CREATE TABLE FYCalendar (
fiscal_year INTEGER NOT NULL ,
[period] BYTE NOT NULL ,
[week] BYTE NOT NULL ,
start_date DATETIME NOT NULL ,
end_date DATETIME NOT NULL ,
CONSTRAINT PK_FYCalendar
PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date))

I also created the validation rules successfully. However, there were some
issues.

1. We do not use the week number like this, period 1 and week no 1. We
used to say period 5 or week 18. I am at loss why you configure the week no
to 1-4, it should be 1 to 52 or 1 to 53 once every 6 years.

2. After I enter the end date, the validation rule is always triggered
regardless correct date entered. Firstly, I believe probably it is due to
European date format. I changed to American date format. The issue
remained unchanged. I removed the validation rule and it works ok. Do you
have any idea why the validation is incorrectly run.

3. Example data (each row [record] is info on one period. Your examples
look random and do not correspond with the info of one period. For example,
2005, 1, 1, 5/2/2005,6/5/2005, it indicates year 2005, period 1, week 1, the
start date May 2, 2005 and end date June 5, 2005. In fact, period 1 should
be from May 2, 2005 and ended May 29, 2005 and the week no can be 1 to 4.
In addition, why did you quote 3 period 1 in 2005. I am a bit confused this
part. Could you please clarify it.

I also built both old query and simplified query successfully. When I ran
the old query, it come out two parameter boxes asking for T.date_column, and
c.end_date and what should I enter. When I ran the simplified query, it
came out one parameter box asking for c.end_date and what should I enter. I
tried to enter a date that I want to know the year no, period no and week no
but the answer is incorrect. Probably, the issue is linked with and
question 3 as well.

Your further explanation is highly appreciated.

Ray
"MGFoster" <me@privacy.com> wrote in message
news:9R*****************@newsread1.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The CONSTRAINT CK_DateOrder is a CHECK constraint. Syntax:

CONSTRAINT <constraint name> CHECK (<evaluation>)

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 <at> earthlink <decimal-point> 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" <me@privacy.com> 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 <at> earthlink <decimal-point> 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 #6

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. I used week 1-4 because it would indicate the week of the period.
If you want to use week 1-53 then change the Validation Rule to "BETWEEN
1 AND 53." I'd also change the name of the column from "week" to
"year_week" to differentiate between a week of the period and a week of
the year. If you change the column name, be sure to change it in all
your queries that use this table.

2. Access probably tried to "correct" the Validation Rule by delimiting
the names of the columns with double quotes, thereby changing the
validation from a comparison of the dates in the columns to a comparison
of the strings "start_date" and "end_date". Since the string "end_date"
is alphabetically before "start_date" the exception appeared.

To "inform" Access that the words used in the Validation Rule are column
names and not strings, enclose each column name in square brackets.
E.g.:

[start_date] < [end_date]

3. That data I used was just random example data, it was not meant to
actually represent your fiscal year dating system. You have to put in
the correct dates that denote your fiscal year dates.

4. I'd have to see the SQL of your queries to determine why parameter
prompts are appearing. My guess is that you have not aliased the table
names. Post your queries' SQL, please.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmKK9oechKqOuFEgEQLFZgCfXxFD89Z2WNSbDxbT18+nLA iBFaEAoJbr
2Q+jkm+291/AhQ8McE8N08Jk
=uD1X
-----END PGP SIGNATURE-----

Ray wrote:
MG,

Many thanks for your detail explanation. I have modified to the following
SQL statement and created the FYCalendar table successfully.

CREATE TABLE FYCalendar (
fiscal_year INTEGER NOT NULL ,
[period] BYTE NOT NULL ,
[week] BYTE NOT NULL ,
start_date DATETIME NOT NULL ,
end_date DATETIME NOT NULL ,
CONSTRAINT PK_FYCalendar
PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date))

I also created the validation rules successfully. However, there were some
issues.

1. We do not use the week number like this, period 1 and week no 1. We
used to say period 5 or week 18. I am at loss why you configure the week no
to 1-4, it should be 1 to 52 or 1 to 53 once every 6 years.

2. After I enter the end date, the validation rule is always triggered
regardless correct date entered. Firstly, I believe probably it is due to
European date format. I changed to American date format. The issue
remained unchanged. I removed the validation rule and it works ok. Do you
have any idea why the validation is incorrectly run.

3. Example data (each row [record] is info on one period. Your examples
look random and do not correspond with the info of one period. For example,
2005, 1, 1, 5/2/2005,6/5/2005, it indicates year 2005, period 1, week 1, the
start date May 2, 2005 and end date June 5, 2005. In fact, period 1 should
be from May 2, 2005 and ended May 29, 2005 and the week no can be 1 to 4.
In addition, why did you quote 3 period 1 in 2005. I am a bit confused this
part. Could you please clarify it.

I also built both old query and simplified query successfully. When I ran
the old query, it come out two parameter boxes asking for T.date_column, and
c.end_date and what should I enter. When I ran the simplified query, it
came out one parameter box asking for c.end_date and what should I enter. I
tried to enter a date that I want to know the year no, period no and week no
but the answer is incorrect. Probably, the issue is linked with and
question 3 as well.

Your further explanation is highly appreciated.

Ray
"MGFoster" <me@privacy.com> wrote in message
news:9R*****************@newsread1.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The CONSTRAINT CK_DateOrder is a CHECK constraint. Syntax:

CONSTRAINT <constraint name> CHECK (<evaluation>)

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 <at> earthlink <decimal-point> 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" <me@privacy.com> wrote in message
news:vu*****************@newsread1.news.pas.ear thlink.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 <at> earthlink <decimal-point> 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 #7

P: n/a
Ray
MG,

Thanks for your detail explanation. I have fixed all the issues except one
of queries as below.

SELECT C.Fiscal_Year, C.Period, C.Week
FROM tblDXFYCalendar AS C, tblDXFYCalendar AS T
WHERE ((([T].[date_column]) Between [C].[Start_Date] And [C].[End_Date]));

The extra brackets and square brackets were added by the system. When I ran
this query, an Enter Parameter Value box appeared. I entered a date and it
gave me an incorrect result.

Thanks,

Ray

"MGFoster" <me@privacy.com> wrote in message
news:MV*****************@newsread3.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. I used week 1-4 because it would indicate the week of the period.
If you want to use week 1-53 then change the Validation Rule to "BETWEEN
1 AND 53." I'd also change the name of the column from "week" to
"year_week" to differentiate between a week of the period and a week of
the year. If you change the column name, be sure to change it in all
your queries that use this table.

2. Access probably tried to "correct" the Validation Rule by delimiting
the names of the columns with double quotes, thereby changing the
validation from a comparison of the dates in the columns to a comparison
of the strings "start_date" and "end_date". Since the string "end_date"
is alphabetically before "start_date" the exception appeared.

To "inform" Access that the words used in the Validation Rule are column
names and not strings, enclose each column name in square brackets.
E.g.:

[start_date] < [end_date]

3. That data I used was just random example data, it was not meant to
actually represent your fiscal year dating system. You have to put in
the correct dates that denote your fiscal year dates.

4. I'd have to see the SQL of your queries to determine why parameter
prompts are appearing. My guess is that you have not aliased the table
names. Post your queries' SQL, please.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmKK9oechKqOuFEgEQLFZgCfXxFD89Z2WNSbDxbT18+nLA iBFaEAoJbr
2Q+jkm+291/AhQ8McE8N08Jk
=uD1X
-----END PGP SIGNATURE-----

Ray wrote:
MG,

Many thanks for your detail explanation. I have modified to the
following SQL statement and created the FYCalendar table successfully.

CREATE TABLE FYCalendar (
fiscal_year INTEGER NOT NULL ,
[period] BYTE NOT NULL ,
[week] BYTE NOT NULL ,
start_date DATETIME NOT NULL ,
end_date DATETIME NOT NULL ,
CONSTRAINT PK_FYCalendar
PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date))

I also created the validation rules successfully. However, there were
some issues.

1. We do not use the week number like this, period 1 and week no 1.
We used to say period 5 or week 18. I am at loss why you configure the
week no to 1-4, it should be 1 to 52 or 1 to 53 once every 6 years.

2. After I enter the end date, the validation rule is always triggered
regardless correct date entered. Firstly, I believe probably it is due
to European date format. I changed to American date format. The issue
remained unchanged. I removed the validation rule and it works ok. Do
you have any idea why the validation is incorrectly run.

3. Example data (each row [record] is info on one period. Your
examples look random and do not correspond with the info of one period.
For example, 2005, 1, 1, 5/2/2005,6/5/2005, it indicates year 2005,
period 1, week 1, the start date May 2, 2005 and end date June 5, 2005.
In fact, period 1 should be from May 2, 2005 and ended May 29, 2005 and
the week no can be 1 to 4. In addition, why did you quote 3 period 1 in
2005. I am a bit confused this part. Could you please clarify it.

I also built both old query and simplified query successfully. When I
ran the old query, it come out two parameter boxes asking for
T.date_column, and c.end_date and what should I enter. When I ran the
simplified query, it came out one parameter box asking for c.end_date and
what should I enter. I tried to enter a date that I want to know the
year no, period no and week no but the answer is incorrect. Probably,
the issue is linked with and question 3 as well.

Your further explanation is highly appreciated.

Ray
"MGFoster" <me@privacy.com> wrote in message
news:9R*****************@newsread1.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The CONSTRAINT CK_DateOrder is a CHECK constraint. Syntax:

CONSTRAINT <constraint name> CHECK (<evaluation>)

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 <at> earthlink <decimal-point> 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" <me@privacy.com> wrote in message
news:vu*****************@newsread1.news.pas.ea rthlink.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 <at> earthlink <decimal-point> 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 #8

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You do not need the 2nd table name in the FROM clause. Change it like
the following. This query will prompt you with the [Enter search date]
parameter prompt. I believe you can enter any valid date format - not
just American formatted dates.

PARAMETERS [Enter search date] Date;
SELECT C.Fiscal_Year, C.Period, C.Week
FROM tblDXFYCalendar AS C
WHERE [Enter search date] Between [C].[Start_Date] And [C].[End_Date]

Read the Access Help articles, or a book on SQL, on SQL data
manipulation language (DML). It will help you understand how to create
a query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmKqUoechKqOuFEgEQJN7gCgtNIXVK/tWuq53Kkpp4k9I4TE2nQAoILQ
CvlxtI79TsRWr0GD04usJbna
=W8ea
-----END PGP SIGNATURE-----
Ray wrote:
MG,

Thanks for your detail explanation. I have fixed all the issues except one
of queries as below.

SELECT C.Fiscal_Year, C.Period, C.Week
FROM tblDXFYCalendar AS C, tblDXFYCalendar AS T
WHERE ((([T].[date_column]) Between [C].[Start_Date] And [C].[End_Date]));

The extra brackets and square brackets were added by the system. When I ran
this query, an Enter Parameter Value box appeared. I entered a date and it
gave me an incorrect result.

Thanks,

Ray

"MGFoster" <me@privacy.com> wrote in message
news:MV*****************@newsread3.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. I used week 1-4 because it would indicate the week of the period.
If you want to use week 1-53 then change the Validation Rule to "BETWEEN
1 AND 53." I'd also change the name of the column from "week" to
"year_week" to differentiate between a week of the period and a week of
the year. If you change the column name, be sure to change it in all
your queries that use this table.

2. Access probably tried to "correct" the Validation Rule by delimiting
the names of the columns with double quotes, thereby changing the
validation from a comparison of the dates in the columns to a comparison
of the strings "start_date" and "end_date". Since the string "end_date"
is alphabetically before "start_date" the exception appeared.

To "inform" Access that the words used in the Validation Rule are column
names and not strings, enclose each column name in square brackets.
E.g.:

[start_date] < [end_date]

3. That data I used was just random example data, it was not meant to
actually represent your fiscal year dating system. You have to put in
the correct dates that denote your fiscal year dates.

4. I'd have to see the SQL of your queries to determine why parameter
prompts are appearing. My guess is that you have not aliased the table
names. Post your queries' SQL, please.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmKK9oechKqOuFEgEQLFZgCfXxFD89Z2WNSbDxbT18+nLA iBFaEAoJbr
2Q+jkm+291/AhQ8McE8N08Jk
=uD1X
-----END PGP SIGNATURE-----

Ray wrote:
MG,

Many thanks for your detail explanation. I have modified to the
following SQL statement and created the FYCalendar table successfully.

CREATE TABLE FYCalendar (
fiscal_year INTEGER NOT NULL ,
[period] BYTE NOT NULL ,
[week] BYTE NOT NULL ,
start_date DATETIME NOT NULL ,
end_date DATETIME NOT NULL ,
CONSTRAINT PK_FYCalendar
PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date))

I also created the validation rules successfully. However, there were
some issues.

1. We do not use the week number like this, period 1 and week no 1.
We used to say period 5 or week 18. I am at loss why you configure the
week no to 1-4, it should be 1 to 52 or 1 to 53 once every 6 years.

2. After I enter the end date, the validation rule is always triggered
regardless correct date entered. Firstly, I believe probably it is due
to European date format. I changed to American date format. The issue
remained unchanged. I removed the validation rule and it works ok. Do
you have any idea why the validation is incorrectly run.

3. Example data (each row [record] is info on one period. Your
examples look random and do not correspond with the info of one period.
For example, 2005, 1, 1, 5/2/2005,6/5/2005, it indicates year 2005,
period 1, week 1, the start date May 2, 2005 and end date June 5, 2005.
In fact, period 1 should be from May 2, 2005 and ended May 29, 2005 and
the week no can be 1 to 4. In addition, why did you quote 3 period 1 in
2005. I am a bit confused this part. Could you please clarify it.

I also built both old query and simplified query successfully. When I
ran the old query, it come out two parameter boxes asking for
T.date_column, and c.end_date and what should I enter. When I ran the
simplified query, it came out one parameter box asking for c.end_date and
what should I enter. I tried to enter a date that I want to know the
year no, period no and week no but the answer is incorrect. Probably,
the issue is linked with and question 3 as well.

Your further explanation is highly appreciated.

Ray
"MGFoster" <me@privacy.com> wrote in message
news:9R*****************@newsread1.news.pas.ear thlink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The CONSTRAINT CK_DateOrder is a CHECK constraint. Syntax:

CONSTRAINT <constraint name> CHECK (<evaluation>)

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 <at> earthlink <decimal-point> 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" <me@privacy.com> wrote in message
>news:vu*****************@newsread1.news.pas.e arthlink.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 <at> earthlink <decimal-point> 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 #9

P: n/a
Ray
MG,

Many thanks for your useful advice.

Ray

"MGFoster" <me@privacy.com> wrote in message
news:dT*****************@newsread3.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You do not need the 2nd table name in the FROM clause. Change it like
the following. This query will prompt you with the [Enter search date]
parameter prompt. I believe you can enter any valid date format - not
just American formatted dates.

PARAMETERS [Enter search date] Date;
SELECT C.Fiscal_Year, C.Period, C.Week
FROM tblDXFYCalendar AS C
WHERE [Enter search date] Between [C].[Start_Date] And [C].[End_Date]

Read the Access Help articles, or a book on SQL, on SQL data
manipulation language (DML). It will help you understand how to create
a query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmKqUoechKqOuFEgEQJN7gCgtNIXVK/tWuq53Kkpp4k9I4TE2nQAoILQ
CvlxtI79TsRWr0GD04usJbna
=W8ea
-----END PGP SIGNATURE-----
Ray wrote:
MG,

Thanks for your detail explanation. I have fixed all the issues except
one of queries as below.

SELECT C.Fiscal_Year, C.Period, C.Week
FROM tblDXFYCalendar AS C, tblDXFYCalendar AS T
WHERE ((([T].[date_column]) Between [C].[Start_Date] And
[C].[End_Date]));

The extra brackets and square brackets were added by the system. When I
ran this query, an Enter Parameter Value box appeared. I entered a date
and it gave me an incorrect result.

Thanks,

Ray

"MGFoster" <me@privacy.com> wrote in message
news:MV*****************@newsread3.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. I used week 1-4 because it would indicate the week of the period.
If you want to use week 1-53 then change the Validation Rule to "BETWEEN
1 AND 53." I'd also change the name of the column from "week" to
"year_week" to differentiate between a week of the period and a week of
the year. If you change the column name, be sure to change it in all
your queries that use this table.

2. Access probably tried to "correct" the Validation Rule by delimiting
the names of the columns with double quotes, thereby changing the
validation from a comparison of the dates in the columns to a comparison
of the strings "start_date" and "end_date". Since the string "end_date"
is alphabetically before "start_date" the exception appeared.

To "inform" Access that the words used in the Validation Rule are column
names and not strings, enclose each column name in square brackets.
E.g.:

[start_date] < [end_date]

3. That data I used was just random example data, it was not meant to
actually represent your fiscal year dating system. You have to put in
the correct dates that denote your fiscal year dates.

4. I'd have to see the SQL of your queries to determine why parameter
prompts are appearing. My guess is that you have not aliased the table
names. Post your queries' SQL, please.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmKK9oechKqOuFEgEQLFZgCfXxFD89Z2WNSbDxbT18+nLA iBFaEAoJbr
2Q+jkm+291/AhQ8McE8N08Jk
=uD1X
-----END PGP SIGNATURE-----

Ray wrote:

MG,

Many thanks for your detail explanation. I have modified to the
following SQL statement and created the FYCalendar table successfully.

CREATE TABLE FYCalendar (
fiscal_year INTEGER NOT NULL ,
[period] BYTE NOT NULL ,
[week] BYTE NOT NULL ,
start_date DATETIME NOT NULL ,
end_date DATETIME NOT NULL ,
CONSTRAINT PK_FYCalendar
PRIMARY KEY (fiscal_year, [period], [week], start_date, end_date))

I also created the validation rules successfully. However, there were
some issues.

1. We do not use the week number like this, period 1 and week no 1.
We used to say period 5 or week 18. I am at loss why you configure the
week no to 1-4, it should be 1 to 52 or 1 to 53 once every 6 years.

2. After I enter the end date, the validation rule is always
triggered regardless correct date entered. Firstly, I believe probably
it is due to European date format. I changed to American date format.
The issue remained unchanged. I removed the validation rule and it
works ok. Do you have any idea why the validation is incorrectly run.

3. Example data (each row [record] is info on one period. Your
examples look random and do not correspond with the info of one period.
For example, 2005, 1, 1, 5/2/2005,6/5/2005, it indicates year 2005,
period 1, week 1, the start date May 2, 2005 and end date June 5, 2005.
In fact, period 1 should be from May 2, 2005 and ended May 29, 2005 and
the week no can be 1 to 4. In addition, why did you quote 3 period 1 in
2005. I am a bit confused this part. Could you please clarify it.

I also built both old query and simplified query successfully. When I
ran the old query, it come out two parameter boxes asking for
T.date_column, and c.end_date and what should I enter. When I ran the
simplified query, it came out one parameter box asking for c.end_date
and what should I enter. I tried to enter a date that I want to know
the year no, period no and week no but the answer is incorrect.
Probably, the issue is linked with and question 3 as well.

Your further explanation is highly appreciated.

Ray
"MGFoster" <me@privacy.com> wrote in message
news:9R*****************@newsread1.news.pas.ea rthlink.net...
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>The CONSTRAINT CK_DateOrder is a CHECK constraint. Syntax:
>
> CONSTRAINT <constraint name> CHECK (<evaluation>)
>
>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 <at> earthlink <decimal-point> 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" <me@privacy.com> wrote in message
>>news:vu*****************@newsread1.news.pas. earthlink.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 <at> earthlink <decimal-point> 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 #10

This discussion thread is closed

Replies have been disabled for this discussion.