Headcount Report (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Thanks Hans and Wendall. It worked great!!

Reply | Quote