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!!