Produced November 2013
Updated 2022 (Coming Soon)
In 2013, I was participating in an elk study project that was assessing the impacts of the construction of a new natural gas extraction plant in the natural habitat of an elk herd. One of the impacts that we wished to evaluate was the effects of vehicles accessing the plant area. Previously, the project used paper logs to track types of vehicles that entered the construction area, but this was very tedious and required an enormous amount of time to review and aggregate into meaningful information. This became the genesis of the idea for this tracking database.
The original database application included a simple database design. There were four things that needed to be logged with each visitor: the visitor’s name and company, the type of vehicle, their destination, and the date/time they were admitted through the gate. The design included four tables as seen in Figure 1 and they are CompanyName, Visitors, LogVisitors, and VehicleLog. The CompanyName table is a store house for the companies involved in sending visitors to the compound and has a one-to-many relationship with the Visitors table. The Visitors table stores the name of visitors that visit the compound and, as mentioned above, is related to the CompanyName as each visitor must be related to a company in order to visit the compound. Because a visitor may visit the compound multiple times and in different vehicles, the Visitors table has a lookup table, LogVisitors, that relates it to the central table of the database, the VehicleLog table. This table logs each visit and the date/time that it occurs.
The database used a switchboard menu to access the forms and reports used in this application. The switchboard was built to be the main operating interface for the database and was set to open when the application was started. The switchboard allowed access to most parts of the database including the main event, logging vehicles entering the compound, but also forms to enter visitors, companies, and manual logging of vehicles as well as printing reports and exporting the logging data to Excel format. Figure 2 shows the switchboard interface for this database.
The main purpose of this database was to log visitor information as the gate to the compound and that was accomplished by using the main logging form accessed by clicking on the “Log Vehicles” button on the switchboard. The “Log A Vehicle” form, as seen in Figure 3, allows a user to admit a visitor to the compound by logging the company, vehicle type, and destination for the visitor. On this form the company name is stored in a table and accessible through a data bound combobox, and the vehicle type as well as the destination field are constrained to a hard-coded list entered via their property attributes. To log in a visitor, the operator simply needs to click the “In” button and the time is automatically logged in the database. The same is true for logging out a visitor, the operator simply needs to click the “Out” button. This is accomplished by using VBA code that is activated with the buttons’ OnClick events, see figure 4. The VBA essentially adds the date and time to empty data bound fields on the form that insert the values into the database once the record is saved. When the user is done logging visitors in the form, they can click the “Done” button to return to the main menu.
As an alternative, an operator can use the “Vehicle_Logging_Manual” form to enter the same information. While the default logging form was meant to streamline the logging of visitors to the compound, it also brought out the need to have a method to enter the logging information in addition to a manual method of entering the entry time and date. If an operator needed to make “offline” entries, from a paper log or such, then this application needed to accommodate that. Therefore, this form added the date, Time In, and Time Out fields so that a user could manually enter the information. Also note that a record only stores a time for the In, or the Out fields. These data were stored in the database in different rows as that was a requirement for the project.
One other form was available for operators to add authorized companies that would enter the compound. This form was a quick function of simply typing in the name of the company and clicking the “Save Company Name” button to add the name to the database. This version of the database only stores the name of the company the visitor represents as a record. Figure 6 shows the “Add a Company Name” form as it appears in the application.
Reports
Three report types were included with this application: Complete Vehicle Log, Daily Summary, and a Vehicle Time Range Report. As part of the requirements there needed to be a report that would essentially “dump” the vehicle log to a report. All visitors and their vehicles logged through the application would appear in this report and its design is shown in Figure 7. This was meant to mimic the old manner of logging visitors on a paper form.
The Daily Summary report is very similar to the Complete Vehicle Log but it is limited, as it says, to the vehicles entering the compound on the given day. This report relies on a query that limits the data reported to the vehicles entering the compound on the reporting day. The advantage of this report is the purpose of the entire application, and that is the ease of use for the operators at the gate, and for those reporting on the study. The design of this report is shown in Figure 8.
The final report included in this application is yet another variation on the theme of listing the visitors and their vehicles to the compound. This report had only a slight difference in how the attributes are formatted and it allows for a time/date constraint to query the database for the vehicles admitted to the compound in a date range. The design of this report is shown in figure 9.
In conclusion, this database application was a simple and quickly put together tool to allow users to log visitors to a construction compound. It also provided a manner for us to analyze the possible effects certain vehicles had on wildlife in the compound by giving us times and dates that the different types of vehicles would enter the compound.