Results 1 to 10 of 10

Thread: Creating Table

  1. #1
    Join Date
    Nov 2004
    Posts
    23

    Unanswered: Creating Table

    I'm trying to create a table in MS Access called Employees Attendance which should show Employee NID #, Date, Day of the Week, time in, time out, total Normal hours worked, Overtime, Hourly Rate, Gross Wages.

    I'm having two problems:
    1. Puting a formula in Gross Wages column that will calculate total hours worked times hourly rate.
    2. I'm having problems arranging the fields so that this info will show day by day attendance for a week for each employee.

    Any help/suggestions are greatly appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    1) I would not store the Gross Wages value in a column of the employees table. You may however, want to store derived values in log style tables.
    2) Your table structure does not need to be changed to accomplish this. Instead, a cross-tab query can be used.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Latoya
    I'm trying to create a table in MS Access called Employees Attendance which should show Employee NID #, Date, Day of the Week, time in, time out, total Normal hours worked, Overtime, Hourly Rate, Gross Wages.

    I'm having two problems:
    1. Puting a formula in Gross Wages column that will calculate total hours worked times hourly rate.
    2. I'm having problems arranging the fields so that this info will show day by day attendance for a week for each employee.

    Any help/suggestions are greatly appreciated.
    Hi Latoya,

    First let me say "Welcome to the Forum
    Now, for your calculated column, you do that in either your Query or Form.
    In your form you have 2 TextBoxes, one for HoursWorked and one for HourlyRate.
    NEXT, you create an Unbound TextBox to hold the calculation for GrossWages. When done you should have something that looks like this:

    (HoursWorked) (HourlyRate) (=[HoursWorked]*[HourlyRate])

    First two parenthesis are the TextBoxes representing each field.
    Last parenthesis calculates both of those 2 TextBoxes to give you the GrossWages.

    Awww, what the heck, I attached a small sample database below to show you what I am talking about. When you open it a form will automatically open. Go to FormDesignView and look at the TextBoxes to see what I did. As stated you can also do this in your Query but this seems simple enough. It is in Access 2000 version. Oh...btw...to make it easier for others to help you it is sometimes best to make a copy of your database, strip out all sensitive data and replace with dummy data, Zip it with WinZip or something and attach it here, as I just did. That along with an explanation of your problem gets you the quickest help at times.
    Hope you enjoy the forum as it's a great place to be.

    Hope this helps you out some
    Have a nice one,
    BUD
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    HiBud,
    I'm self taught (well, self teaching), the problem here is that i sometimes miss the basics, how did you create the form that adds a new row but keeps the completed one visible?? also is it possible to keep adding rows but not move the record # up (i guess keep it in the same row.
    I have a medical form where you fill in all the visits a patient has had (it can go from 1-30+) however at the bottom i want to be able to total all the different visits (i know v1 +v2+v3= v_total) is this possible?
    I'd like to do it in the style mentioned above to save space
    Sorry if i need to start another thread i will, but i didn't mean to get this deep into it
    Brownie

  5. #5
    Join Date
    Nov 2004
    Posts
    23
    Thanks for the advice. I will not calculate the wages in the employees table.....however I'm still having a little problem arranging my table fields to show an employee attendance for each week.

    Here is the scenario:
    On a weekly basis the owner of a daycare totals the children's attendance of 40 children and any child attending 2 days or more pay $100 and less than 2 days pay $50. Payment for children are received weekly with a few occasional monthly payments.

    She employs 5 persons who work an 8 hour day @$5 per hour workers are paid weekly and provision is made for statutory deductions of 5% NIS and 10% Income tax.

    I'm new to Access...so my question is What fields would a Children & Employee attendance tables consist of given the scenario above

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    TableA(employee_id, date, time_in, time_out)

    Calculating overtime depends on the business. For example, you may have a separate overtime table consisting of rules for each employee group.

    Hourly rate can be stored in the employee table, but not in tableA.

    The primary key of the table would depend on the business. For example, can an employee register a combination of time-in and time-out values more than once a day?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Nov 2004
    Posts
    23
    Thanks a million Bud. The sample was very useful.

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Latoya
    Thanks a million Bud. The sample was very useful.
    Hi Latoya,

    You're very much welcome. I am just glad that I am able to help in some way. We're all here to help one another. Did the solution by r123456 solve your other problem? For me I would have to actually see the database to possibly work it out. That's just the way that I work best and helps to see the total structure of the program and working out a possible solution. Sometimes I don't have all the answers, but do try hard to find one.

    have a nice day and see ya soon,
    BUD

  9. #9
    Join Date
    Nov 2004
    Posts
    23
    Yes...many thanks to r123456 too ...I used that suggestion for the table structure. I'm still working on the tables....will move on to Forms next.

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Excellent. I see you're on here today also. Cool. I will be in here on and off so just buzz me if you need me.

    have a nice day and wear a Smile

    BUD

Posting Permissions

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