Results 1 to 7 of 7
  1. #1
    joef is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2019
    Posts
    3

    New database

    Hi all

    For some of you this will be very simple. Probably take you lest than 30 min. I am guessing will take me many days to learn and create but I need it so I will if that is my only option.
    I just got a new job. I was hired to implement an ERP software. The currently do everything within excel sheets. I have the software installed and running but now cost has become an issue and they are not willing to deploy all of the features it offers.
    We are a bread factory. We have distributors and also in-house drivers. Now some of them give us the data from handheld devices they use when they deliver our product. Others give us paper invoices and employees sit all day entering them into the account software.
    The new ERP program does offer the ability to use handheld devices when delivering the items. It then uploads it into the program. I export it from the ERP program then upload into the accounting program.
    The problem is it is $60 a month per route. We have over 30 routes. If we deploy this it will eliminate the need for one or more employees who do data enter all day and then pay for itself. But they are not willing to do that.
    I have found a way to get the handheld data that we get from our distributors into the accounting program from excel. I take the data we get from the distributor’s handheld devices, I then have created 10-12 VBA macros within excel and convert it to how I need it and use a program I found to upload it.
    If I can create a simple Access database that I can give to distributors who currently give us paper invoices where they fill in the blanks with what they delivered for the day. It then needs to be exported to an excel file and sent to us. I can then just import it. It will need it to be very simple to enter and export the data from the access database so I am able to teach the distributors office staff. I am hoping there is a way I can just have a button they click after entering the data and it will convert into and excel file.
    The reason I want to create a database and not have them use excel is to minimize user error. Rather than having them enter the data into and excel file I can create a database with dropdown boxes to chose the company and items.
    I did take an access class in college but that was over 10 years ago and I have forgot everything. I can picture what I need. Tables that are linked to fields. Where I can fill in the tables with the options, I want to show in the drop-down boxes. I only need 6 fields. “Date, invoice number, item quantity and price”. The date and invoice number will auto populate. Maybe I can give the invoice number for each distributor a different starting number and it will just increase from there. The price will need to be linked to the items. The items and customer names will be what I put in the tables.
    Does anyone know of a place I can get something created for me like this that is very inexpensive or free? Or next option will be to learn and create myself. Who knows the best place for me to start would be good to know?
    Also thoughts how long this could take to create.


    Thank you for anyone who did read all of this!!
    I did also post this in another forum. The more advice the better. I am on a time restraint.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Getting someone to do it for you will cost. And to develop yourself will take time. Although if you evaluate your time then that will give you a budget you can work with. Have you talked to your distributors? Perhaps they already have an electronic file they can email? Or perhaps they won’t want to or can’t use access. You can create lists and dropdowns in excel which might be a simpler and cheaper solution

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The reason I want to create a database and not have them use excel is to minimize user error
    If this means due to transposing from workbooks to a db then that's not really an issue. If you mean by whomever is entering into workbook, then I don't see much difference as it's possible to get input errors in a workbook or a db. It's also possible to do things to prevent that - in either place.

    Hard to know precisely what to recommend as believe it or not, the outline does leave a fair bit of room for interpretation. If each business providing data provided their own workbook, you can link to those as tables in Access. That could be given to you via email, or they could have a shared cloud based folder belonging to you. Not sure, but it sounds like if you can get the handheld data into Excel, you only need to worry about the paper based stuff. There might be OCR, or pdf forms, or Word docs from templates that could be considered as inputs.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    joef is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2019
    Posts
    3
    I need output like the picture below. A couple issue I see with having them enter into excel. Each item needs to be on a new line. The invoice numbers needs to be the same for each line with a item from that invoice. Also the account names need to be spelled and in the same format I choose. Same with items. For them to have to look at a paper invoice and type it out like in the picture below would be a lot of work.

    We will be giving the distributors the option. To pay the fee for the handheld devices or to enter the data into whatever I can find that will be reasonable. I am trying to find a solutions that wont take them long time and try to minimize input error.

    Currently the distributors that give us paper invoices do not have any digital copies.
    Attached Thumbnails Attached Thumbnails 1.JPG  

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In the image above, is the Account name the same as distributors/ in-house drivers?
    Who determines the invoice number? Your company or the distributors/ in-house drivers?
    How is the price per item determined? In the image, all items are $3.36.

  6. #6
    joef is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2019
    Posts
    3
    The first image is how I receive data from a distributor that sends us handheld data. The second is the format I need to upload it into my account software. The third is what I take out of the first image before I run my macros. I have a xlsm file that I paste the data into and it converts it to how I need. The only data I need is date, invoice number, account name, item and quantity. I have macros that converts there information to match mine. The invoice number does not matter. I would like it 6 digits and must be unique. I can insert the price when I run my macros.

    I am trying to find a way to get an excel file from the ones who currently hand us paper copies
    I did just start looking into creating a mobile app to get them to enter the information as they deliver it. I have never worked with apps before. Me getting it in an excel format would be best for me but I need to keep it easy for them also


    Click image for larger version. 

Name:	kkb.JPG 
Views:	20 
Size:	180.7 KB 
ID:	38072Click image for larger version. 

Name:	after.JPG 
Views:	20 
Size:	239.1 KB 
ID:	38073
    Attached Thumbnails Attached Thumbnails ex.JPG  

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like you want to stay with Excel, so you might look into OpenOffice . (also see http://www.openoffice.org/product/calc.html)
    Open Office is free and has a spreadsheet app; it is available on a PC, Mac or Linux platform.

    You could set up spreadsheet forms for distributors who currently give you paper invoices.
    Can/could use a iPad or a tablet and have the drivers enter the info into the Open Office spreadsheet app. Then save the data as a text/CSV file or Excel compatible file that can be imported into Access/Excel for manipulation.


    Good luck with your project.

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

Similar Threads

  1. Replies: 6
    Last Post: 10-30-2018, 04:35 AM
  2. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  3. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  4. Replies: 1
    Last Post: 07-01-2015, 04:18 AM
  5. Replies: 1
    Last Post: 03-21-2015, 11:55 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