Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing a query (Access 2003)

    I am creating a database to track a time bank. Every employee receives 10 sick days per year. Days not used are accumulated in a bank and when people retire they are paid for days not used. I need a query that will update the "time bank" as of Dec 31 each year. The formula is: total time bank Dec 31 year 1 - sick days used Jan 1-Dec 31 year 2 + 10. This takes the total days in the bank at the end of 2005 (for example) minus the days used during 2006 plus the 10 available for 2006.
    If an employee has 100 days in the bank as of Dec 31, 2005 and used 5 days in 2006 the calculation would be 100 -5 + 10.

    I've tried to a

    If an employee started during the year, then the 10 sick days available will be prorated. I was thinking that I could subtrack the employee's start date from Dec 31, 2006 if the start date is in 2006, but not if it is prior to 2006 divide the result by 365 and multiply this by 10. So the calculation for an employee starting on July 1 will be: (Dec 31, 2006 - July 1, 2006)/365 x 10.

    I'm not sure how to make sure the prorating only happens for employees with prior year start dates--2006 in this case.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing a query (Access 2003)

    Do you want to round the prorated days
    a) Down to the next lower whole number
    [img]/forums/images/smilies/cool.gif[/img] Up to the next higher whole number
    c) To the nearest whole number
    d) Don't round - keep the fractional part

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing a query (Access 2003)

    I have attached a version with a modified query. The calculation rounds days down, but I've included columns that show how to round to the nearest integer and how to round up.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a query (Access 2003)

    Thanks a million Hans.
    I have a couple of questions. 1/ can you recommend a good source for writing queries? I have the Access for Dummies book, and I always use the online help and sometimes it is helpful, but I find just understanding the logic and syntax of the query very challenging.

    2/ can you suggest a simple way to find a record in a database? I'm trying to create a form combining the two tables in my database:
    Employee Set-up and Absence Input with the set-up table being the parent and the Absence Input being the child (sub-form). I've created a query using [Enter Employee Number] as the criteria in the Employee number field on the Employee Set-up table. This works fine. I input an employee number and retrieve the record. I've created the parent form using this query and the sub-form using the Absence Input Table. I've linked employee number in both, but when I open the combined form the [Enter Employee Number] dialogue box does not appear. When I type in the employee number in the employee number field in the parent form section (Employee Set-up) the child information changes, but the parent info stays with the previous record info. So I have the employee number of Bob and Bob's absence information in the subform, but I have John's name, and the rest of his Employee set up fields in the parent form.
    I want both parts of the form to change when the employee number is input in the employee number field in the set up table.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing a query (Access 2003)

    1) Books on the website of MVP John Viescas lists several useful books about Access, among which several about designing queries.

    2) I wouldn't use a parameter query here. Instead, create a main form based on the employee setup table and a subform based on the absence input table. Link them on the Employee Number field in the Link Child Fields and Link Master Fields properties.

    To find an employee, you can use an unbound combo box on the main form. The easiest way to create it is to let the Combo Box Wizard do the work for you.
    - Open the main form in design view.
    - Make sure that the Control Wizards button on the Toolbox is "on" (it is the 'magic wand' button)
    - Place a combo box on the form.
    - Select the third option ("Find a record ..."), then click Next and follow the instructions.

    When you select an employee number from the combo box, the corresponding record will automatically be found, and the subform will automatically display the absences for this employee.

  6. #6
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a query (Access 2003)

    Thanks

  7. #7
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a query (Access 2003)

    Thanks
    The form/sub-form is working using employee number to retrieve records. I need to be able to restrict access to a set of records defined by department.

    I've tried to retrieve groups of records by department using a parameter query and an option group. Neither approach has worked.
    Am I going down the wrong path? Each department will have an admin person update the absence records, and add new employees. I need to restrict their access to their department's records.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing a query (Access 2003)

    You could create a separate form on which the user can select a department from a combo box or a list box. A command button on this form opens the employee main form:

    Private Sub cmdEmployeeForm_Click()
    DoCmd.OpenForm FormName:="frmEmployees", WhereCondition:="Department = " & Me.lbxDepartments
    End Sub

    cmdEmployeeForm: name of the command button
    frmEmployees: name of the main form
    lbxDepartments: name of the list box from which the user selects a department.

  9. #9
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a query (Access 2003)

    Thanks Hans. It's working great!
    Hopefully I won't bug you for awhile.

Posting Permissions

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