Database Application in Microsoft Access for equipment tracking in the automotive industry ...

120 Pages·2015·4.34 MB·English

by  João Fernandes

Database Application in Microsoft Access for equipment tracking in
the automotive industry

João Miguel Moreira Fernandes

Dissertation to obtain the Master of Science Degree in

Industrial Engineering and Management

Supervisor: Prof. Fernando Henrique de Carvalho Cruz

Examination Committee
Chairperson: Prof. Paulo Vasconcelos Dias Correia
Supervisor: Prof. Fernando Henrique de Carvalho Cruz
Member of the committee: Prof. José Manuel Costa Dias de Figueiredo

June 2015
Abstract
In the present case, the author has done his internship at a Metal Plant in Portugal. This Plant produces
metal frames for the automotive seating segment. The author describes the company at first in order to
allow the reader to understand the dimension of the company and one of its core businesses which is
the automotive seating.
This Plant has close to 300 equipments that can be considered eligible to be tracked, according to the
company’s rules. A challenge to track the selected equipments was proposed by the multinational
company itself.
An approach of the concept of maintenance in the past and its evolution till now is presented to the
reader, in order to make the connection to the fundamental part of this dissertation, the creation of the
application in Microsoft Access. Also an introduction to the evolution and use of the robots in the
industries nowadays is addressed and its importance of being followed (monitored) which may be
indispensable to the success of some industries.
A description of the solution is then included and further explained in this dissertation. This description
may turn out to be very useful to help others interested in the development of a similar Microsoft Access
application to the one in this dissertation.
Key words: Access, VBA, maintenance, application, equipments, tracking

I
Resumo
No presente caso de estudo, o autor realizou o seu estágio numa fábrica de metal designada por Metal
Plant em Portugal. A fábrica produz estruturas metálicas para o segmento de assentos automóvel. O
autor descreve numa primeira instância a empresa de modo a que permita ao leitor perceber a
dimensão da empresa e um dos seus principais negócios que é o segmento dos assentos automóvel.
A fábrica mencionada possui perto de 300 equipamentos que podem ser considerados elegíveis para
serem seguidos, tendo em conta as regras da empresa. O desafio de seguir todos estes equipamentos
foi proposto pelo grupo da multinacional.
É abordado o conceito de manutenção e a sua respetiva evolução até aos dias de hoje, por forma a
fazer uma ligação à parte fundamental desta dissertação, a criação da aplicação em Microsoft Access.
Também é introduzido o tema sobre a evolução dos robots e a sua utilização nas indústrias de hoje em
dia, assim como a importância de seguir (monitorizar) os mesmos pode ser indispensável para o
sucesso de algumas indústrias.
Uma descrição aprofundada da solução sobre a forma de como fazer um seguimento dos
equipamentos, está incluída na dissertação. Esta descrição pode ser muito útil para outras pessoas
interessadas em desenvolver uma aplicação com um propósito similar ao desta dissertação.
Key words: Access, VBA, manutenção, aplicação, equipamentos, seguimento

II
Acknowledgements
I’ll gladly take this opportunity to gratefully acknowledge the assistance of some who had faith and have
contributed to the creation and development of this work by having the patience to answer all my arising
questions throughout the year. Susana Franco, Paulo Silva, Juliana Silva, João Fernando and Carlos
Gonçalves.
My supervisor, Fernando Henrique Cruz who gladly accepted to supervise my work even though the
hard circumstances to meet.
To the ever-faithful and lovely family, who have supported me over these years and through the final
stage of my academic journey.
For last, special thanks for a special person, who helped me to finish this long project and had the
patience to encourage me to do a better job everyday.

