data base to collect items and qty

Hi all

For some of you this will be very simple. Probably take youlest than 30 min. I am guessing will take me many days to learn and create but Ineed it so I will if that is my only option.
I just got a new job. I was hired to implement an ERPsoftware. 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-housedrivers. Now some of them give us the data from handheld devices they use whenthey 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 fromour 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 theday. 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 datafrom the access database so I am able to teach the distributors office staff. Iam hoping there is a way I can just have a button they click after entering thedata 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 thatare linked to fields. Where I can fill in the tables with the options, I wantto 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 learnand create myself. Who knows the best place for me to start would be good to know?

Thank you for anyone who did read all of this!!

I did also post this in another forum. The more advice thebetter. I am on a time restraint. http://www.accessforums.net/showthre...461#post427461

I cannot see why you are proposing Access when you really want to do it all in Excel. Excel can control what is entered and minimize user error, data validation, worksheet events, all c an be deployed to make sure data is correct. Personally I abhor userforms, especially Access forms, but you could even gave forms in Excel.

Thanks I will look into excel. I am thinking it wouldlook better and be easier for me to setup for a distributor to beentering data into an access database rather than an excel file. I can alwayslook more into that at another time. I did like access in college but as I saidit has been so long you are probably right and I should go that way right now.I will try to get the excel working for now and maybe look into access a laterpoint.

Here is an example I have just knocked up. It is limited of course, it only took me 15 mins, but it can show you the sort of thing I mean.

https://1drv.ms/x/s!AgstZyOTo17jaSOVDk9HuCkcwkA

The company is a dropdown that only allows picking from a list, the date is restricted to a date in this year, and the amount must be numeric. When a line is completed, a new line appears as if by magic.

Thank you. That is good start for me.


Some things would just make something like that hard tofill out.
The end results need to be like the picture below.


So each item within the invoice needs to be on its ownline. The invoice number needs to be the same for all items on the invoice.They would need to fill it out daily but I would only want onereport weekly or monthly with all the data. Sorry I did not explain that.


One invoice could have 7 items each.

That was kind of one reason I was thinking access. Then they could just start anew record each time. Each new record would have its own invoice number. Thenall the items on that record would end up being exported to excel and I hope Ican get results like below.


Do you have any suggestions of an easy way in excel toproduce what I need.


Below is what I use when importing the data to theaccounting program. By the end of a week it could be 1000 rows. Based on howmany items are on each invoice and how many stores they visit that week.


Some places I worry about user error in that case is theywould mistype the invoice numbers. If they do not match it will not importcorrectly.


Can the invoice number auto populate? How would they makea new number appear?


So in my head that makes sense to me and I can think ofthe concept of how to do it with a database. But I can’t think of an easy wayto do it with excel. Is it possible to get all that from excel?

Also that could have data auto populate based on whenit is filled out.


Thank You For Help

I wasn't saying my file was a solution, just some ideas to show you how easy it is. Just like Access, you have to design your solution and build it in accordance with YOUR requirements.

You might want to revisit that last post, there is a ton of garbage there.

Sorry I meant to ask if you can think of a way to do what I need in excel to be user friendly.

Thanks for all your ideas. I will continue to look in all directions

That looks so much better :-).

Looking at it, I think the way that I would do it is to add a couple of buttons to the ribbon, one for a new invoice, one for a continuation invoice. The first would present a new blank line ready for all input. the second would create a new line and auto-populate all of those columns that need replicating.

I'll knock up an example later today.

Thanks I am also doing research but not easy without starting from the basics. Time is always the issue

If you find you need an Access solution let me know.
Usually Excel users find Access too intimidating to program.