Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Employee Anniversaries--Separate into months (Access 2000)

    I have a feeling that this is pretty basic, but this morning it has me stumped. Could someone please advise me how to query a table for employees' anniversaries. The two fields in question will be "EmployeeName" and "StartDate". We would like to see a list from January to December with employees listed under the appropriate month with their actual start date. Appreciate any guidance anyone can give me. Many thanks...Mary

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

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Start by creating a query based on the employees table.
    Add the EmployeeName and StartDate fields.
    Add a calculated column:

    Anniversary: DateSerial(Year(Date()),Month([StartDate]),Day([StartDate]))

    This column will display the date of the anniversary for this year for each employee.
    Save this query.

    Next, create a report based on the query. You can use the report wizard to create it. When it asks for grouping levels, specify the Anniversary field, then click Grouping Options... and specify group by month. In the next step, specify how you want to sort the records within a month - by name or by anniversary. The rest is standard. You will probably want to adjust the design afterwards. Of course, you can also design the report from scratch.

    Attached is a screenshot of the result for the Employees table from the Northwind database.
    Attached Images Attached Images
    • File Type: png x.PNG (4.6 KB, 0 views)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Many thanks, Hans! You are my hero! I just did as you outlined and it is exactly what is needed. May I ask you another question--if we want to see how many years the employee has been employed on his anniversary date, what should the calculation field look like to get the exact number of years, e.g., John Doe's anniversary is July 6, 2004 and his StartDate was July 6, 1996, which I think = 8 years. Once again, many thanks for your quick response and clear guidance....Mary

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

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Add another calculated column to the query:

    <code>YearsEmployed: Year(Date())-Year([StartDate])</code>

    You can also use (with the same result):

    <code>YearsEmployed: DateDiff("yyyy",[StartDate],[Anniversary])</code>

    Put a text box bound to this field in the detail section of the report.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Brilliant! Thank you so much, Hans! I really appreciate your expertise and willingness to help me out! Sincerely, Mary

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Thank you, Hans! The calculation examples for years of service works great, except that when I run the query & report for either one, I get a

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

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Have you named the calculated column Anniversary, as in my first reply:

    Anniversary: DateSerial(Year(Date()),Month([StartDate]),Day([StartDate]))

    If you have given it a different name, yet refer to Anniversary elsewhere, you'll get a parameter prompt. Also check carefully for typing errors - the slightest difference will throw Access off.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Hello, Hans...I have checked and double checked by copying field names and overwriting in the calcuations, but I still get the parameter prompt. But like I said, when I click OK on the prompt (without entering anything), the query results are correct--all records show the column reflecting the result (years of service). For the time being, I'm just going to ignore the prompt, as the results are what we need anyway. Thanks again for your help!...Mary

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

    Re: Employee Anniversaries--Separate into months (Access 2000)

    I'm afraid I have no further ideas without seeing the database. If you like, you can post a stripped down copy of the problem database. See <post#=368161>post 368161</post#> for instructions. That would allow Loungers to look at the problem directly.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Thank you, Hans...When I get a chance I just may do that, as I'm curious as to why it is happening. My boss asked if we could just pull up one month at a time for the anniversaries, e.g., she might only want to see anniversaries coming up in August. The Anniversary calculation works great for one report reflecting all 12 months with relative employees' anniversaries under each month. Can you please tell me what I need to do to either have user enter a month as a parameter value, or select from the 12 months. Many thanks!...Mary

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

    Re: Employee Anniversaries--Separate into months (Access 2000)

    Add a calculated column to the query:

    <code>TheMonth: Month([StartDate])</code>

    In the criteria line for this column, you can enter a parameter:

    <code>[Enter Month Number (1-12)]</code>

    Or, you could put a combo box on a form from which the user can select a month, and use criteria like this:

    <code>[Forms]![frmSelect]![cboMonths]</code>

    where frmSelect is the name of the form, and cboMonths the name of the combo box.

Posting Permissions

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