Results 1 to 6 of 6
  1. #1
    dpill is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    4

    Question Designing an Access 2016 database for my financial advisory practice

    Hey all -

    WHY I NEED HELP
    I hope this finds you well. I'm a small business owner (financial advisor) and spend my day wearing all employment hats to keep my practice going. After 5 years in the business, I'm at a turning point where my excel based system is too inefficient and taking too much time away from me working with my clients. I'm tech savvy but have little experience in Access. I can handle tutorials and learning the program but I need your help on a database design to match my current workflow so I can effectively use my time when learning the program.

    CURRENT WORKFLOW EXPLAINED
    My current system consists of 7+ excel sheets--all with multiple tabs--and is rife with inefficiency. I've put together a diagram of my current work flow to give you guys a better idea of how my business works. Hopefully this will help guide your suggestions but I'm happy to provide more details if they would be helpful. The diagram can be broken into 3 elements:


    1. The grey boxes represent excel sheets for specific parts of my business
    2. The arrows and text leading to other grey boxes show how clients move through the different sheets and why
    3. The yellow comment sections explain what the excel sheet is used for and what it contains\


    PROBLEMS I'VE IDENTIFIED WITH MY CURRENT SYSTEM

    • I'm in data entry mode too much - there are way too many excel sheets that contain the same data (name, contact info etc.)
    • It's difficult to develop real time reports without spending my day crunching numbers and looking at different sheets
    • I'ts a pain to have 3 different excel windows open to input data on one client
    • Certain sheets do not have up to date information on clients
    • It integrates poorly with other programs
    • I am having a hard time scaling my business because the infrastructure is so fragmented


    WHAT I NEED HELP WITH

    1. Identifying how to integrate my current workflow into access e.g. what tables would you recommend? should I use multiple forms for different purposes? how should I leverage relationships to maintain data integrity?
    2. What macros, queries, etc. would be useful to help with automation?
    3. What areas of my current workflow do you identify that could greatly benefit from Access integration?
    4. Any resources that you would suggest as far as learning to implement your specific suggestions.


    A BIG THANK YOU IN ADVANCE
    I wanted to just say thanks in advance. To most of you, this stuff is second nature and I'm really counting on your experience to help me design a more efficient way for me to work in my business. There are limited hours in the day and I'm feeling SO strapped on time putting in 60 hour work weeks... this is an area I've identified that could be a huge time saver with the right guidance.



    As mentioned above - let me know if I can clarify anything!
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are a number of free data models at Barry Williams site
    You may get some ideas from some of them. They are generic and don't represent a specific company/client.
    They are meant to represent the "typical" situation and serve as a starting pointing. You can add/remove and adjust as it meets your needs.

    Also, I recommend this 8 part video series on Database by Dr. Daniel Soper --- starts here.
    Watch 1,2 and 4 to start.

    Good luck with your project.
    Last edited by orange; 03-16-2016 at 07:40 AM.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    A very wide ranging question.

    Just remember, Access isn't a bigger excel but a different beast altogether and whilst almost certainly more appropriate for your needs requires a different way of thinking.

    1. Excel data is stored wide and short, databases are tall and thin. what tables required? all I can say is the obvious ones - customer, contracts, billing etc. These may be split into further tables - e.g. customers may have customer name/address table, a contacts table, a contact method table, a relationships table etc - depends on your business. However these can all be displayed on one access form, utilising subforms. Learn about normalisation. Data stored in excel is about as far way from normalisation as you can get!
    2. could be anything - much can be done without code if data is normalised
    3. all of it - though excel graphs/pivots are probably more flexible than Access
    4. if you need a start, here is a link - it includes a link to a series of videos which should put you on the right path.

    http://www.access-programmers.co.uk/...d.php?t=285357

    Much as I would encourage people to give it a go themselves, I also feel I need to caution you that it is a steep learning curve which may well take you away from managing your business which may suffer as a consequence. So do consider using a professional and compare their quotations with the true cost of you doing it yourself, how much you are currently losing through inefficiencies, lost opportunities etc. - although working weekends doesn't necessarily cost money - it still has a value to you.

  4. #4
    dpill is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    4
    You guys are awesome. Thanks so much for your input. My current meeting tracker looks like this:
    Click image for larger version. 