III
IV
Contents
Chapter 1 – Introduction .......................................................................................................................... 1
1.1 – Project and motivation ................................................................................................................ 1
1.2 – Definition of the problem under study ......................................................................................... 1
1.3 – Thesis Methodology and Outline ................................................................................................ 2
Chapter 2 – The Multinational ................................................................................................................. 3
2.1 - Presenting the company ............................................................................................................. 3
2.2 - Frencia Automotive Seating and the future ................................................................................. 4
2.3 - Frencia Metal Plant ..................................................................................................................... 5
2.3.1 - Organization inside the plant ............................................................................................ 5
2.3.2 - Frencia Excellence System (FES) ..................................................................................... 6
Chapter 3 – State of the Art ..................................................................................................................... 7
3.1 - Introduction .................................................................................................................................. 7
3.2 - The Maintenance ......................................................................................................................... 9
3.3 - The history of Equipment Management and its types ............................................................... 11
3.3.1 - Introduction ...................................................................................................................... 11
3.3.2 - Phase 1: Breakdown Management ................................................................................. 11
3.3.3 - Phase 2: Preventive Maintenance .................................................................................. 12
3.3.4 - Phase 3: Productive Maintenance .................................................................................. 13
3.3.5 - Phase 4: Total Productive Maintenance ........................................................................ 15
3.3.6 - Phase 5: TPM with Predictive Maintenance................................................................... 21
3.4 - Computerized Maintenance Management System (CMMS)..................................................... 21
3.4.1 – CMMS definition .............................................................................................................. 21
3.4.2 - Examples of CMMS systems in the market ................................................................... 23
3.5 – Database .................................................................................................................................. 25
3.6 – Pareto Chart ............................................................................................................................. 26
Chapter 4 - The Equipment Care in Frencia ......................................................................................... 28
4.1 – Production tracking ................................................................................................................... 28
4.2 – Equipment Care in Frencia Group ............................................................................................ 29
4.3 - The equipments in the Equipment Care.................................................................................... 32
Chapter 5 – The Application in MS Access Platform ............................................................................ 36
5.1 – Brief Introduction ...................................................................................................................... 36
5.2 - The Application principles ......................................................................................................... 37
5.2.1 - Split Database Architecture ........................................................................................... 38
5.3 – The Application ......................................................................................................................... 41
5.3.1 - Introduction of Data Form (Introdução de dados) .............................................................. 43
Part A – Combo and text boxes (including calendar) and fast introduction buttons........... 44
Part B – Subforms: Introducing and summarizing records .................................................... 49
Part C – Monitoring the records introduced ............................................................................. 52
Part D – Buttons panel ................................................................................................................ 55
V
5.3.2 - Breakdown Progress (Evolução Avarias) ...................................................................... 56
5.3.3 - Cycle Time (Tempos de Ciclo) ........................................................................................ 58
5.3.4 - Pareto NTRS ..................................................................................................................... 59
5.3.5 - Pareto of Pareto (Pareto do Pareto) ............................................................................... 65
5.3.6 - Monitoring production (Consulta de Produção) ........................................................... 67
a) Monthly Production (Produção Mensal) ........................................................................... 67
b) Diary Production (Produção Diária) .................................................................................. 68
c) Diary Production by reference (Produção diária por referência) ................................... 68
5.3.7 - Engineering (Engenharia) ............................................................................................... 69
a) MTTR ..................................................................................................................................... 70
b) MTBF ..................................................................................................................................... 71
c) Maintenance Team Waiting Time ....................................................................................... 73
5.3.8 – Exit the Application (Sair da Aplicação) ....................................................................... 74
Chapter 6 – Results ............................................................................................................................... 75
Chapter 7 – Conclusions and future work ............................................................................................. 77
References ............................................................................................................................................ 79
Appendix ................................................................................................................................................ 81

VI
List of Tables:
Table 1: VBA code to check day selected by user ................................................................................. 45
Table 2: Week day and number functions ............................................................................................ 47
Table 3: Fast introduction button TOP5 ................................................................................................ 48
Table 4: 1,1- Menu Principal subformulário1 VBA Events associated .................................................. 50
Table 5: Tempo Útil (Useful Time) calculation ...................................................................................... 51
Table 6: Combo box Requery ................................................................................................................ 52
Table 7: Calculation of Minutos disponíveis (available minutes) by shifts ........................................... 54
Table 8: VBA code in Breakdown Progress Chart Avarias sem códigos ................................................ 58
Table 9: VBA code to automatic giving the 1st value ............................................................................. 60
Table 10: VBA code to transfer data between text or drop boxes ....................................................... 60
Table 11: VBA code to assign a value to a text box ............................................................................... 60
Table 12: VBA code to activate a Delete Query .................................................................................... 62
Table 13: VBA code for global function to calculate cumulative percentage ....................................... 63
Table 14: VBA code to initiate global variables ..................................................................................... 63
Table 15: VBA code to set a value to global variables ........................................................................... 63
Table 16: VBA code Transform date in weeks ....................................................................................... 71
Table 17: VBA code to differentiate Opening Time by shift .................................................................. 72
Table 18: VBA code to Exit the Application ........................................................................................... 74

