table - missing field

[gscma1]

My database is well and truly set up and has been functioning for almost a year. however, i have recently noticed i made an error in my table creation as i forgot to include a year field.

i have a tblWeeksDates which includes the week numbers for this year 1 - 52 and the start date of each week.

the primary key is the week no.

i need these to be the week no.s for year 06/07.

i use the week no. field within most of my tables.

for the year 07/08 i need to be able to use the numbers 1 - 52 again however they have different week start dates.

any ideas?

 

[Remou]

Redesign?

 

[gscma1]

redesign my whole database? surely not?

 

[dhookom]

Just redesign the parts that depend on the week number. You haven't provided any information regarding how you use the field or why you can't add a year field to the table and include it in the primary key.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gscma1]

the week number field is used in various other tables.

currently the week number is set as the primary key within this table.

we input employees working hours into this database, therefore these are done weekly. within this table hours and employee ids are input against a specific week number.

since the week number is the primary key, it will not allow me to change the primary key as it links to about 9 other tables.

changing this would be a massive job!

 

[SBerthold]

>for the year 07/08 i need to be able to use the numbers 1 - 52 again however they have different week start dates.

Yes, and not only that, but you will sometimes be missing a week 53.

First, you have to determine when the first week of a year starts - there are basically three or so different thoughts there.

Secondly, you will need to allow for a week 53.

It would be better to not store weeks, but dates, and then convert those dates to the week number when needed (like using the DatePart() function)

 

[dhookom]

This is one of the reasons why I generally use autonumbers as primary keys.

If you have set your Cascade Updates on in your relationships, you might be able to change your week number field from 45 to 200645. This would propogate to all your other tables. You would then have the ability to include both 200645 and 200745 in the week number field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

dhookom, I can see that this is a lifeline, but isn't this type of change likely to lead to later problems at a time when changes would be impossible, rather than a great deal of work?

 

[dhookom]

I would probably avoided the design error to begin with. I'm not sure what you mean by "when changes would be impossible".

It didn't seem that gscma1 was too receptive to thoroughly resolving the poor design.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gscma1]

i don't doubt that you "would have avoided the design error to begin with" and you are probably 100 times better with databases than i'll ever be, but i've tried my best and taught myself hence the "poor design" that you refer to. I'm a very busy person hence why as you put it, i'm not "too receptive to thoroughly resolving the poor design".

silly me for thinking this was somewhere i could get advice when i made silly mistakes...

 

[dhookom]

My reply was generally to Remou in response to his questioning my suggesion to you on [red]15 Feb 07 12:12 [/red] which had a possible simple solution for you? If you read it, did you try it? If you did try it, what were the results? If you didn't try it, why not?

We all make mistakes in design. Much of this is based on our belief that customers understand what they are talking about. We also attempt to preserve "paper" system look and feel in our table structures. It would be interested to start a thread on the "top ten database developer mistakes I have made".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]