Use Northwind for this? Twist on Orders database.

[madpiet]

Northwind is basically one customer per order. In my case, I'm thinking of slapping together a solution for a friend of mine who basically collects small orders from people in his neighborhood, aggregates them, and orders from a distributor like Sysco. The problem is that right now nobody gets a proper "subinvoice" (his slice of the whole bill), which makes tracking your costs kind of impossible.



So to solve the problem, I'm thinking I could do modify the standard OrderHeader--(1,M)--LineItem--(M,1)--Product template so that instead it's

OrderHeader--(1,M)--SubOrderHeader(SubOrderHeaderID (PK), CustomerID)--(1,M)--SubOrderLineItem--(M,1)--Product

then if I change the prices in Product, everything adds up. (If the order is above some threshold amount, shipping is free, which they always do).

So then Daniel, the "middleman", can basically just separate out the "subinvoices" and send them to each of his customers.

Did I miss anything?

[June7]

I haven't looked at Northwind lately but seems maybe just have a new field in OrderDetails for MemberID. The order to vendor could aggregate items by product. Then another report would group items by MemberID and total each member group to show portion cost. Page break at each member group.

[madpiet]

Oh, and then change the PK of the OrderDetails table from (OrderID, ProductID) to (OrderID, ProductID, MemberID)? Then my tables shake out like this:

VendorOrder (Date, VendorOrderID(PK), VendorID(FK) ),

CustomerOrderHeader (VendorOrderID(FK), CustomerOrderID (PK), CustomerID, OrderDate)

CustOrderDetail (CustomerOrderID(FK), ProductID (FK), Quantity, UnitPrice)

Product (ProductID (PK), ProductDescription, [default UnitPrice]) ...<-- including Price here means I need a new ID for each price change?

Customer(CustomerID (PK), Name, Phone...)

because the way I see this working is that Customers call in or e-mail with their shopping list, and someone just enters that into the database, and then at some point, they just run a query to aggregate everything and then submit that to the vendor. Then it's easy to sort out who gets how much of each thing. (and they can print out invoices and all that).

After that, the reports are easy.

[June7]

Customer and Member are synonymous?

Does that schema mean you have to create a new table and rebuild everything to accommodate? Not what I had in mind but I suppose it will work.

As for Product table, if you are saving price into UnitPrice field of CustOrderDetail, don't really need a new record when price changes. Otherwise, don't need UnitPrice inCustOrderDetail.

[Welshgasman]

Could ask over on AWF? as two of the developers are on that site?

[ArviLaanemets]

You need the structure like
tblYourOrders: YourOrderID, YourOrdeNo, YourOrderDate, YourDistributorID, ...;
tblVendorOrders: VendorOrderID, VendorOrderNo, VendorOrderDate, ...;
tblVendorOrderRows: VendorOrderID, VendorOrderRow, ItemID, ItemQuantity, ...;
tblItems: ItemID, ItemName, ItemUnit, ...;
tblYourOrderVendorOrders: YourOrderVendorOrderID, YourOrderID, VendorORderID

[HiTechCoach]

What you need is standard accounting. You create Invoices for Customers and Purchase Orders to purchase inventory from suppliers.

You might be using an older version of the Northwind database. There are newer versions of Northwind with everything you need. There is even a developer version. The newer versions have Purchase Orders and inventory control.

[madpiet]

I know that.

I'm just "collecting" their Invoices, aggregating what's in them, and then creating a Purchase Order from that. Dead simple. It's like I'm creating a shopping list for a bunch of households, going shopping, and then dividing the bill up.

[June7]

I tried downloading the Northwind Dev edition template through Access and keep getting "Something went wrong" error. Any other way to get the file? I searched and can't find.

Okay, finally found at https://www.devhut.net/access-northw...ect-downloads/

Now that I can see the db, quite possibly you don't need to change a thing. You are the Northwind business, the neighbors are your customers and you purchase from vendors. If this model won't work for you, likely you will be building from scratch.

[madpiet]

If I wanted to make it super easy for everyone, I'd do something like create an Order form in Excel, where they fill in what they want (maybe have all the products already entered and they just enter quantities of each that they want). Assuming they put their name somewhere on that sheet, I can just put that in a column and then it's super simple. I just leave out that column in my summary, or I concatenate all the business names into a single column.

Some daze I'm just not so smart. I swear! Thanks, June! Can't remember but maybe Leo did the comma-separated values thing (kinda like using STRING_AGG() in SQL Server).

Right now, my idea is to assume that nobody will use Access for any of it. Just collect the data from Excel, append the results, and build the order from there. (Might be a bit optimistic, but we'll see).

I tried opening the template and what does OneTimeProcessing do? Is that a Sub? If so, where's the code?

[Welshgasman]

Decompile and recompile the db. Some people have that issue for some strange reason. I never did.

This is the code

Public Sub OneTimeProcessing()


10        On Error GoTo Err_Handler


20        If Not GetSystemSetting(ssFirstTimeRun) Then


30            SysCmd acSysCmdSetStatus, "One-Time Processing. Please stand by."
40            DoCmd.Hourglass True


50            SetDatesToCurrent     'So new instance is working with current data.


60            SetCtrlCurrencyFormat


70            AddDataMacros


80            SaveSystemSetting ssFirstTimeRun, -1      '-1 is better than True in international scenarios.


90            SysCmd acSysCmdClearStatus
100       End If


Exit_Handler:
110       DoCmd.Hourglass False
120       Exit Sub


Err_Handler:
130       clsErrorHandler.HandleError "modGlobal", "OneTimeProcessing"
140       Resume Exit_Handler
150       Resume
End Sub

It is in ModGlobal

https://www.access-programmers.co.uk...2#post-1959440

[madpiet]

I tried downloading the Northwind Dev edition template through Access and keep getting "Something went wrong" error. Any other way to get the file? I searched and can't find.

Okay, finally found at https://www.devhut.net/access-northw...ect-downloads/

Now that I can see the db, quite possibly you don't need to change a thing. You are the Northwind business, the neighbors are your customers and you purchase from vendors. If this model won't work for you, likely you will be building from scratch.

Kind of. Yes. They submit "shopping lists" to me, I aggregate all that together, and then purchase from the vendors for them, and then distribute it when the shipment arrives.

Since I know they won't have the database, I guess my options are (1) give them a stripped down runtime that will grab the current/selected Purchase Order from their local version, output it to (say) Excel, then attach that to an e-mail with a canned subject. Then I can basically use Outlook automation to grab all of those Excel files, insert them into my database with their e-mail address (or just use it to look up their CustomerID) and then I'm off to the races. Right?

Thanks, June!

[June7]

You would enter their "shopping lists" as orders. You place orders to vendors for product to meet those orders. How you connect them I don't know. I haven't looked at Northwind that closely. Doubt there is a direct connection between customer orders and vendor purchase. Don't think that would be standard business practice.

Email of Excel file is certainly one way for customer to provide order.

[madpiet]

You would enter their "shopping lists" as orders. You place orders to vendors for product to meet those orders. How you connect them I don't know. I haven't looked at Northwind that closely. Doubt there is a direct connection between customer orders and vendor purchase. Don't think that would be standard business practice.

Email of Excel file is certainly one way for customer to provide order.

Okay, that's pretty much what I thought. Thanks!

Pieter