Landfill Daily Operations Microsoft Access 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.

Highlight Color	Excel (Old Method)	Access (New Method)
Orange	Had to manually enter	Access automated
Yellow	Had to manually enter	Only have to enter once per record

Orange	Excel (Old Method)	Access (New Method)
Intern Name	Enter in manually	The log-in screen keeps the value of a user and is entered into its respective table if the user is entering, editing, or spot-checking records
Asset Type	We would write down the asset number the employee wrote on the form, then we would have to go on a different sheet where all of the asset numbers and equipment names were listed, then we would have to write in the equipment name.	Asset Number is a drop-down menu on the form; when an asset number is typed in or selected, the equipment type already has a relationship with the asset number (through tables), so we don’t need to type it in, it’s automatically recorded
Daily Task – Item #	Enter in manually	Automated by Access; stores value of task sequence to table as the task is saved
Total Odometer/Miles	Enter in manually (we had a certain formula we needed to stick with for certain equipment)	Automated on Access

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