Name:	client contact excel sheet.png 
Views:	22 
Size:	6.4 KB 
ID:	24104

    If you cant see the rows they are:
    First name / last name / email / rating / location / 1st meeting set / fact finder completed / graduation date / last contact / status / next contact date / next contact notes / 1st-11th (date stamp of each meeting)

    One thing I was kinda confused on was how to best represent the dates for each of my meetings. Especially if each meeting is different. It doesn't really seem efficient to have an excel sheet like this represented by one table in Access. Is there a way to say "add a meeting" and have it indicate the type of meeting and what the date was and store that tied to the client? Then, after that... is there an easy way to see meetings in a linear fashion?

    I'm not sure if that makes any sense to you guys but I wasn't really sure how to get around this issue and thought I'd ask.

    Thanks again for all of your help and if anyone else wants to weigh in (specifically with ideas on different types of tables and what they'd do in reference to other elements) that would be wonderful!

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.
    if anyone else wants to weigh in
    OK.... well...

    Watch the videos provided in orange's post.
    Re-read Ajax's post - several times.
    Work through the tutorials at http://www.rogersaccesslibrary.com/forum/forum46.html.

    Think about Ajax's last paragraph. I totally agree with him.
    If you decide to develop the dB yourself, you will give up any free time you might have had (for several months, at minimum).

    You will have to forget about Excel while learning about Access. It doesn't matter how you did it in Excel; in Access it is different. For example. in Excel, you can use any word(s) for column names (headers). In Access, you shouldn't use reserved words as object names. Only use letters, numbers and sometimes the underscore in object names. No spaces, special characters or punctuation. Do not begin object names with a number.
    Stay away from look up FIELDS in tables and multi valued fields.

    Read about normalization. It is very important to have a sound table structure BEFORE worrying about forms or reports.

    I have an autonumber field as the PK (primary key) field in almost every table. Stay away from text fields as PK fields.
    Here is more reading:

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm


    Start designing your table structure using paper and pencil, chalkboard, whiteboard, cardboard, sticky notes, etc BEFORE getting into Access.
    It is way easier and you can see the big picture easier.......


    And if you get stuck or have questions, post back here.........


    Good luck with your project.

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Caution - read up and understand normalisation, then apply it before asking questions - otherwise most of your questions will take 3 times as long to answer and the subject is brought up again and again to guide you on the correct path. It is much easier to help someone who has taken some steps and needs confirmation they are on the right path rather than someone who wants others to do all the heavy lifting and then doesn't really understand the solution. Although you have provided a good brief about your business, if this was a consultation, to answer your questions requires many further questions to be asked to help define the table structure.

    As an educated guess your meeting tracker would contain data from other tables and linked together in a query via primary and foreign keys (also known as Parent/Family or PK/FK) i.e. there would not be a specific meeting tracker table to produce the equivalent excel view

    tbl..Fields
    1 First name / last name

    2 / email

    3 / rating

    1 or 6 / location

    4 or 6 / 1st meeting set

    1 or 5 / fact finder completed

    1 / graduation date

    6 / last contact / status / next contact date / next contact notes / 1st-11th (date stamp of each meeting) - one record for each meeting

    In Access you would display on a form or report (you never look at data directly in tables or queries except whist developing). Depending on the required perspective, e.g. customer rings up and you want to see the details - table1 might be the source for the main form with the others displayed in subforms. or perhaps you want to know what you are doing today so table6 would be on the mainform, with 1, 4 and 5 on subforms and 2 and 3 sub sub forms to table 1

    Is there a way to say "add a meeting" and have it indicate the type of meeting and what the date was and store that tied to the client? Then, after that... is there an easy way to see meetings in a linear fashion?
    yes

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

Similar Threads

  1. Replies: 1
    Last Post: 02-23-2016, 11:14 AM
  2. Replies: 7
    Last Post: 12-15-2015, 06:33 PM
  3. Best practice for deploying Database
    By saudkazia in forum Access
    Replies: 4
    Last Post: 10-08-2015, 10:46 AM
  4. Replies: 3
    Last Post: 06-02-2015, 06:48 PM
  5. Database Structure | Best Practice Question
    By davidson12 in forum Forms
    Replies: 0
    Last Post: 11-05-2009, 03:29 PM

Tags for this Thread

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