Gathering and consolidating sub-orders for a bulk order

[madpiet]

A friend of mine basically buys bulk items (like 50# bags of flour, among other things) and in order to get free shipping, basically collects orders from his neighbors. Then he collects money from the other buyers to cover their parts of the entire purchase and then places the order. Instead of creating an invoice for each "shopping list", he just makes photocopies of the shipping manifest/invoice and each customer gets a copy.

I was just wondering what the least technical way of doing this. What if I create something like a purchase order form with dropdowns for Product and maybe quantity.. then the rest is simple math. Then each "customer" can e-mail the Excel file or save it to somewhere, and then just summarize using something like PowerQuery to create the consolidated purchase order.

Yes, I could create a database that does this, but my goal was to make it absolutely stupid simple to use. I can definitely see the case for doing this in Access, but I was kind of trying to avoid it. (although the stupid simple reports for quarterly etc reports would be super easy).

Is this even a sensible approach? (If it were my choice, I'd use Access for obvious reasons... queries, reports, data storage...) I'm just trying to figure out if there are other sensible options. One problem with just using Access is how do I enable "customers" to fill out their own "shopping lists' and then submit them. Then I can get the prices from the vendor, add those to a table if the prices have changed, and I'm off and running.

Does this make sense to do in Excel? If so, how would you do it?

[CarlettoFed]

It makes little sense as it is not designed for what you want to do, using Access when opening the program you will have to present a login form that can subsequently influence the Operator's possibilities in managing it..

[madpiet]

Excel isn't for creating basically forms that people fill out? That's funny. 25 years ago I used to do that very thing every single week to submit my timesheet.

The whole point is that I don't want to rely on them having to even know what Access is or how to use it, because I've seen people who in theory know how to use it and it was a disaster. So I'd very much like to avoid that. But if I give them a form with a table, they can fill that out pretty easily.

"Not designed for what I want it to do"? that's absolutely ridiculous. NEXT.

[Welshgasman]

You could try Google Forms. I am sure you can get the data from the Google sheet that gets populated?

[madpiet]

Thanks Gasman! I like that idea. As long as they send me the data in a non-crazy format, I'm sure I can import it. The whole idea is to get the data, aggregate it, then send that to the purveyor or whoever. I'd be okay with a one-time setup (creating forms or whatever) and then I just collect the data, dump it in Excel or whatever and aggregate it. I'm just trying to sort out how I'd do it if I weren't and Abscess nerd. (But it would be handy to dump into Access and generate a receipt for people (say as a PDF) so they can give that to the taxman or whoever.

[Welshgasman]

Thanks Gasman! I like that idea. As long as they send me the data in a non-crazy format, I'm sure I can import it. The whole idea is to get the data, aggregate it, then send that to the purveyor or whoever. I'd be okay with a one-time setup (creating forms or whatever) and then I just collect the data, dump it in Excel or whatever and aggregate it. I'm just trying to sort out how I'd do it if I weren't and Abscess nerd. (But it would be handy to dump into Access and generate a receipt for people (say as a PDF) so they can give that to the taxman or whoever.

It is up to you how you set your forms up and I seem to recall there is some simple validation you can apply to each control.
https://www.youtube.com/results?sear...forms+tutorial

https://www.access-programmers.co.uk...1/post-1932626

[madpiet]

If the list of things that people buy are not completely crazy, maybe the easiest way is a simple form in Excel (or google sheets)
Name at the top, date, and then a table of
(Product, Quantity)

then just import using PowerQuery. As long as I can get (Customer, Product, Quantity, Date) together, the rest is trivial.