Results 1 to 15 of 15
  1. #1
    Robbo11 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    6

    Building a good foundation for a database

    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.


    1. 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?)
    2. 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.
    3. 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
    Attached Files Attached Files
    Last edited by Robbo11; 01-04-2020 at 11:23 PM. Reason: added details

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Each client should absolutely NOT have own table.

    Forget what you know about organizing data in Excel.

    Begin by identifying data entities and how they relate. So far I see entities of Clients, Projects, Drawings. That's 3 tables for a start. Then you need 'junction' tables that associate these entities.

    Yes, forms should be used for data entry/edit. Users should not interact with tables and queries.

    If this is a multi-user database, it should be split and frontend placed on each user machine.

    Checking a tick box for selection of record can be a complicated procedure in a multi-user db.

    Have you studied an introductory book on Access and/or relational database concepts?

    Have you web searched topic of "Access database CAD drawings"?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Robbo11 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    6
    Thanks June7 for the advice.
    What you say about data entities makes sense and after watching a few more YouTube videos (my main learning platform ) I realised that each client as a table was not the way to go. Your suggestion for Clients, Projects and Drawings tables makes sense to me.
    I need to investigate these junction tables you have mentioned as well, I suspect these will be the missing link
    The database will likely be multiuser so I need to look into splitting the database as well.
    Thanks again

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    I'll try and answer you questions
    first thing I do is make a short note of what it is I want to achive

    Look here I haven't started this project but I may spend another week or two going over it
    https://databasedreams.createaforum....-design/msg286
    I would create a clients folder when each client when added then I would add a folder for each project when created they are easy to find then.

    This info below gives you a good way to com up with a design you need to think beyond excel
    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.)
    tblClients
    tblProjects
    tblProjectDrawings
    Etc

    Don't use Hyperlinks you can use text boxes as there are a number function for openning files
    Hyperlink to the drawing folder it lives in
    Hyperlink to the drawing itself within its folder

    This project would be ideal in access you have a learning curve but you are in the right place

    good luck with your project.

    mick

  6. #6
    Robbo11 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    6
    Thanks for the help so far. I’ll spend some time planning the layout with the help of your ideas, make a start and likely come back with more specific questions.
    Thanks again.

  7. #7
    Robbo11 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    6
    Hi

    My number of tables has grown somewhat and I am hoping someone may be able to see if I am on the right track.

    I now have the following tables

    Principles (each project will have 1 Principle and each Principle can have a number of projects so 1 to Many)
    Clients (each project will have 1 Client but each Client can have a number of projects so 1 to Many)
    Sites (each project may have several sites and each site may have several projects so Many to Many)
    Projects
    Documents
    Document Type (each document can only be one type so 1 to Many)
    Status
    File Type

    The aim of this project is to be able to output a report for drawings / documents based on any of the following
    Principle
    Client
    Site
    Project
    Type
    Revision

    Am I on the right track? My main problem at this stage is around relationships and I suspect I need a junction table for Sites and Projects but I want to make sure I understand the 1 to many vs many to many relationships.

    Do I need the Principle_ID_FK and Client_ID_FK in the document table or will having them in the Project table be all I need.

    I am still looking through threads, online videos etc. as I like to learn as I go rather than be handed the answer but I am stumped at the moment.

    Any input, ideas, criticism is appreciated.
    Attached Files Attached Files

  8. #8
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    I'm just about to run out the door to work so will have a pop at this later

    What are Principles And please explain what you company does I.E Builds housing estates or sngle dwellings or both Plus, plus I think you may be over complicating it I'll read the first post again later.

    mick

  9. #9
    Robbo11 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    6
    Thanks Mick, no great hurry and much appreciated.

    The Principle is typically the end user. Sometimes we will work directly for the Principle and other times we will work for a contractor (our client) who in turn works for the Principle. I hope I’ve explained it well enough

    I produce electrical engineering designs, drawings, schedules etc and as time has progressed, keeping track of these documents has gotten too difficult with just a spreadsheet.

    There is every chance I have over complicated things, it’s what we engineers do and I’m more than happy to be shown a less complicated way to do this.

    Thanks again, much appreciated.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some ideas from this free document management data model from Barry Williams' site.
    There are other document related models at the site that may be useful.
    It would help readers, and I'm sure would benefit you, if you could describe a day or week in your "business" that shows the who, what, why, when, how much and how often sort of scenario that links/relates the subject matter in clear terms. Have you mocked-up some outputs, questions or queries, searches that you can test against your evolving data model?
    Good luck with your project.

  11. #11
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    So your Principle and client are one and the same I would keep them in the same table you could include a type for say client, contractor Etc.

    Those schemas posted by Ornange above look like they would do what you need.

    EDIT: you should note I was a contracts manager for an electical firm back in the 90's so have had a few dealings with drawings lol

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I took a peek at your dB....... Soooooo, here is my 2 cents.

    In my table designs, in virtually every table, I use an Autonumber as the primary key field. The PK field has a suffix of "_PK". Likewise, the foreign key field has a suffix of "_FK" (obviously it must be a long integer).
    Here is why I do this:

    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

    Articles
    1) Microsoft Access Tables: Primary Key Tips and Techniques
    2) Relational Database Primary Keys
    3) Autonumbers--What they are NOT and What They Are




    Quote Originally Posted by Robbo11 View Post
    <snip>My main problem at this stage is around relationships and I suspect I need a junction table for Sites and Projects but I want to make sure I understand the 1 to many vs many to many relationships.<snip>
    Maybe something like this???
    Click image for larger version. 

Name:	Presentation1.png 
Views:	18 
Size:	120.1 KB 
ID:	40618
    Again, not sure about the difference between Principals and Clients.
    In table "jnctProjectSites", I would set a unique compound INDEX with the fields ClientID_DK, ProjectID_FK and SiteID_FK.



    Attached is a modified version of your dB.
    It does not have the junction table, just showing how I might/would design the tables using my naming convention and using autonumbers.


    I'm late - gotta run.


    Note: unlike Mickjav, I was never made it to a manager level. But I have had lots of experience reading P&IDs (oil field piping and instrumentation diagrams)
    So take the above with a few grains of salt......


    Good luck with your project........
    Attached Files Attached Files

  13. #13
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    Looks like ssanfu Hit the nail no the head that should do the job nicely

  14. #14
    Robbo11 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    6
    Thanks all. I’ll have another crack today and report back.
    Again, appreciate the clues and I am learning a lot which is as important as the end result.

  15. #15
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255

    Richiesta

    In the 2190100 Drawing Register_D.xls file you should explain what columns B and C represent.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Design database - is it the good approach ?
    By jaryszek in forum Access
    Replies: 9
    Last Post: 10-02-2017, 03:11 AM
  2. Replies: 10
    Last Post: 11-16-2016, 07:59 AM
  3. My Database... Isn't very good!
    By DavidMcArthur in forum Database Design
    Replies: 9
    Last Post: 06-03-2016, 02:19 AM
  4. Want to know if this looks like a good database design
    By tmcrouse in forum Sample Databases
    Replies: 7
    Last Post: 09-28-2014, 08:46 PM
  5. Is this a good database design?
    By Someday in forum Database Design
    Replies: 4
    Last Post: 07-22-2012, 06:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums