Automatically change table links

[lucky625]

I'm creating a leave (holiday) database but each year must be kept seperate for analysis. (I have split the front and backend of the database) I thought of using a dropdown on the main page which could be used to select the year which relates to the leave being dealt with. Depending on the choice selected (present financial year as default) the links would update to the required database.

Any ideas?

 

[JoeCruse]

Are you suggesting that you will make a separate database for each year??? I hope not, because there is no need to with relational databases like Access, and you would be headed down the wrong path. You can use queries on your table to pull data by year.

 

[lucky625]

new database

That is my intention as the whole database has multiple tables, queries and calculations. This project was started as a quick fix for a friend in work but spiralled in its complexity. Therefore its badly designed, (in my eyes) but as it was an unpaid project I was reluctant to spend the time to rebuild it.

I can send you a copy if you feel that would allow you a better feel for my problem.

The main tables that need changing would be the leave table, the days in lui and the sickleave. But for ease of management I intended just replicating the database for each year.

PS please remember that a finanacial year is April 01 - March 31

 

[JoeCruse]

There is still no reason to start a new database full of blank tables. It sounds like you are entering the leave dates, so as long as that captures the year in the date, it should ALL be in one database. If you only want forms to "see" current year, make the underlying query for the form (you should use queries based on tables for the recordset for forms) limit the recordset by date. For any reports, make the underlying query limit the recordset by date. I would imagine that, no matter how bad the database structure, this would still be the easiest route to go.

 

[lucky625]

Thanks for the advice

 

[neileg]

Trying to live with a bad structure instead of putting it right may seem easier in the short term, but long term it's a disaster!

 

[AngelsGuardian]

Well..having 1 database for each year may not be that bad no ?

I am having the same problem here and was wondering.
I know that in 1 year, i will get a lot of content in my DB and by having 1 DB a year...it makes it less heavy in the end when you do researches.

That's the way i see it.

Still...having a pulldown list with years would be a nice thing