Embedded Files
The frontend application built in Microsoft Access serves as the cornerstone for data entry, modification, and management within the movie production companies database. It is designed to facilitate a wide range of operations from basic data insertion to complex data analyses, even mere navigation through the database, all through a user-friendly interface called Forms. The application leverages a series of SQL queries and Access Design to fetch, display, and process data, enabling users to gain insights into various aspects of the business environment in which these companies operate.
Connecting MySQL to Microsoft Access
A connection between Microsoft Access as a Data Entry application is needed to interact with a MySQL database and to do this, I installed the MySQL/ODBC Connector from the official MySQL website version 8.0.11. I went on to Add a new User DSN in the ODBC Data Sources (64-bit) app where I set up a new Data Source with the MySQL ODBC 8.0 Unicode Driver after which I was led to a MySQL Connector / ODBC Dialog box
Fun Fact: I wrote majority of the Stored Procedures for Data Manipulation mostly addition and deletion routines before I realized how easier it can get with Microsoft Access.
I went ahead to fill in the boxes with details of the MySQL movie_production_companies Database which I intend to use as the Data Source when connecting with my Microsoft Access and the connection was successful.
In Microsoft Access, I located the taskbar and followed these steps:
External Data >> New Data Source >> From Other Sources >> ODBC Database >>
Link to the data source by creating a linked table >> Machine Data Source
Here I was able to locate the Data Source I initially created in the MySQL/ODBC Connector. A dialog box containing a list of all the tables in my Database appeared and I selected all then clicked ‘Okay’. The tables linked and Microsoft Access as a frontend application for Data entry into MySQL database was a success.
Creating Forms
Here, I employed Forms as user interface objects that provide a way to interact with and enter data into my database. I used it to create a custom data entry and viewing experience for users, making it easier to input, modify, and navigate through records.
When using Microsoft Access as a frontend to interact with a MySQL database, the relationships between tables are typically defined and maintained on the MySQL server side. Microsoft Access will recognize and respect these relationships when you link the MySQL tables to your Access database so I had no need to create Relationships between tables from the scratch.
Utilizing Combo Boxes
Combo boxes played a crucial role in the forms, enhancing data entry efficiency and accuracy. They were particularly useful in fields requiring selections from a predefined list of options, such as department names, role titles, or country names, city names.
Dynamic Data Population: Combo boxes were connected to the database, dynamically populating options based on existing records. This ensured data integrity and minimized entry errors.
Simplified User Selection: By providing a dropdown list, combo boxes made it easier for users to select the correct item without memorizing specific codes or titles.
Cascading Combo Boxes: In some instances, the selection in one combo box determined the available options in another, guiding the user through a logical data entry flow.
Text Boxes for Direct Input
Text boxes were used for fields requiring free text input, such as company names or addresses. They provided the flexibility to enter unique data that was not limited to predefined options. There are also disabled text boxes that will not allow any form of input, useful just for display alone.
Subforms for Related Data
Subforms enabled the inclusion of related data directly within a primary form. This was especially useful for:
Phone Numbers: Employees could have multiple contact numbers, categorized as Mobile, Work, or Home. The subform allowed for the entry of multiple phone records linked to a single employee.
Staff Salaries: For employees identified as staff, the subform facilitated entering their salary details, including working hours and job level.
Employee Form
Below is a Video of how the employee form works and its relevance in simplifying data entry, update, and record navigation. Things to note:
Buttons - Buttons were stategically placed in the form to simplify activities like searching for a record, refresh, save, close form.
Auto-generation - fields like ID, Email Address, Phone are auto-generated which confirms what I said earlier about how events defined and maintained on the MySQL server side reflect in the frontend as well.
Conditional Visibility of Subform - The visibility of the staff salary information subform is dependent on whether the employee is Staff. It disappears if the employee is Crew, because crew members typically receive payment based on the movies they have worked in which can be found in the crew_info table.
Employee Form.mp4Company Form
Below is a Video of how the company form works and its relevance in simplifying data entry, update, and company record navigation. Things to note:
Buttons - Buttons were strategically placed in the form to simplify activities like searching for a record, refresh form, save form, undo action, delete record, add new record, next record, previous record, first record, last record, close form.
Auto-generation - fields like ID are auto-generated which confirms what I said earlier about how trigger events defined and maintained on the MySQL server side reflect in the frontend as well.
MS Access events - Microsoft Access After Update events allows for 3 text boxes (country, registration body, country_code) to update as soon as a city is selected. Check out the VBA section to explore the Visual Basic written for this purpose.
Company form.mp4Creating Queries
Queries were created to streamline tasks and retrieve specific subsets of data. They were used both to support backend processes and to provide users with quick access to reports or data summaries.
The following are queries that played vital parts in creating forms:
city_country: This query, specifically made for the company form, is the backbone of the functionality that retrieves information about a city's country and registration body of that country, if a city is selected. It performs an inner join between the city, country & registration_body tables based on the country_code field in the city & registration_body tables and the code field in the country table.
SELECT city.id, city.name, country.code, country.name, registration_body.name
FROM ((city INNER JOIN country ON city.country_code = country.code)
INNER JOIN registration_body ON country.code = registration_body.country_code);
employee_roles_union: This query combines the list of names from both the department and role tables into a single list, without any duplicates. It is primarily used for populating a dropdown combo box in the employee form where a user might need to select a department or role, regardless of the categorization.
SELECT department.name
FROM department
UNION SELECT role.name
FROM role;
UI (Queries + Form)
UI is an overview application that aims to aggregate and display detailed information about companies in a form. This includes not just basic details like name and address, but also more specific data such as the number of employees, company registration details, shareholder info, grant overview, number of film productions, financial information.
Query Design
The queries are engineered to provide a comprehensive overview of the companies, incorporating details about company demographics, shareholder engagement, employee distribution, film production involvement, even a company's grant success rate. This holistic view aids in understanding the operational scope and financial health of the companies. Three key queries serve as the foundation of the application, each designed to fetch a distinct set of data:
root - This query is designed to provide a comprehensive view of each company's core information, including their geographic location, organizational type, the body in charge of their registration, financial metrics, and employee count.
SELECT
company.name,
company.address,
company.zip_code,
city.name AS cityname,
registration_body.name AS registrationbodyname,
kind_of_organization.name AS kindoforgname,
country.name AS countryname,
company.total_asset,
company.total_liability,
company.registration_date,
COUNT(employees.id) AS EmployeeCount
FROM
((((company INNER JOIN city ON company.city_id = city.id)
INNER JOIN country ON (city.country_code = country.code) AND (company.country_code = country.code))
INNER JOIN kind_of_organization ON company.kind_of_organization_id = kind_of_organization.id)
INNER JOIN registration_body ON company.registration_body_id = registration_body.id)
LEFT JOIN employees ON company.id = employees.company_id
GROUP BY
company.id,
company.name,
company.address,
company.zip_code,
city.name,
registration_body.name,
kind_of_organization.name,
country.name,
company.total_asset,
company.total_liability,
company.registration_date;
root_ui - This query is crafted to gather specific counts of crew and staff members associated with each company in the database, illustrating the workforce composition in terms of operational roles, thereby providing insights into the operational structure and human resources distribution.
SELECT
company.id,
company.name,
(SELECT Count(*) FROM crew INNER JOIN employees ON crew.crew_id = employees.id WHERE employees.company_id = company.id) AS crewcount,
(SELECT Count(*) FROM staff INNER JOIN employees ON staff.staff_id = employees.id WHERE employees.company_id = company.id) AS staffcount
FROM company
GROUP BY company.id, company.name;
root_ux - This query provides a comprehensive overview of the relationships and activities associated with each company in the database, focusing on shareholder participation, film production involvement, and grant application outcomes.
SELECT
company.id,
company.name,
(SELECT COUNT(*) FROM company_shareholder WHERE company_shareholder.company_id = company.id) AS shareholdercount,
(SELECT COUNT(*) FROM company_film WHERE company_film.company_id = company.id) AS filmcount,
(SELECT COUNT(*) FROM company_grant WHERE company_grant.company_id = company.id) AS grantcount,
(SELECT COUNT(grant_request.id)
FROM grant_request
INNER JOIN company_grant ON grant_request.id = company_grant.grant_id
WHERE company_grant.company_id = company.id AND grant_request.status = 'Approved') AS approvedgrants,
(SELECT COUNT(grant_request.id)
FROM grant_request
INNER JOIN company_grant ON grant_request.id = company_grant.grant_id
WHERE company_grant.company_id = company.id AND grant_request.status = 'Pending') AS pendinggrants,
(SELECT COUNT(grant_request.id)
FROM grant_request
INNER JOIN company_grant ON grant_request.id = company_grant.grant_id
WHERE company_grant.company_id = company.id AND grant_request.status = 'Denied') AS deniedgrants
FROM company;
Significance of these Queries
Strategic Insights: By breaking down company activities into quantifiable metrics, such as the number of films produced, shareholder counts, and grant application statuses, the queries offer strategic insights that can inform decision-making processes. This includes assessing company performance, identifying growth opportunities, and understanding market positioning.
Operational Efficiency: The frontend utilizes these queries to streamline operations, reducing the time and effort required for data management tasks. Users can easily navigate through different sections of the database, thanks to the relational data structure and the optimized queries that link various tables seamlessly.
Enhanced Decision Making: The ability to quickly access detailed and categorized information about companies, employees, and their projects enables users to make informed decisions. Whether it's evaluating the success rate of grant applications or analyzing employee distribution across departments, the queries empower users with the data needed to make strategic choices.
Customizability and Scalability: The queries also offer a level of customizability and scalability, accommodating the ever-evolving needs of the database. As new companies, films, or shareholders are added, or as new roles and departments are introduced, the queries can be adjusted to incorporate these changes, ensuring the frontend application remains both current and comprehensive.
Form Design
Form Design
The form's design centers around providing users with an intuitive way to navigate through and interact with data concerning movie production companies. It utilizes combo boxes (to select a company), and text boxes alone (to display company information) to present a summary as it pertains to a particular company.
The form's design was guided by the need to provide a comprehensive summary that supports accessibility to company information which will in turn support decision-making processes. Key considerations include:
User Experience: Ensuring the form is intuitive and easy to use, with clear labels, and logical grouping of information.
Data Integrity: Implementing mechanisms, such as validation rules in combo boxes and text boxes, to maintain the accuracy and consistency of the data entered into the form.
Performance Optimization: Minimizing the form's load time and improving responsiveness by optimizing the underlying queries and the way data is fetched and displayed.
Ui form.mp4Unfortunately, at this time, I am ineligible for joining the Microsoft 365 Developer Program hence unable to create a SharePoint app to help users explore this database more :(
Page updated
Google Sites
Report abuse