New customer management system (1 Viewer)

MarkusPalsson

New member
Local time
Today, 01:19
Joined
Nov 6, 2018
Messages
8
Hi all!


I have a new project in mind, but as I'm completely new to MS Access, I have a difficult time telling what can be done and what can't be done in access. So I'll describe my ideas and hopefully you can give me some pointers :).


Me and my wife has a couple of pet shops and we are growing tired of using manual registers to keep track of bonus cards, mostly related to dog food brands and cat food brands. Each brand has their own bonus system, i.e. "buy 5 products, get 1 for free", or "buy 4 products, get 50% discount on the fifth" etc. So it's a lot of registers, and some of them contains hundreds of customers. It takes up time browsing through them, and it's a bothersome task to collect completed cards and report them. So now we want to design a digital system, and I thought MS access might do the trick. It has to be easy to use as all the store personel, young and old, must be able to use it with ease.



These are the requirements of the system:
- Customer mangement (add, edit, delete customers)
- Register purchases to customers
- Calculate when it's time to give a bonus
- Generate a specific type of report for each brand


I've attached some pictures showing an idea of a user interface and which tables I think I need. I would be very happy to get some input on this. Which parts of this design is simple/difficult to realize in access? What will i need to learn, and where can i find good information on those topics?



I've tried to google some information, but it's difficult to know how to build it from the ground when you get a glimpse here and there, without a solid basic understanding.


Grateful for all the help I can get!


Best Regards,
Markus
 

Attachments

  • add_customer.png
    add_customer.png
    6.2 KB · Views: 312
  • edit_customer.png
    edit_customer.png
    7 KB · Views: 296
  • tables.png
    tables.png
    10.4 KB · Views: 290
  • customer_page.png
    customer_page.png
    21.1 KB · Views: 294

isladogs

MVP / VIP
Local time
Today, 08:19
Joined
Jan 14, 2017
Messages
18,186
Hi markus
Welcome to AWF
Your post was moderated because you attached images.
Until you have 10 posts you need to zip any attachments to avoid that happening again.

Now I've approved your post, can Isla have some free dog food? :D
 

MarkusPalsson

New member
Local time
Today, 01:19
Joined
Nov 6, 2018
Messages
8
Ok, I missed that. Thanks. Swing by the store in Sweden and I'll fix you up with some premium dog food ;)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 19, 2013
Messages
16,552
So I'll describe my ideas and hopefully you can give me some pointers
.
general points, take a look at the access templates for ideas, but recommend don't build on them since they are simplistic and often will take longer to modify than to start from scratch.

- Customer mangement (add, edit, delete customers)
generally straight forward, don't recommend delete, use a delete flag instead.

- Register purchases to customers
consider how - will this be part of your till system as well (calculate change, generate a receipt etc)

- Calculate when it's time to give a bonus
need to know what the rules are, but build the rules in a table. Don't hardcode them in vba or a query

- Generate a specific type of report for each brand
can be done

Google 'normalisation' to understand how tables and relationships should be constructed.

best way to start is to get a pad of sticky pads and write the name of one piece of information on each one (name, address, transaction date, product code etc) and stick them on a wall, grouping them together by relevance and where you would create new values - these groups will become your tables.

For each group have another sticky - primary key (e.g. customerPK, ProductPK). These uniquely identify a record in a table.

Do not write the same bit of information down twice. For example product name will be with your products and also on an invoice - but would be maintained in a product table - so for this you would use a productFK (foreign key) in the invoice table to refer back to the fields in the product table.

Always get your table design done before worrying about forms and reports, if your table design is correct they will follow easily. Although obviously if you need a specific bit of information in your report, it needs to be collected somewhere.

Another example - prices. Where would you store this? in the product table? Perhaps. But prices change, so they probably want to be is a separate table to include the productFK, the date the price became effective and the price.

This may be going further than you need but if any of the data you are collecting is used for taxation or other reporting purposes (VAT, final accounts etc) then the price should copied from the price table and stored in the invoice table - reason is you do not want to risk reporting a different figure over time. It is one of the few occasions where storing the same bit of data twice (breaking normalisation rules) is the right thing to do

good luck with your project
 

isladogs

MVP / VIP
Local time
Today, 08:19
Joined
Jan 14, 2017
Messages
18,186
Ok, I missed that. Thanks. Swing by the store in Sweden and I'll fix you up with some premium dog food ;)

LOL. She was hoping you'd send her a sample. :D
Good luck with your project. You're in good hands with CJL
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:19
Joined
Feb 28, 2001
Messages
26,996
Right now is a crucial time in your product. CJ has given you good advice. I'll add a couple of my favorite bits of advice - and some encouragement.

You have started to organize things you want to do. This is GREAT because so many people just take the plunge and start building things. The design phase of any project is where you can make or break your end result long before it exists as anything other than a dimly imagined image. I offer two rules to contemplate as being crucial issues related to the proper mind-set.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

This means that as you approach your problem, you should identify the manual steps you need to implement in order to achieve the goal currently being considered. If you can't do that, you won't be able to implement VBA code sequences or use Access automatic features to reach that goal.

One approach I often recommend is to keep a document of how you envision each part should work AND you should expressly include how the parts will work TOGETHER. This document will be like a road map. Think of it this way: If you don't have a road map to get you to a place you have never been, (a) how will you get there and (b) how will you know when you have gotten there?

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first (or at least explain to it how to tell you).

