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,459
Thanks: 62
Thanked 1,173 Times in 1,073 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,435
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

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 10:47 PM.

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