Additional Requirements

[Guest]

Hi,

I've got a complex problem with an existing system. We have a staff
database which was designed three years ago and has been adjusted
relentlessly since its implementation.

Currently we book annual leave for staff using an absence form which is
based on the absence table. This is a generic form which is used for all
types of absence. We hold a total number of annual leave hours in the
staff's personal details form. Using this A/L total and a total of hours
missed generated from the absence table we have a report which details all
annual leave episodes for any staff member for the current tax year then
calculates how many taken, how many remaining etc.

This method has served us well over the last few years, however I've been
asked to alter the system so that we can look at past annual leave reports
more accurately. As the current system only stores the current year’s
total. There are obvious flaws here, but like I say it was not an initial
requirement.

I have no idea how I can possible group the annual leave episodes by
multiple tax years. I understand that an Annual Leave table will need to be
created to store details of each years leave allowance. But I have no idea
how to use the totals that would be generated from this table in conjunction
with the absence table.

I realise this is quite a big problem and that I have not given you much
detail but just wanted to get somebody else' opinion on the situation.

I would be very grateful for any comments.

Ian

 

[Arvin Meyer [MVP]]

Add a single column (named something like ALYear) to your table. I'd just
add a text column 4 characters long, and set the default value to:

=Year(Date())

If you use a Fiscal year, you'll need a function and need to use it a bit
differently (query based form) to fill in the data automatically. Post back
and I'll provide a Fiscal year function for you.

Now just add a column and a Where clause to each report recordsource:

Select * FROM MyTable
WHERE ALYear = "2006";

If you anticipate reports spanning multiple years, use a date field instead
of a text field and set its default value to:

=Date()
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Joseph Meehan]

Hi,

I've got a complex problem with an existing system. We have a staff
database which was designed three years ago and has been adjusted
relentlessly since its implementation.
....
As the current system only stores the current year's total.

I was not able to understand your current system or problems, but this
above line may well be the reason for the problems.

Storing calculated data is almost always a bad idea. I don't know if
this case if that is true, but it usually so.

Deleting data is also usually a bad idea. It is usually better to just
flag it as prior period so you can use it as needed, and no matter what
people think, they will need it exactly 30 seconds after it is deleted.

 

[Guest]

Hi,

Thanks for your response. You made it seem a lot easier then i originally
feared. However I am stil a little confused. I understand why I should add
a year colum in the absence table with the default value.

Could this log the Fiscal year? We are anticipating reports spanning
multiple years

 

[Guest]

Hi,

Thanks for your response. Yes I realise that only storing the current years
allowance is the root of the problem. I may not have been very clear but, we
do not store calculated values, however we do generate them for reports and
queries and to perform further calculations.

We never delete data however the A/L allowance may change from year to year.
What was meant is that the stored reports that we have for annual leave
presently only work on this tax year. The data from previous tax years is
stil stored however due to the problem you recognised these reports arnt much
good because the leave may differ from one year to the next.

What we want to do is change the annual leave setup so that past allowances
can also be stored and as a result produce more accurate historic reports.
Not sure if thats much clearer.

Ian

 

[Arvin Meyer [MVP]]

So for a Fiscal Year, you'd use a function like:

Function GetFY(dtmDate As Date, intFMonth As Integer) As String
' ©Arvin Meyer 9/27/1998
On Error Resume Next

Dim intMonth As Integer
Dim intYear As Integer

intMonth = Month(dtmDate)
intYear = Year(dtmDate)

If intMonth >= intFMonth Then intYear = intYear + 1

GetFY = Str(intYear)

End Function

in a standard module, and then call it in a query column like (for a Fiscal
Year beginning in July:

FiscalYear: GetFY([DateField],7)

and the query would look like:

SELECT MyTable.*, GetFY([DateField],7) AS FiscalYear
FROM MyTable
WHERE (((GetFY([DateField],7))="2006"));
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access