This is crucial to understanding what Access IS (and isn't). It is also crucial to what you must understand. If you were a carpenter, Access would be your box of power tools but YOU would still have to shape and fit the parts together to make the French Armoire. YOU are the subject-matter expert. Access knows only one thing: How to put together arbitrary databases.

Therefore, when designing your project, you have various goals in mind. Some of them are behavioral. Many of them represent physical outputs. For each output, you must assure that if Access has to tell you X, you have a source for X, either directly or through computation. This sounds like it involves, and actually DOES involve, working backwards from desired output to necessary inputs. If you need XYZ, can you get XYZ as a block? Or do you need to get X, Y, and Z and a formula? These are the kinds of questions you need.

CJ's method of sticky-notes (which is one of my favorite methods as well) helps you visualize what you are building as a structural mix of database elements. I'm going to break here and continue on a different set of thoughts.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:19
Joined
Feb 28, 2001
Messages
26,996
Round 2:

I looked at your table structure and immediately noted something that should be "food for thought" - appropriate since it was about cat food and dog food.

Here is my question: From a BUSINESS viewpoint, is there any difference in the way you handle dog food and cat food? The tables in your .PNG file look identical. If that is the case, then by adding one more field (the targeted animal type), you can combine two tables into one - the "pet food" table.

Since you have multiple discounting rules, having two tables doubles the number of discounting tables as well. Yet I'm sure that discounts would work similarly for each type. So combining the two types of food would combine the discount tables (or unify them). Again, there would be no structural difference between the dog food discounts and cat food discounts, right? For brand X, it is buy 5, get one free. For brand Y, you get 50% discount for bulk purchases of 10 or more at one time. Things like that would be the same regardless of whether it is cat food, dog food, or wombat food.

This "identification of similarities" would be an example of normalization, which was something CJ suggested would be a good reading topic. And normalization simplifies your life because it eliminates duplication of storage AND of effort.

The example to learn here is that if you have two things that are treated identically but kept separately, you should ask (a) why are they separate and (b) can they be combined from a business flow viewpoint?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:19
Joined
Feb 19, 2002
Messages
42,970
To continue Doc's thoughts. Do your shops sell ONLY cat and dog products? What about discounts for other type of pet food? By putting each type of food in a separate table, you force yourself to add an additional set of tables each time you want to expand your offerings. This is something you need to think about constantly as you are designing the tables and the rest of the application - Am I making an unnecessary constraint? What if I want to add another member of the class "pet" to my discount offerings?

One last thing. Names should be formed from letters (upper and lower), numbers, and the underscore. NO other character should be used including spaces. Any non legal character in the object name will force you to have to enclose the name in [] every time you use it. I know it is only two extra characters but boy to they get annoying. Plus they get confused with (). Also avoid the names of proeprties such as "Name", "Filter", and functions such as "Date", "Month". Access sometimes warns you when you use one of its reserved words but then lets you use it. When you look at code, will
Me.Name
refer to the Name control or the Name of the form? If you don't know, you will end up with subtle errors. Just avoid reserved words and not worry about the potential conflict.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:19
Joined
Feb 28, 2001
Messages
26,996
In the final analysis of normalization, things that are alike in the way they are treated go together in the same table. Things that are different in the way they are treated go into different tables. If you use CJ's suggested sticky-note method first, you identify things that are different before you have to define them in the application file.

I might add that my own variant of that is to put the notes on a dry-erase board so you can draw lines to help you understand data flow and relationships. And then, if you have a method of taking a picture with high enough resolution and can get it to your computer so you can print it or embed it, then you can document your relationships visually. That picture can be part of the "road map."

As you proceed down this garden path, you will find many times where you are not thinking clearly about X but have some great ideas about Y. So you put aside X and tackle Y. You know what? We ALL run into that. Here is where the document becomes important - eventually you have pounded Y into the dirt and are ready to get back to X. And if you have notes that you can review, the time it will take you to pick up on X again is significantly reduced.

Even more important, ... somewhere down the road you'll have a working app but some great new feature will come along and you need to fit it in somehow. So... if you have the road map, you can more easily figure out where it goes - or equally important, where it does NOT go.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 19, 2013
Messages
16,552
Also avoid the names of proeprties such as "Name", "Filter", and functions such as "Date", "Month".
Besides which Name, Date etc is not a good descriptor - name of what object? date of what event? OK, so in a customer table, name probably means customer name, but that means in isolation, you need to know two things, not one.
 

MarkusPalsson

New member
Local time
Today, 01:19
Joined
Nov 6, 2018
Messages
8
Oh no... I just spent an hour writing a long reply to all of your kind inputs, but when i submitted it, i was logged out and the reply disappeared. *sigh...* :eek: :mad: :( :(


I'll have to go to work now, I'll give it a new go this evening.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 19, 2013
Messages
16,552
been caught out like that myself - best to write in notepad/word then copy paste to a new post
 

MarkusPalsson

New member
Local time
Today, 01:19
Joined
Nov 6, 2018
Messages
8
Ah, what the heck, I’ll give it another go now when I have it fresh in mind. It won’t be a detailed as my last attempt, but I’ll try to get the important stuff in there.

So, first of all, many thanks to all of you for your kind replies, it really made my day to see your level of commitment and engagement! I haven’t had time to develop the project further since I posted the topic, but I’ll try to clarify it a bit by answering you questions.

CJ
- Register purchases to customers
consider how - will this be part of your till system as well (calculate change, generate a receipt etc)

No, it is a completely separate customer management system made only to handle bonus cards.

- Calculate when it's time to give a bonus
need to know what the rules are, but build the rules in a table. Don't hardcode them in vba or a query.


How do I build rules into a table? I tied to simplify things in my initial design example by only having two general categories – cat food and dog food. In reality there will be several based on different brands. There will be different rules for different brands, but they all look similar. 5+1 (i.e. sixth for free), 7+1, 4 + 50%, etc.

Another example - prices. Where would you store this? in the product table? Perhaps. But prices change, so they probably want to be is a separate table to include the productFK, the date the price became effective and the price.

Each brand can supply lists (in excel most often) which contains product code, name, weight and list price. Therefore I will use one table per brand, which will be populated by importing this list. This way I can manage product updates in name, size, price etc in an easy manner.
When registering a purchase, one of these tables will be used as a lookup table to pick the right product. Also the list price will be used as the default price, but here you can change that price if you wish to, to account for campaigns or other discounts.

The Doc Man
From a BUSINESS viewpoint, is there any difference in the way you handle dog food and cat food? The tables in your .PNG file look identical. If that is the case, then by adding one more field (the targeted animal type), you can combine two tables into one - the "pet food" table.

Short answer, no, there is no difference. But if you read my reply to CJ above you will understand why I have divided them. However, it might be unnecessary to have more than one purchase table. It should be possible to only have one table which handles all purchases. My question is just, how do I know from which table to pick the products?

Pat Hartman
Do your shops sell ONLY cat and dog products? What about discounts for other type of pet food? By putting each type of food in a separate table, you force yourself to add an additional set of tables each time you want to expand your offerings. This is something you need to think about constantly as you are designing the tables and the rest of the application - Am I making an unnecessary constraint? What if I want to add another member of the class "pet" to my discount offerings?

No, we sell all sorts of stuff =). It was only a simplified example. But it’s a good point. It will most likely pop up new bonus cards from time to time which should be easy to add to the system. As I wrote above in reply to Doc Mans question, I might use only one table to register purchases, but I think I will need separate tables for each brand.


Hopefully I’ll get some time to continue the project this weekend. And without the shadow of a doubt, I’ll get back to you with more questions =).
Again, thanks a lot for taking the time to help me, it is very much appreciated!
 

isladogs

MVP / VIP
Local time
Today, 08:19
Joined
Jan 14, 2017
Messages
18,186
Me too on numerous occasions.
I also use Notepad/Word to prevent that.

The easiest way to mess up, is in my experience, to check a link in mid post and then return to your post.
Unfortunately AWF doesn't have an autosave content unlike some other forums

Markus
Your last post got moderated for some reason so didn't appear.
Now approved.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 19, 2013
Messages
16,552
I'm really busy today but a quick response to

How do I build rules into a table? I tied to simplify things in my initial design example by only having two general categories – cat food and dog food. In reality there will be several based on different brands. There will be different rules for different brands, but they all look similar. 5+1 (i.e. sixth for free), 7+1, 4 + 50%, etc.
Depends on the rule, your example is not clear enough, but if you mean purchases over time and not 'BOGOF' then simplistically a table might be

ProductFK
MinQuan
Reward

when purchases reach MinQuan, apply reward

also

Each brand can supply lists (in excel most often) which contains product code, name, weight and list price. Therefore I will use one table per brand, which will be populated by importing this list. This way I can manage product updates in name, size, price etc in an easy manner.
Bad idea, you will create more problems than you solve doing it this way. You want one table
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:19
Joined
Feb 28, 2001
Messages
26,996
CJ beat me to it. When you have multiple tables, you do not help yourself.

My question is just, how do I know from which table to pick the products?

You solve that problem by not having multiple tables. You absolutely MUST read up on the concept of "normalization" before taking this too far. Many of us will tell you the same exact thing because we know WHY we think a particular idea is good. And the idea of having multiple tables that describe essentially the same thing - pet food - is one of the NOT SO GOOD ideas.

I wonder if your problem is that you have (pardon the metaphor) dived into the deep end of the pool when you should still be working the shallow end where it is harder to get in over your head. Some serious reading on normalization and some contemplation of queries that include WHERE clauses might help.

Example: If every type of pet food has the same characteristics but is just targeted to different animals, then add one field to the table for animal type. Then a QUERY from the pet food table will list all examples of CAT food with something like this:

Code:
SELECT ProdIDCode, ProdName, ProdMaker, ProdPkgSize, ProdPrice, ProdDescription, etc FROM tblPetFood WHERE ProdAnimal = "CAT" ;

Then imagine the same query for the animals "DOG" and "LLAMA" and "WOMBAT" and "CAPE EALAND BUFFALO" (or whatever else you stock.)

You can do updates that include WHERE clauses when you get that Excel spreadsheet. All you need is to import the Excel data to a working table and then write an INSERT INTO (append) query with appropriate WHERE-type selectivity.

As to your bonus table? Consider a table with these fields: < BonusCode, BonusBuy, BonusGet, BonusDiscount, BonusOneBuy >. Then (with Bonus Code being just an arbitrary number)

<1, 5, 1, 100, TRUE> would be Discount 1 = buy 5 at one time (BonusOneBuy flag true), get 1 free (100% discount).

<2, 4, 1, 50, TRUE> would be Discount 2 = buy 4 at one time, get 1 half-off (50% discount).

<3, 10, 10, 20, TRUE> would be Discount 3 = buy 10, get another 10 (in same purchase at 20% discount) - which is the same as - buy 20 and get 10% off.

<4, 9, 1, 100, FALSE> would be Discount 4 = Buy 9 across multiple purchases (BonusOneBuy flag false). We will track your purchases. When you buy the 9th one, you get the 10th one free (100% discount).

To support the latter kind of discount, you need a transaction-based table to remember that customer's previous purchases.

If you use this kind of discount table, it doesn't matter what kind of animal you are feeding. All it matters is that you have an entry in the discount table for that particular discount scheme. And it is possible using JOIN queries to tie tables together so that internally, you are working on data records that contain everything you need to manage the operation on-the-fly.

Everybody says you want to combine tables. You are trying to split tables. But here is where you will find help: Read up on how to use queries. The reason is that when you are driving things through some kind of form, you don't ever need to actually touch a table. You can drive a form through a query. Forms aren't picky. They work quite well when given data via a SELECT query. But let the QUERY touch your table(s) to pick out what you need and filter out the rest. Let the QUERY put things together for you as well as picking them apart.

Queries are your friend.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 19, 2013
Messages
16,552
one other point, if you were to have separate tables for each pricelist so you can simply 'copy/paste' - what would you do if the layout and/or format changes?
 

MarkusPalsson

New member
Local time
Today, 01:19
Joined
Nov 6, 2018
Messages
8
So I finally found some time today to work on my project. I've read about normalization as you all recomended, and I think I got the hang of it, and also I've tried to take in as much of your input as I could. I'll submit the work so far and I'd appreciate your comments on the progress.



I now have the following tables in the design:


tblCustomers (storing customer information)
tblPurchases (storing customer purchases)
tblProducts (storing product names, price, weight etc.)

tblBounsCards (storing number of purchases for each customer and product type)
tblBrands (storing brand names and what type of reward program they use)

tblRewardPrograms (storing min qty for bounus and what the bonus is)


You'll find the relations in the attached file.



DocMan said if I can't do it on paper, I can't do it in access. I've tried to keep that in mind, and I think with the tables above I can manage the system on paper. The process goes something like this.


1. Customer comes wants to register a purchase. Is the customer new, go to 2, else 3.
2. Create new customer. Go to 4.
3. Check in tblBonusCards if the customer has a bonus card of the chosen type. If yes, go to 5, else go to 4.
4. Create a bonus card in tblBonusCards and set nbrPurchases =1 and set the bonus value equal to the price of the bought product. Go to 7.
5. Increase nbrPurchases by 1 and calculate a new bonus value, then check if the required number of purchases in tblRewardPrograms is reached. If yes go to 6, else go to 7.
6. Give the customer a discount equal to the bonus value on the current purchase. Set BonusUsed flag = 1 in the purchase table. Reset counter and bonus value in bonus card table.

7. Done.


The bonus value might require further explanation. It is a number in the bonus cards table which stores the value of the cheapest product bought up until the current purchase, and this will be used to calculate the reward when the bonus card is filled.



So now I can record customers purchases over time. Do you think the current design is doable in access?


I've attached the files in a zip archive as I still don't have 10 posts. I hope you'll still take the extra time and view the files =). Thank you all!



Best Regards,
Markus
 

Attachments

  • flowchart_and_relations.zip
    200.3 KB · Views: 319

MarkusPalsson

New member
Local time
Today, 01:19
Joined
Nov 6, 2018
Messages
8
No feedback at all...? I could really use a helping hand here.


Best Regards,
Markus
 

Users who are viewing this thread

Top Bottom