Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Wink Unanswered: Where would I put calculations?

    Hi,

    Not only am I new to this forum, I am also new to Access!

    Firstly I am running Access 2003. I went on a course 2 years ago but this is the first time I have started creating a database so please bare with me.

    I am creating a database for staff holidays/public holidays/maternity leave etc within a unit which has subdivisions.

    I have created a table called Staff, that has staff details i.e. employment #, name, job title, subdivision name, holiday outstanding balance and public holiday outstanding balance.

    I have created a second table which gives the employment #, hoiday year, holiday entitlement and public holiday entitlement. This table I've connected with a one to many relationship.

    I have created a third table with employment #, subdivision name, holiday dates from and to, # of days and type of leave. I don't know where to connect this table too!

    My ideal end result would be when data is entered for a holiday request you can cascade from employee details to dates requested and then cascade down to see who else is off within that subdivision at that time.

    How would I get table 3 to calculate taking one date from another, knowing how many days have been requested and entering it into the 'No of Days' column? Then, depending on the 'Type of Leave' requested deduct the 'No of Days' from the yearly entitlement and enter the balance into the 'holiday outstanding balance' or 'public holiday oustanding balance' if it applies, in table 1? And, thereafter deduct the 'No of Days' from the outstanding balance in table 1 leaving an uptodate 'outstanding balance'?

    I hope this doesn't sound too confusing. I must admit my brain is turning to mush trying to work it out I have a niggling doubt in the back of my mind telling me I'm trying to do this the wrong way round!

    If anyone can point me in the right direction I would really appreciate it.

    Many thanks for your patience and kindness.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can't have formulas or calculations in tables. They belong in queries, forms, reports or code.

    Since you haven't figured out how to relate table 3 to other(s), you shouldn't really even be thinking of calculations until you get your table design right. For instance, you may not need number of days if you have a start date and an end date. Those balances might not be needed either; you may be able to recalculate the balance from other data.

    Sorry, but I think you are trying to do too much too soon in tables.

    Why did you wait 2 years to start!? That effectively means you have NO training You might like to consider repeating the course? It might save you a lot of stress in the long run
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2009
    Posts
    5
    Hi Startrekker,

    I do try to run before I can walk at times, thanks for keeping me on the straight and narrow

    You're absolutely right I'll redo the course and then focus on getting my table design right.

    I did my ECDL to help get me back to work after bringing up my family. I hadn't been working, office based, for 16 years and only got back to work last year, hence the delay in writing a database. However, when I did the course I thoroughly enjoyed it so I'm determined I'll get this database written and working well.

    Thank you again for your wise words

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You are most welcome

    If you have any questions come back and ask!

    ECDL = Enabled Computer Driver's Licence??
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2009
    Posts
    5
    Enabled Computer Driving Licence

    Interesting guess.........but no............its European Computer Driving Licence.

    Thanks for the offer of help

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Wow! I was close!

    I hope they never bring in licenses for computer usage here... I'd be sooo offended since I've been using and teaching computers since the XT came out.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jan 2009
    Posts
    5
    Yes you were very close

    There's also another one, ICDL, which is the International one

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I would never have thought anyone would ever need a licence to use a computer... I mean there are some people who are dangerous with a computer, but extorting them to pay money for a license isn't going to help much.

    Do you have to pass tests to get these licenses?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jan 2009
    Posts
    5
    It's not as bad as it sounds.

    You don't need the Licence to use the computer. It's a proficiency qualification in the use of the Microsoft Office programme. There are 7 modules to the ECDL and you have to pass each one before you are awarded the certificate.

    A lot of employers over here are asking for this qualification if you apply for an office based job.

    As you can tell, I've been the best advert for the Access Module

    But what it does prove is - If you don't use it, you lose it!

    Although my husband would say I never had it in the first place

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    LOL!

    Ok, so there is some half decent education behind the licence, so it has value.

    Strange name for it though... I guess it's a better name, less offensive, than something like "I am not a computer moron certificate" ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •