School Year - ways to handle it (1 Viewer)

[Ni-Ni]

Hey guys, my name is Nile. I was wondering if you could help me with a little bit of planning and design here. Anyways I gotta build a database for a network of schools, it has to do with invoicing and receipts with students. So yeah I got this master table here I need to store the year of all the child transactions. So what I came up with so far is this. I am going to make a Long Integer field and store the year like this "20022003" as you guys know the school year is different than a normal year, it's halfs of both. Anyways guys, if you have a better idea for me, I would surely be thankful to you!

All the Best to you my Bros in Access,
Nile

 

[crosmill]

Hi Nile

I'd advise storing the date as a date field, access will perform calculations differntly to date fields as it will to numbers, I know the school year is different to a normal year but when your manipulating the data you can make allowances for this by perfoming calculations, there are fuctions that will allow you calculate months, days, hours, minutes, seconds, all you have to do to compansate to add or subtract from your date.

hth

 

[Chris RR]

If the school "year" changes from year to year you might want to consider creating a lookup table to hold the beginning & ending dates of each "year". You'd probably have a field that would be the academic year (it would contain something like "20022003" and it could be text), a printable version of the academic year ("2002 - 2003" or whatever text should appear on any reports) and a beginning and ending date (in date format).

Then you can easily compare any date to the beginning and ending dates in the table (using between) and find your academic year.

I've done this with a fiscal calendar that does not match any known wall calendar (got to love those accountants) and it works very well. Our version had to find fiscal months and weeks as well. Because it was a heavily used thing, I built some nice functions that did all the retrieval for me.

You do need to remember to load out the next year's dates when they are available, and yes, I would leave previous year's dates in. You can build a little maintenance form just for this table & appoint some user "administrator" of dates and let them worry about it.