Results 1 to 5 of 5
  1. #1
    mkwilliq is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    1

    How would you design this database?


    Hello everyone!

    Access newbie here. I am trying to accomplish something utilizing Access but i don't know the first thing about creating a database from scratch and i don't know how to write code either. So i'm hoping via this forum i can learn how to accomplish this.

    Who's up for a challenge and want's to play mentor role?!

    Current State
    Right now i have a standardized Excel form that my customer use to request services from my team. There are dedicated cells on the form that they manually enter info and then there are dedicated cells where they pick items from a drop down list. Once they fill those items out, it gets emailed to my team. We then typically print off the form and take it with us to use for reference as we are performing those task. We have a section on the form for internal use where we manually jot down info about the services we are performing such as how long it took us, what consumables we used, etc. At the end of the day, we then take those printed forms with the notes and go to a crude access database that i somehow managed to tweak from a Microsoft template (shocked myself here!). I basically recreated an access form that mimics the Excel form. So my team then opens a new form and manually enters in everything our customer entered plus everything we noted and clicks submit. Very inefficient and the output data is only a small piece of what i want to see.

    Future State

    Ultimate goal is for the customer to:
    • Customer fills out Excel form and emails it to us
    • We download the attachment to a dedicated folder we will setup
    • Access will automatically read that Excel file and extract data from fields that i specify and enter that into the database as a new order.
    • My team then has a tablet/laptop with access on it and they can open each order and type their info directly into each open order. Once all their info is in they hit submit and it goes to completed orders.


    There are 2 other things i want to track that we currently don't track.
    • Consumables - I want my team to be able to fill out a form within this database that tracks who we are buying supplies from and what we are buying and how much we paid.
    • Outsourcing - Occasionally we get a request that we cannot accommodate so we outsource the work to suppliers we have setup. I'll usually quote 2-3 suppliers before awarding 1 the work. I would like to track what each supplier quoted in addition to which one was awarded the work.


    For our "dashboard" i would like to see 3 things primarily in terms of "queries".
    • Outstanding list of jobs that need to be completed
    • A side by side tracker that shows what i've spent on consumables (by quarter) compared to the jobs we've completed internally (by quarter). We are not a profit center so i need to make sure these 2 items stay close to equal of each other. If they don't, i need to reduce the cost of some of our services for the next quarter to get it back in sync.
    • A tracker that shows quarterly and yearly spend on outsourcing.


    The Access database that's pretty darn close is called "Services" and is an available template from Access if you search for it under the "New" tab.

    Our customers (internal employees within the company) don't have access to Access, hence why we need to keep Excel as our connecting point.

    This should be enough to get the discussion going!!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    all doable in Access. Your customers don't need a full working copy of Access, they can use access runtime which is free. Main issue is whether or not they have access to where the backend will be.

    Other considerations - how many customers, how many in team. How do they connect to the network? wireless, cable? - it may using sql server express would be a better option for the backend or perhaps sql azure if your business is multi site.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That's a lot of ground to cover. First thing you need to do is complete some tutorials, at least get an introductory book on Access and spend a week with it. Get a basic understanding of relational database principles, Access functionality, programming concepts, and macro and/or VBA coding. A forum platform is not the best place to learn basics. We can help with specific issues but you need a grounding in the basics.

    Identify your data entities and relationships (diagram on a napkin if that works for you). Then build tables accordingly. Then look at building the user interface.

    Keep the spreadsheet structure simple for easier import of data. Here's a good site for example code http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
    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.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    one other thought - rather than access parse a folder for new files, it can parse an outlook folder, or you can set up vba in outlook to update access - best to set up for a specific folder for a group email account

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are your customers internal only? If so and they can have Access, maybe eliminate the Excel part and have them input data directly into tables that you link to. Does not have to be the same back end that you use to complete the tasks (which means I'm implying both db's should be split).
    It has been oft said here that it's not a matter of IF your db gets corrupted over WIFI, it's a matter of when. The part about using tablets or laptops to complete the tasks means they either require connectivity to the master db to do this in real time, or you require a means to later connect to the network and upload the changes from their stand alone copies, which puts you in the realm of replication.

    What you want to do is quite beyond the novice level, and your superiors should be content with this taking you quite some time if you're really at the novice level now. Even if you're a fast learner, I'd say you're talking months - many if this won't be your only responsibility in the meantime. There is a danger that you could learn everything you need to know to put it together and maintain it only to find it blows up because of issues affecting performance that are beyond the realm of Access itself - such as WIFI induced corruption.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  2. i need design database
    By abdelkader_99 in forum Access
    Replies: 3
    Last Post: 10-06-2015, 09:06 AM
  3. Database Design
    By Dyers in forum Database Design
    Replies: 22
    Last Post: 11-19-2014, 12:21 PM
  4. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  5. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM

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