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.

[Micron]

Welcome to the forum! You have come to the right place because there are quite a few here who are very knowledgeable and I'm sure many of them will pitch in to help. First, I suggest you help us help you by giving us an idea of what you know about relational databases. Touch on normalization, naming conventions, and what things you know you should avoid. Plus what version of Access you would be using (2016 according to your post). I could have dumped in a lot of suggestions on what you should read as a beginner, but those topics might be things that you already know all about.

Likely you would be very surprised to know that a few of us would recommend you plan out with paper and pencil first, but you need to know how to build a good foundation first, otherwise, you will only make things more difficult for yourself.

[orange]

As micron has advised --more info about you and relational database is needed. First, as we tell many new comers --Access (database) and Excel(spreadsheet) are very different animals in concepts and mindset.

Here is a link that has a lot of info in various formats about database planning and design. The tutorials from RogersAccessLibrary are great if you work through them --you'll experience database design based on a description of the business.

Adding a form is rarely the answer to anyone familiar with spreadsheet and embarking on building their first database.

From experience and as you will find in many posts on many forums, getting a database designed to meet your business rules/facts is absolutely critical. You know your business better than any reader, so for readers to help, we'll need to know more about you, your experience, the business generally, and then we will be able to assist. Clarity is key to getting meaningful/focused answers.

Welcome to the forum and good luck with your project.

[isladogs]

Welcome to the forum
As a new user you may 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 https://www.access-programmers.co.uk...d.php?t=299597

[husseintayebali]

Thank you for the replies. Really grateful

Im a beginner in Ms Access, i know very little, maybe not even the basics, but im good at following through instructions.

Also, what do you guys think after reading my post.. Should i make the switch to Access?

[Micron]

Also, what do you guys think after reading my post.

If you really want to know what I think, take a look at post #9 here:
https://www.accessforums.net/showthread.php?t=71907

In retrospect, this is interesting

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

when one considers you posted this same issue 4 hours earlier elsewhere.

[husseintayebali]

i posted it here first then after an hour on another post cause i had ZERO views and when i cross posted it there, i even put the link that i have cross posted it.

[husseintayebali]

i just couldnt write it as a link cause i need to have minimum 10 posts before i can post links on that forum

[husseintayebali]

Welcome to the forum
As a new user you may 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 https://www.access-programmers.co.uk...d.php?t=299597

thank you for the reply. i read about cross posting. will follow the rules.

[Micron]

thank you for the reply. i read about cross posting. will follow the rules.

That's all we (or at least, I ) are looking for. Yes, some forums don't allow the posting of links right away, but I'm pretty sure this one does. When I followed Ridders52 link to the other forum, pretty sure you had not declared it there yet. Regardless, where forums don't allow links right away, you can still declare that the question is cross posted and provide the name of the other forum(s), which is really the proper thing to do. For what it's worth, one hour is not enough time to wait for free help. Shall we move on?

To answer your question, Excel is great for complex and numerous types of calculations, pivot tables and above all, charts. If you need to store and mix/match data (relate it) and retrieve it, then Access is the way to go. Access can still perform a lot of aggregate functions and some of the Excel functions have Access equivalents, but Access doesn't have all of the Excel functions. Definitely follow through with the links that Orange provides and see if any of them are helpful to you. I will add my own list that I often put in an answer because I believe they are important additions to the primary one - normalization. That is going to be your foundation, so don't skimp over it.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

You should make use of as much of the sources provided as you can, then post back with an image of your Access relationships. While you don't have to create relationships between tables, it helps us to decipher how things are/should be related as well as check for other traps you might have fallen into. As I mentioned earlier, it's common to receive advice that says you should map things out on paper (or white boards) to get an early feel for your design. I've often used large sheets of packing paper, drawing lines between related table fields, revamping as needed. You can scribble notes for follow-up, rough sketch forms, figure out field data types, etc. I wouldn't blame you for thinking that in this computerized world that planning a database on paper seems archaic, but I think others will back me up on it's effectiveness. Alternatively, there are programs you can use that define the tables/fields and relationships between fields without having to create tables and relationships in Access. Perhaps that would suit you better, rather than do all that only to find out it's not right. Perhaps the paper thing is better for those who already understand normalization and relationships. Unfortunately, I've never used those programs, but I suspect others have and will provide a link or two for you to investigate.