Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Additional Requirements

Discussion in 'Information Technology' started by NoviceIan, Jul 28, 2006.

  1. NoviceIan

    NoviceIan
    Expand Collapse
    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
     
  2. Loading...


  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    NoviceIan wrote:
    > 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.

    --
    Joseph Meehan

    Dia duit
     
  4. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    "NoviceIan" <NoviceIan@discussions.microsoft.com> wrote in message
    news:F91F1DB4-8BF1-48C9-A746-FD23D81B2225@microsoft.com...
    > 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
    >
     
  5. NoviceIan

    NoviceIan
    Expand Collapse
    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

    "Arvin Meyer [MVP]" wrote:

    > 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
    >
    > "NoviceIan" <NoviceIan@discussions.microsoft.com> wrote in message
    > news:F91F1DB4-8BF1-48C9-A746-FD23D81B2225@microsoft.com...
    > > 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
    > >

    >
    >
    >
     
  6. NoviceIan

    NoviceIan
    Expand Collapse
    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

    "Joseph Meehan" wrote:

    > NoviceIan wrote:
    > > 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.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  7. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    "NoviceIan" <NoviceIan@discussions.microsoft.com> wrote in message
    news:4CE7A92F-7388-43DF-97B9-8335F29E82C4@microsoft.com...
    > 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
    >
    > "Arvin Meyer [MVP]" wrote:
    >
    > > 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
    > >
    > > "NoviceIan" <NoviceIan@discussions.microsoft.com> wrote in message
    > > news:F91F1DB4-8BF1-48C9-A746-FD23D81B2225@microsoft.com...
    > > > 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
    > > >

    > >
    > >
    > >
     

Share This Page