Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

Thread Tools Rate Thread Display Modes
Old 11-08-2018, 06:06 AM   #16
Happy Retired Curmudgeon
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,472
Thanks: 62
Thanked 1,175 Times in 1,075 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: New customer management system

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:

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.

I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-09-2018, 02:34 AM   #17
Super Moderator
Join Date: Feb 2013
Location: UK
Posts: 10,439
Thanks: 40
Thanked 3,368 Times in 3,263 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: New customer management system

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?
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 11-09-2018, 05:47 AM   #18
Newly Registered User
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: New customer management system

Hope these pics give you a few idears

After 20 years working with access i have no more hair to give.
MickJav is offline   Reply With Quote
Old Yesterday, 12:12 PM   #19
Newly Registered User
Join Date: Nov 2018
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
MarkusPalsson is on a distinguished road
Re: New customer management system

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,
Attached Files
File Type: zip flowchart_and_relations.zip (200.3 KB, 1 views)

MarkusPalsson is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
library management system mrrul3z General 1 11-26-2010 09:16 AM
Stock Management System mis General 20 01-25-2010 03:48 AM
Order management system garywood84 General 11 12-22-2007 05:33 PM
Using Access for Customer Management? dalesellers Forms 0 10-05-2006 05:43 PM
CD Management System Slate General 1 04-29-2003 02:06 AM

All times are GMT -8. The time now is 02:22 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World