Storing data by week

[Guest]

I'm creating a database that will store data on a wwekly basis. I am not sure
what the best identifier is for a particular week.
I could use a date field to record the first day of the week in question, a
'Week Beginning' field. However, I need to refer to the weeks using the
company's internal calendar which consists of 12 periods of 4 or 5 weeks
running from July to June on a 4,4,5 cycle. Therefore, all periods have 4
weeks except for periods 3, 6, 9 & 12 which have 5 weeks. Additionally, every
four years, week 1 of period 1 will have 5 weeks. I'm not sure of the best
way to tie this information to a date field.
Originally I was going to have three fields in the table that stored the
data, [Year], [Period] and [Week]. However, this did not seem like an elegant
way of doing it.
(Actually, trying to clarify the problem for this posting is helping my
thought process already)
I think I'd be better using VB to calculate the Week, Period and Year based
on a date entry. So possibly I've answered my own question... )
Ideally, when a user is entering data, they will have a combo-box that
offers the last four weeks to select from. I presume that this can be
automatically calculated from the current date and sent to a table using an
OnLoad procedure or similar.

Any opinions and advice would be valued.

 

[Armen Stein]

I'm creating a database that will store data on a wwekly basis. I am not sure
what the best identifier is for a particular week.
I could use a date field to record the first day of the week in question, a
'Week Beginning' field. However, I need to refer to the weeks using the
company's internal calendar which consists of 12 periods of 4 or 5 weeks
running from July to June on a 4,4,5 cycle. Therefore, all periods have 4
weeks except for periods 3, 6, 9 & 12 which have 5 weeks. Additionally, every
four years, week 1 of period 1 will have 5 weeks. I'm not sure of the best
way to tie this information to a date field.
Originally I was going to have three fields in the table that stored the
data, [Year], [Period] and [Week]. However, this did not seem like an elegant
way of doing it.
(Actually, trying to clarify the problem for this posting is helping my
thought process already)
I think I'd be better using VB to calculate the Week, Period and Year based
on a date entry. So possibly I've answered my own question... )
Ideally, when a user is entering data, they will have a combo-box that
offers the last four weeks to select from. I presume that this can be
automatically calculated from the current date and sent to a table using an
OnLoad procedure or similar.

Any opinions and advice would be valued.

In general, you would want to store the minimum amount of data and
compute anything else you might need. In this case, that would be the
date only - as you point out, you can compute the Week, Year and Period
whenever you need them.

If this turns out to be a performance issue, you can store them also -
but I suggest you indicate that they are calculated fields. In our
shop, we have a standard to add "_CALC" to the end of each de-normalized
calculated field (e.g. Period_CALC). This reminds you to make sure
there's code to update it whenever the Date field changes, and also to
not allow the user to update it on a form.

Yes, you can set the RowSource of a combobox based on the current date
when the form loads.