Solved Suggestions on recurring dates management... (1 Viewer)

[Garren.Shannon]

Hello all,

I am crafting a ridership database. The database collects data about bus trips and reports totals to the state on a set annual cycle multiple times a year. Those dates change from year to year depending on the dates the calendar committee set for the coming year so I added a "SchoolYrDates" table (see below)

I run queries against the "current" dates and trigger reports for the year so obviously there can only be one "current" date set. But there are instances where the team needs to dig back in time for a report so my thinking was they could simply change the "current" flag to an earlier set... which does not feel right to me... so the question is two fold.

1. If I continue using a flag to mark the current date set, how should I ensure the ONLY one record set of all the future date record sets is flagged as current?
2. Is there a better way to do this other than flagging the current set of dates by using a "Current Yes/No" field?

 

[plog]

1. With great difficulty. You could write a script that checks to make sure only one CurrentYear is Yes. You could add a new table and put the ID of the CurrentYear in there and remove the CurrentYear field--but that still requires you make sure that table only has 1 record.

2. By SchoolYear.

Instead of relying on the CurrentYear field to filter your data, use SchoolYear and enter the school year you want to query.

 

[Garren.Shannon]

Well, there are quite a lot of views from the managers point of view so having them put in a date for every report would make them crazy. Is there a way to set a constant in Access that can be set and changes once in a while but normally loads every time you open the database? That way, I could have them pick from a combo-box and set that in a constant.

Wait... that would just be a "constants" table that I limit access to right?

 

[plog]

I usually use a Reports Menu. One drop down for the report to run, one drop down for the SchoolYear and a button. Click the button and it opens the selected report to just the selected SchoolYEar.

 

[Garren.Shannon]

OK... got it. That makes sense.

 

[Garren.Shannon]

Can a table in Access be set to allow updates but not add records? Or would I do that in a form and always set the current record to 1?

 

[plog]

No. You could make an unbound form and simply UPDATE the date in your table.

 

[Garren.Shannon]

Thanks PLog... I very much appreciate the input. I will look into that.