best approach for data fields?

[merlin777]

i'm designing an appointments database. Each appointment will have a time - year, date and time. I'll probably have at least one report that lists them by week number.

So, here are my questions:
should i break up the time into seperate fields i.e. year, date and time of day, or use just one field?

should i include a week number to be added by the user or should i keep it simpler for them by calculating it? From what I remember calculating week numbers in excel was quite complex and i'm still an access novice.

any advice greatly appreciated...

 

[plog]

Keep it all in one field, calculate everything that can be calculated.

The trouble with weeks is that people fail to realize that 7 never goes perfectly into 365 (nor 366). My advice is to create a custom get_Week() function, pass it a date value and have it return the correct week that you want. Of course, that requires you being able to logical define what week every date should resolve too (its a lot more difficult than you think).

 

[merlin777]

Thanks. I've done it in excel but there's a function for it. Even so it's tricky to use with regional variations involved.

 

[GinaWhipp]

I would use separate fields for storing Date\Time but that is just me. I like to use a Date Picker and a separate Time Packer just because. However, if you plan to extract information then, as plog suggested, keep them in one field.

No need for Users to worry about Week Numbers, you get those, see...
https://support.office.com/en-nz/article/Format-Function-6f29d87b-8761-408d-81d3-63b9cd842530

 

[plog]

You should always worry about week numbers. They never line up like people expect.

 

[merlin777]

thanks for the link. thats set me in the right direction.

this db will be for an NHS department so they'll be heavily oriented around week numbers and i can't really work around that.

Ican't see this being used for more than a few years so, thinking about it, I might work them out by hand and put them in a table.

You mentioned a time picker. Can that be customised to offer - for instance - only every 15 mins from 0800 to 1700?