Landfill Database

Part 1: Creation of Database with Mentorship

My first experience with Microsoft Access. It started with entering data from the Landfill and Transfer Stations into Microsoft Excel, then it evolved to migrating our Excel sheet into a fully functional database on Microsoft Access. I also implemented new procedures in our overall data entry process.

• Click here to scroll to a PDF that has all the pictures of the Microsoft Access Database
• Next – Part 2: Managing the database Independently

This is separated into 2 parts. Part 1 (this page) talks about how this project and my involvement started; it shows the original Excel spreadsheet where we entered data; early phases of organization such as reporting bugs within the database; and shows the database in general. Part 2 discusses the new system I implemented in the overall data entry process.

• Location: Orange County Government Utilities Department, Solid Waste Division
• Position: Student Intern
• Our team consisted of: 3 interns, 1 supervisor
• Our team would receive daily physical paper reports from the Landfill and Transfer Stations. On these reports they would track:
  • Employee tasks/productivity
  • Equipment’s employees used
  • Number of loads being transferred from the Transfer Station into the Landfill
  • Number of loads being moved around the Landfill
• Each of us interns would get assigned a stack of these reports (the stacks vary by location from which we received it)
• Purpose of creating Microsoft Access Database: Our Division Manager wanted us to migrate our Excel spreadsheet to Microsoft Access that way we could build and deliver reports to other supervisors.

• May 2019, Jean-Marc Denis (I.T. Department) was responsible for of the creation and migration of the database. Originally there was only one intern at these Microsoft Access meetings with Jean-Marc until I asked my supervisor if I could also attend. I didn’t have prior coding experience at all and I thought this would be a good way to learn.
• Jean-Marc didn’t like using Macros so everything was coded through VBA.

• In meeting: We met once a week, and the first few months were dedicated to data entry for our tables such as, employees, equipment, equipment types, task types, etc. Then we would test the skeleton of the database Jean-Marc created. During our meetings, Jean-Marc would just build the database and explain to us what he was doing and why; we sat behind him taking notes and telling him what our data entry process was. Due to my natural curiosity , if there was something I didn’t understand, I didn’t hesitate to ask for a further explanation, and thankfully Jean-Marc was happy to answer them.
• Post-meeting assignments: When our hour meeting was over, he would assign us with a little Access task for us to attempt on our own, and I LOVED this!!!! At some point I would just start experimenting on my own or I would print out a screenshot of the form and write in potential additions we could incorporate.
• What I didn’t think would happen is me falling in love with coding. I love that coding is like a big strategy game. All I wanted was to know more; I couldn’t get enough of it.
• In December 2020, my supervisor decided that the other intern didn’t need to attend the meetings, as their focus was directed toward other tasks, while I had taken a more active role in this project.

I created a bug reporting sheet for our team to report bugs within the database. This would act as the agenda for our Microsoft Access meetings.

I created this detailed flowchart in able to implement a new feature that would further streamline the process. At this point, we are entering data from 3 different locations. The Landfill and Transfer Station as mentioned previously, and we also added Shop reports (where I eventually made a separate Microsoft Access database independently). More about this flowchart in Next – Part 2: Managing the database Independently

Next – Part 2: Managing the database Independently

Part 2: Almost 2 years on Access & Working Independently

• Previous: [Part 1] Landfill Daily Operations: Beginning and with Guidance
• Spot-checking Error Sheet (Excel)
• Introducing Stack ID’s
• Flowchart displaying Spot-Checking Process (Access)
• Before and After

Spot-Checking and Tracking Errors

• What is Spot-Checking?
  • When an intern is done entering data and turns in the stack of reports to our supervisor, our supervisor gives the stack to a different intern to go through and make sure it was entered correctly, and if it wasn’t we needed to return the incorrectly entered papers for them to correct.

• If there was an error found by the spot-checker, the spot checker would either verbally or write down on a sticky note what was entered incorrectly
• One of the most annoying things about spot-checking was having to search each employee individually to find their record.
• Cons of this method (for me personally): I have bad memory to verbally didn’t work and I hated wasting sticky notes

• I created an excel sheet that helped me organize all the errors and this would subsequently make it easier to make corrections and to ensure the error is corrected.

• My supervisor loved the idea, then asked me to add fields to track the dates received by each person. After the revision, the interns and supervisor began to use this excel spreadsheet to track spot-checking errors.

• When COVID hit, we started working from home which meant that we brought multiple stacks of paper with us. In effort to save paper and time, I started handwriting my own type of ID for each stack and would combine the stacks together so I could input them in alphabetical and date order. My supervisor did not like this and told me to keep them in the stacks I originally received them to ensure consistency.

• My supervisor at some point thought it’d be best for her to have her own sheet where she could also track all of the errors and the dates being corrected (Figure 2.4)

• My supervisor created these cover sheets for each stack to show the report type, quantity, date range, and people assigned.
• This was created on Microsoft Word and was printed out.

• My supervisor would keep track of the Stack Cover Sheet data (Figure 2.5) on her own spreadsheet, but there were a few instances where she would claim that we didn’t turn in a stack and would make us shuffle through all of the cover sheets. Annoyed, for accountability sake, I decided that there’s definitely a way to spot-check on Access, but I knew it would take a while.
• 5/6/2021, I released version “v110” with everything they needed at that time and I got to working on v111.

• I started with mapping out the process we usually go by:
  1. My supervisor receives a stack from landfill/transfer stations/shop.
  2. Supervisor assigns it to one of the interns.
     • Intern enters the data > return stack to supervisor.
  3. Supervisor assigns a spot-checker.
     • If there are errors found by the spot-checker, the supervisor returns it to the data enterer.
• I used Microsoft Visio and mapped out the whole process (Figure 2.6), the values that need to be recorded, actions needed to be taken by the users, and the relationships between the forms and sub-forms.

Creation of: Stacks Form

• This form is designed for the supervisor to assign the data entry and spot-checking tasks.
• When she assigns the data entry task, she would click ‘Print Cover Sheet‘ (changes to ‘View Cover Sheet‘ if that data is already entered). (Figure 2.7)
  • In Figure 2.7, the items in the red boxes are different elements of the Stack Form.
  • Figure 2.8 opens when ‘Print/View Cover Sheet‘ is selected, which is directly referencing my supervisors original cover sheet (Figure 2.5)
• Every Stack that our supervisor assigns to us will now have an ID associated with it (Stack ID). This means that after the data enterer enters the stack, the spot-checker will only have to select the associated Stack ID and it will pull up every record for the stack saving us SO much time.

Before & After: Stack Cover Sheet

Click and drag vertically to reveal the before and after of the original cover sheet and Microsoft Access generated cover sheet