Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Headcount Report (Access 2000)

    I'll try to make this as simple as possible...so here goes...

    Every month I need to generate a headcount report. The data is sent to me via excel and I load it in using access. The table has name, ss#, salary, status, and reason for leaving if status is T. In any case, I can easily create the monthly headcount report using the current table but my problem is, I need to compare last months table with this month table. After the compare, I have to report who isn't on this month's report and the reason why. Does anyone know how I can accomplish this?

    It would really be helpful.

    Thanks in advance.
    <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Headcount Report (Access 2000)

    In order to help, we need to know whether the missing people records are actually deleted or given a special status of some sort. The latter makes it easier, the former would require running a left-join query against some sort of archive table to find out who got deleted. There might be other schemes to get a count, but if you need a reason, then you should probably be leaving records in the Excel workbook, and specifying a reason for change in status. Hope this helps.
    Wendell

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Headcount Report (Access 2000)

    The data I am given is a listing of ONLY active employees for the current month. So unfortunately, I have to go back to last month's list and check who's missing on this month's list. Then on a separate sebreport, I need to list the people that are not on the current month's list. It's basically a termination list.

    I hope this makes sense...but let me know if you need further information.

    Thanks!
    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Headcount Report (Access 2000)

    If the data for the current month and for last month are in different tables, you can use the Find Unmatched Query Wizard to create a query that returns all people on last month's list that are not in the current list. You would probably use the Social Security Number as linking field, because it's a unique identifier.
    Use the query created with the help of the Wizard as record source of the subreport.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Headcount Report (Access 2000)

    Hans beat me to it, but his response is right on.
    Wendell

  6. #6
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Headcount Report (Access 2000)

    Thanks Hans and Wendall. It worked great!!

Posting Permissions

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