Several Customers, Several Products, Different Price for each Customer

[husseintayebali]

Hello, hope everyone is doing great.

This is my first post in any forum ever, feels very exciting...

Okay, so I'm 22 years old, live here in Kenya, and run a detergent manufacturing company. I started it from scratch 3 years ago.

I buy raw materials locally, turn them into ready goods (products) and then supply to different hotels (customers).

Payment to me is never in cash, so I do the invoicing, statements, and payment recording using Wave Accounting which is free and online based.

Currently, I manufacture 95 different products, and have 45 different customers.

Where i need help is to set up a database to store pricing for each customer; Prices are negotiated with the customer due to fierce market competition and sometimes I have to beat their current supplier's prices; and every invoice issued to them use that negotiated price list. It is not a fixed rate such as a percentage off or something. Each product price changes.

Currently I have it all in a single Excel Workbook. I have 45 worksheets, each worksheet for each hotel (customer) and in every worksheet i have 95 different products (columns) with the negotiated price next to it product. In short, each hotel buy the 95 products at different prices.

Normally here in Kenya, i just get a call from the Procurement Office or the Store Manager of the hotel on what's required. Purchase Orders and rarely issued.

So whenever I get an order via phone, I log into wave accounting which has records of my customers, i select the customer and the products they need (i have all the 95 products updated on the site but prices are all set to zero). I then have to manually refer to the prices from the workbook for the specific hotel and input them.

My business has started to smooth out and grow. I went to digital invoicing just barely an year ago.

I want to stick to Wave Accounting cause of its simplicity, i don't require complex features of other accounting systems.

Currently, I was thinking of shifting the workbook to MS Access.

Main reason is new products are added frequently, just this year in the past five months i have added 13 new products. So i manually have to go to every worksheet and add each product.

I want someone to help me structure how my database should be in access. How many tables do I need? Any relationships between them? Any identifiers?

Im not sure if its query im talking about but i want the option to just choose from a dropdown menu the customer's name and I want to get all their prices etc.

I also want something like a form to add new products then choose/edit price for each hotel etc.

Or is there something else you would like to recommend to me?

Really sorry for the long question and background info. I didnt want to leave anything out. Thank you reading this atleast.

Cross posted at: www . accessforums . net/showthread.php?t=71915

 

[CJ_London]

welcome to the forum, as a new user you will probably not be aware of the etiquette of cross posting. Please read this link http://www.excelguru.ca/content.php?184

Fir the benefit of responders, cross posted here http://www.accessforums.net/showthread.php?t=71915

from your description, it sounds like you need 3 tables, one for customers, one for products and a price 'join' table to link them together

tblCustomers
CustomerPK autonumber
CustomerName text

tblProducts
ProductPK autonumber
ProductName text

tblPrices
PricePK autonumber
CustomerFK long - link to PK in tblCustomers
ProductFK long - link to PK in tblProducts
Price - currency

Since you are handling most transactions through Wave, you don't really need much more

your form for finding a price for a customer simply needs two combo boxes one for customers and one for products, then a third textbox to display the price - could use dlookup to find the price

use a different for form for updating prices. this would have products as the recordsource - again perhaps with a combobox to find a product. Then in a subform, the record source would be tblprices

if you want to see all the prices for a customer, use a similar form to the one above, but base the main form on the customer table rather than the product table

 

[husseintayebali]

From your description, it sounds like you need 3 tables, one for customers, one for products and a price 'join' table to link them together

tblCustomers
CustomerPK autonumber
CustomerName text

tblProducts
ProductPK autonumber
ProductName text

tblPrices
PricePK autonumber
CustomerFK long - link to PK in tblCustomers
ProductFK long - link to PK in tblProducts
Price - currency

Since you are handling most transactions through Wave, you don't really need much more

your form for finding a price for a customer simply needs two combo boxes one for customers and one for products, then a third textbox to display the price - could use dlookup to find the price

use a different for form for updating prices. this would have products as the recordsource - again perhaps with a combobox to find a product. Then in a subform, the record source would be tblprices

if you want to see all the prices for a customer, use a similar form to the one above, but base the main form on the customer table rather than the product table

im just a beginner in access but i'll try what you said and if it works, i'll cross post the solution in the other forum i posted this. really grateful for the help.. also, sorry about the cross posting..

i hope you got what i mean. each customer has its own set of prices for all the 95 products and currently i have 45 customers..

also customers list is growing and even product list..

 

[The_Doc_Man]

husseintayebali,

As a new forum member, you can be forgiven for not know the community preference about cross-posting. Note that as long as you reveal that you cross-posted, we can be very forgiving. It's the "sneak around for another answer" attitude that bothers, keyword being "sneak."

CJ's advice is sound. The reason you need it this way is a bit of database "normalization" theory. If you haven't read about that subject, you can find information two ways.

A) On this forum use the Search feature for "Normalization"
B) On a general web search, look for "Database Normalization"

CJ's tblPrices is what we call a "junction" table. (You can look that up online too.) Here is the reason you put the prices in the junction table:

You have clearly stated that each customer's price for each product is the result of a negotiation. Therefore the price doesn't go with the customer AND it doesn't go with the product. It goes with BOTH. But normalization doesn't allow you to mix entities. Your customers belong in the customer table. Your products belong in the product table. Something that touches BOTH tables belongs in NEITHER table. So you use the junction table to act as a "bridge" between the two. And anything that belongs to both the customer AND the product is recorded in that junction.

Something you CAN do in the product table is record the "nominal" or "actual" cost of the product i.e. what it costs YOU, so that you could if you wished write a query that you could use to analyze your discount structure and see how much each customer is getting in the way of discounts either across the board - or how much a particular product gets discounted across the list of customers. By keeping things separate this way, when you add features that are related to customers or to products, it is easier to add fields where they belong.

You asked about relationships. Yes, you need two initially. Just to remind you since you said you are new to Access: Every table that will be the "parent" in a relationship MUST have a prime key which by definition is unique. So you will need a customer ID and a product ID. You can look up on this forum the topic of "synthetic keys" to see various discussions regarding the merits of synthetic and natural keys.

The two relationships will be that you have the "one to many" relationship twice. Once between customer (one-side) and prices (many-side), and once between product (one-side) and prices (many-side). If you do that early in the process, then all forms and queries and reports that you build can "see" those relationships and will act accordingly. The form design wizards can build combo boxes for you better if the relationships are already there.

The language we use here is to call the prime key the PK and the corresponding values in the junction table or child table are called foreign keys or FK. You relationship diagram will then be that the prices table is the child of the customers table and the products table. CJ's description of what to do was perfectly good, but if you are new you might wonder WHY you do certain things. Here's at least a partial answer.

 

[Pat Hartman]

Welcome aboard and good for you starting a business of your own at 19.

CJ gave you a great start but I would add one or two additional fields to tblProducts. Either your cost basis or your minimum sales price and perhaps your desired sales price. This will be used when you make proposals as a starting point. If you want to do tiered sales, you will need a second table. 1- 10 units per month @ x, 11-20 @ y, etc.

It seems like you don't actually need to track sales in this database since the online platform is doing that for you. It may have all the reporting you need but if not you might want to look into what types of downloads you can get from the cloud app.

Just FYI, nothing is ever free. Wave is making money on your data somehow. Someone is paying per transaction or Wave is selling your information. Perhaps both. I suggest that you make sure you can retrieve all your data from Wave in a machine readable format so you know where you stand. You don't want to be in a position as you grow and want to change systems and not be able to retrieve all your Wave data in a machine readable format. You also might want to read their agreement to find out what rights you signed away for the "free" service.