Results 1 to 6 of 6
  1. #1
    Hediru is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    8

    Exclamation Create a report tracking employee turn-over

    I'm a novice at Access, and mostly self-taught. I work in the management office for a local small grocery store chain that unfortunately has a high turnover rate. My employer would like me to create a report that shows the monthly turnover by store. I know it is possible, but I am at a loss as to how to begin. I know there will likely be many queries involved.

    The final report should look like this:

    Store
    Month
    Total Employees at the Start of the Month, Total Employees at the End of the Month, Net Change
    Next Month.... etc.
    Next Store... etc.


    The table that stores the information has the following fields that will have to be used: (I tried to do a screenshot of my table in design view but couldn't figure out how to paste it here. The info after the colon is the data type)
    Emp_ID: Number
    Emp_Name: Text
    Store: Number (look-up field that connects the store number to the store name)
    Date of Hire: Date
    Date of Resignation/Termination: Date



    I've contemplated creating tons of queries calculating the number of employees lost and gained during each month for each store and using that data to create multiple sub-reports to create the final report, but I cannot help but think that there has got to be an easier way! Please help
    Last edited by Hediru; 11-18-2016 at 10:38 AM. Reason: Clearer Title

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I don't see a ton of calcs...maybe 12.
    create a 'report' table to hold your results.
    create Q1, to count empIds with hire date inside Jan, and Term dates in Jan., post to rpt tbl.
    Q2 does Feb,
    etc to Q12

    You could also use a form to set the date limits ..start Date,EndDate.
    and the queries could run off that.

  3. #3
    Hediru is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    8
    Thanks for your quick response! So, would I make 1 query for each month for each store then (there's 5)? And then can I assume that I would run the report off of the rpt tbl?

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, count the :
    employee ,store, employedInMo, TermInMonth

  5. #5
    Hediru is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    8
    Thanks! You've been very helpful. One last question. How do I calculate the number of employees who worked in the store in a certain month? What's the formula?

  6. #6
    Hediru is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    8
    Nevermind. I figured it out. Thanks again for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-01-2015, 02:27 PM
  2. Calculated field in Query not working!!! :( I made a video
    By SebastianColombia in forum Queries
    Replies: 3
    Last Post: 07-28-2015, 07:02 PM
  3. Replies: 6
    Last Post: 04-07-2015, 03:41 PM
  4. Replies: 2
    Last Post: 12-15-2014, 04:53 AM
  5. Totals of Calculated Fields in Report Footer
    By rrciesla in forum Access
    Replies: 2
    Last Post: 12-13-2014, 01:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums