Overview
This project involves the design and implementation of a robust database management system tailored for business operations with a large semiconductor OEM. The system integrates automation, analytics, and reporting features to streamline business processes.
I am the sole developer and designer of this project. I was responsible for the full lifecycle of development from defining user requirements and designing database architecture to constructing the automation, UI design, and API integrations. I also managed the data migration, application deployments, conducted testing and ensured performance optimization using DevOps methodology.
Note: Information in this document may have been altered or fictionalized to protect sensitive and confidential information.
Issue Tracking
Added ability to quickly mark parts into issue categories to define issues and standardize the process to handling them.
This in turn reduced the need to ask so many questions and leave things up to speculation on what to do with them, reducing time wasted and possibility of commits being missed because they were assumed to be handled by someone else.
Created custom views for management to be able to see all parts with active issues so that they can quickly be addressed without needing to rely on employees to bring awareness to the issue.
Sample of code used to automatically pull information and create email object.
‘ Ensure a vendor is listed
If (IsNull(Combo.Value)) Then
MsgBox "Enter Vendor ID"
Else
Dim objOutlook As Object
Dim objMail As Object
Dim MyTime As String
Dim PartNumber As String
Dim signature As String
Dim dtToday As String
dtToday = Date
dtToday = Replace(dtToday, "/", "-")
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.Display
End With
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vendorName As String
Dim quoteEmail As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM VendorsT WHERE VendorsT.ID = " & VendorID)
If Not rs.EOF Then
vendorName = rs!VendorName
‘ Ensure information
If rs!Quote <> "" Then
quoteEmail = rs!Quote
ElseIf rs!Contact <> "" Then
quoteEmail = rs!Contact
End If
Else
MsgBox "No record found for VendorID " & VendorID
End If
rs.Close
Set rs = Nothing
Set db = Nothing
signature = objMail.HTMLbody
‘ Set the greeting
Select Case Time
Case Is < TimeValue("12:00")
MyTime = "Good morning, "
'Set rngBody = .Range("B3")
Case Is < TimeValue("16:00")
MyTime = "Good afternoon, "
'Set rngBody = .Range("B3")
Case Else
MyTime = "Good evening, "
'Set rngBody = .Range("B3")
End Select
‘ Select the appropriate email
With objMail
If Not (IsNull(quoteEmail)) Then
.To = quoteEmail
ElseIf Not (IsNull(ContactEmail)) Then
.To = ContactEmail
Else
.To = ""
End If
‘ Select the appropriate part numbers
PartNumber = ""
If Not (IsNull(VendorPNBox)) Then
PartNumber = VendorPNBox
If Not (IsNull(MFRPNBox)) Then
PartNumber = PartNumber & " / " & MFRPNBox
End If
ElseIf Not (IsNull(MFRPN)) Then
PartNumber = MFRPNBox
End If
Dim AMATNum As String
AMATNum = AMATPNBox
.cc = "example@applied.com"
.Subject = vendorName + " New RFQ " + dtToday + " " + AMATNum
.HTMLbody = "BODY style=font-size:11pt;" + MyTime + "Can you please quote the following with your MOQ and all available price breaks? (Please include all of the requested information for this first time quote, needed for part qualification)Part: " + PartNumber + "Desc: " + DescriptionBox + "Price: ?MOQ:?COO:?Standard MFR LT:?HTS:?ECCN:?RoHS/REACH Compliant: Yes or No" + vbNewLine + signature
Shipping
Integrated our stock report from SAP with customer information to create views that include information merged from multiple systems. This enables us to quickly generate reports based on customer inquiries for part availability or forcasting.
The system now automatically generates shipping reports that are sent to our shipping team showing never before linked views of our stock and orders that are ready to ship or those which are in demand by our customer but require action on our end.
Sample of code used to extract some of the information from files to migrate them to the new system.
Sub ImportCellValueToTable()
Dim objExcel As Object
Dim objWorkbook As Object
Dim objWorksheet As Object
Dim strFolderPath As String
Dim strFileName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim filePath As String
' Initialize target cells
Dim TargetMFR As String
Dim TargetNAME As String
...
' Initialize cell value variables
Dim cellValueMFR As Variant
Dim cellValueNAME As Variant
...
' Folder path containing Excel files
strFolderPath = "\\file.global\secure\data\branch\PDFs\Vendor Covers\"
' Specify the target cell to extract (e.g., "A1")
TargetMFR = "A22"
TargetNAME = "b22"
...
' Set up the database and recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("inputtempt", dbOpenDynaset)
' Loop through each Excel file in the folder
strFileName = Dir(strFolderPath & "*.xlsx")
Do While strFileName <> ""
filePath = strFolderPath & strFileName
' Open the Excel file
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filePath)
' Set the worksheet to the first sheet (adjust if needed)
Set objWorksheet = objWorkbook.Worksheets(1)
' Extract the value from the specified cell
cellValueMFR = objWorksheet.Range(TargetMFR).Value
cellValueNAME = objWorksheet.Range(TargetNAME).Value
...
' Add the extracted value and filename to the table
With rs
.AddNew
!FileName = strFileName
!MFR = cellValueMFR
...
.Update
End With
' Close the workbook and clean up
objWorkbook.Close False
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
' Get the next file
strFileName = Dir
Loop
' Clean up
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Data extraction complete!"
End Sub
