Hi there
I have the need to track my engineering drawings in a more efficient way and I believe Access may be the best tool for this. I have no experience developing an Access database but I do know my way around Excel and VBA for Excel quite well and I want to make sure I head off in the right direction before getting too involved.
My project folders are laid out by year, then by project number and description then 10 subfolders within this folder, folder 08 being my drawing folder which contains Master, Superseded and Work in Progress folders e.g. T:\\2019\2190066 – Client Name – Project Description\08 Drawings\Master
At present, my drawings are tracked via spreadsheets, one project per spreadsheet (two samples attached for reference) which are saved within the 08 Drawings folder. One of my problems is that to find the drawings I need to remember the project number or search my folders for keywords which is not particularly efficient. A problem also arises when a new project requires existing drawings to be amended and one set of drawings ends up being split across several projects, again, not efficient at all. It gets even more complicated when I work on two different stages of a project for two different clients under two different project numbers.
I am hoping for some guidance on the best way (I know this is subjective) to put together my database. Information that will be included as fields will be
Principle (End User, often the same as the Client but not always)
Client (Each client has a unique identifier e.g. ROB01)
Project Number (Each project has a unique identifier e.g. 2190066)
Drawing Number (Each drawing has a unique drawing number e.g. 2190066-MCC1-E4-001)
Drawing Description 1 (e.g. PU-002 – Potable Water Pump)
Drawing Type (i.e. Schematic Diagram, Termination Diagram etc.)
Revision Number (Updated with each new drawing revision)
Status (e.g. Issued for Construction)
File Type (e.g. PDF, DWG etc.)
Hyperlink to the drawing folder it lives in
Hyperlink to the drawing itself within its folder
I want to do it as a progressive 3-part project.
- Build the database and update with all existing drawing information (I have an Excel spreadsheet that I can get all of this from) allowing for searching for drawings based on client’s name, project description, drawing type, keywords etc. I can then click the hyperlink to the drawings folder or the drawing itself. New drawings to be added as needed via a Form (is this the best way to do this?)
- Add email distribution lists for each project so I can tick a checkbox for each drawing I want to send externally, a drawing transmittal is then automatically created based on these selections and sent to Outlook along with the drawings (may need to be Zipped first) ready to be sent on to the recipients.
- Generate reports such as all drawings for a particular client between two dates or by project number etc.
I guess my first question is, is the above achievable in Access and how should I build my database? Should each client (there may be in excess of 200 clients) have their own table which is then populated with Principle, Project Number, Drawing Number, Drawing Description 1, Drawing Type, Revision Number, Status, File Type etc? Would the primary key then just be an Autonumber?
I am happy that I will be able to build the database itself, I want to make sure I set it up in a way that allows me to scale it and add functionality as the need arises.
Thanks in advance for any insights.
edit: each client may have anywhere between 1 and 2000 drawings spread across between 1 and 5 projects