Shows the # of Openings, Hires & Cancellations by Req

[wanderlust]

Shows the # of Openings, Hires & Cancellations by Req

Hi Guys,

I am new to this board, so please let me know if my question is not clear. I am working with a system that will be decommissioned in 9 months, but I have to deal with it for now, hence my questions.

I am using 2 excel spreadsheets to load a 2003 Access database which I created to facilitate reporting. The db is not used for updates. The spreadsheets are reports I get daily from a separate recruiting system. (This system that will be decommissioned in 9 months, but I have to deal with it for now, hence my questions.)

Report 1 - Open Positions Report: Each line has a Req ID, Report Date, and # of job openings. The Req ID is a unique identifier for each Job Requisition. Each Req ID can have 1 or more openings.

Req ID
Report Date
Open Positions
624051
09/02/10
24
624051
09/03/10
21
624051
09/06/10
19
624051
09/09/10
15
624051
09/27/10
15
624051
09/28/10
15
624268
09/15/10
29
624268
09/16/10
29
624268


09/17/10
29


Report 2 – Hire Report - I get another report that shows hires by Hire Date, by Req ID. For each hire on this report, the number of openings on the Open Req report will go down.

Req ID
Hire Date
Candidate ID
624051
9/2/2010
754329
624051
9/2/2010
754331
624051
9/2/2010
754350
624051
9/3/2010
754400
624051
9/3/2010
754407
624051
9/8/2010
754421
624051
9/8/2010
754433
624051
9/8/2010
754439
624051
9/8/2010
754448


The Open Positions can go up or down by
1. A Candidate is hired (which will show up on Report 2)
2. The # of Open Positions are increased or decreased in recruiting system
(If the # of Positions are increased by 1 and there is 1 hire, it will appear that the number of positions is unchanged. Since this does not happen much, we are not going to worry about it).

The Req ID will stop appearing on Report1 if the Req ID has been cancelled or if there are no more openings. I assume it has been cancelled if the req is not listed on Report 1 for the associated dates.

My Mission: I’d like to be able to generate a report that shows the number of Openings, Hires and Cancellations by Req ID, by date. I have tried to import these 2 spreadsheets into an Access Database to help reporting, but need some help on how to design/structure the tables.

Please let me know if you have any ideas.

[wanderlust]

The tables lost their formatting so here they are as .gif

[slave138]

Your graphic didn't make it either...

[wanderlust]

See attached .gif