VII
List of Figures:
Figure 1: Frencia sites worldwide (About us: Frencia, 2014) .................................................................. 3
Figure 2: Seat ergonomically adapted, synched with smartphone (Market: GT Demo, 2015) ............... 4
Figure 3: Prototype composite seat example with reduced weight ....................................................... 4
Figure 4: Frencia organizational structure .............................................................................................. 5
Figure 5: Worldwide annual supply of industrial robots 2003 – 2013 (IFR) (Baroncelli, 2014) .............. 7
Figure 6: Estimated worldwide annual supply of industrial robots (2010 – 2013) (Baroncelli, 2014) .... 8
Figure 7: MTBF, MTTR, MTTD, MTTF .................................................................................................... 15
Figure 8 - The pillars of TPM (Borris, 2005) ........................................................................................... 18
Figure 9: General View of Production Lines (solutions: agilitec, 2015)................................................. 24
Figure 10: Production Tracking Sheet ................................................................................................... 28
Figure 11: TPM cycle according to Frencia ............................................................................................ 32
Figure 12: Equipments classification in Equipment Care according to Frencia .................................... 32
Figure 13: Access application architecture (Front-End 1st type) .......................................................... 39
Figure 14: Access application architecture (Front-End 2nd type) ......................................................... 40
Figure 15: Microsoft Access Application relationships ......................................................................... 41
Figure 16: Main Menu Access Application (Front-End 1st type) ............................................................ 42
Figure 17: Main Menu Access Application (Front-End 2nd type) ........................................................... 42
Figure 18: Form Introduction of data subdivided in four parts (A, B, C and D) .................................... 43
Figure 19: Header of the form in ACC2003 (part A from old version in Appendix 11) ......................... 44
Figure 20: Header of the form in ACC2010 (part A from Figure 18) ..................................................... 44
Figure 21: MSCAL.Calendar.7 ................................................................................................................ 45
Figure 22: Microsoft Access 2010 calendar ........................................................................................... 46
Figure 23: Week day and number (from Figure20) ............................................................................... 46
Figure 24: Wrong shift selection ........................................................................................................... 47
Figure 25: Message box about wrong day selection ............................................................................. 47
Figure 26: Part B from Introduction of data in Figure 18 divided in 3 subforms .................................. 49
Figure 27 – 1,1- Menu Principal subformulário1 (subform 1 in Figure 26) ........................................... 49
Figure 28: D01- Somatório NTRS Menu Principal subformulário1 (subform 2 in Figure 26) ................ 51
Figure 29: 1,6- Tabela de Peças (subform 3 in Figure 26) ..................................................................... 51
Figure 30: Indicators of Part C (Figure 18) in ACC2010 version ............................................................ 52
Figure 31: in ACC2003 version............................................................................................................... 52
Figure 32 - Message box about time superior to Opening time ........................................................... 53
Figure 33 - All the indicators hidden version ACC2003 and ACC2010 .................................................. 53
Figure 34: Shift Opening time................................................................................................................ 54
Figure 35 - Indicators with negative values from Part C (Figure 18 ) in ACC2010 version .................... 55
Figure 36: Panel Button to access other forms ..................................................................................... 55
Figure 37: Form Introduction of data blocked ...................................................................................... 56
Figure 38: Breakdown Progress Form ................................................................................................... 56
Figure 39: Breakdown Progress Chart example .................................................................................... 57
Figure 40: Cycle Time Form ................................................................................................................... 58
Figure 41 - Cycle Time table 2,0- Registo de TC .................................................................................... 58
Figure 42: Pareto NTRS Form ................................................................................................................ 59
Figure 43: Pareto NTRS Form ................................................................................................................ 59
Figure 44: Pareto NTRS - Valores Form ................................................................................................. 61
Figure 45: G01- Pre consulta Ref Cruz - Temporary table created by query ........................................ 62
Figure 46: VBA code to call Glabal Function in query ........................................................................... 63
Figure 47: Pareto NTRS chart example .................................................................................................. 64
VIII
Figure 48: Pareto of Pareto Form .......................................................................................................... 65
Figure 49: Grouped Query option from Pareto of Pareto ..................................................................... 65
Figure 50: Pareto of Pareto chart .......................................................................................................... 66
Figure 51: Historical chart from regarding a NTRS cause ...................................................................... 67
Figure 52: Monitoring Production Form ............................................................................................... 67
Figure 53: Monthly Production chart .................................................................................................... 68
Figure 54: Diary Production chart ......................................................................................................... 68
Figure 55: Diary Production chart by reference .................................................................................... 69
Figure 56: Engineering Form ................................................................................................................. 69
Figure 57: MTTR chart example ............................................................................................................ 70
Figure 58: MTTR pre final query ............................................................................................................ 70
Figure 59: MTTR final query .................................................................................................................. 71
Figure 60: MTBF chart example ............................................................................................................ 71
Figure 61: MTBF final query .................................................................................................................. 73
Figure 62: Maintenance Waiting Time chart ......................................................................................... 73
Figure 63: Breakdown Progress (code 4.2 - Robot door) ...................................................................... 75
Figure 64: Breakdown Progress (code 4.1 - Operator door) ................................................................. 76

IX