Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-22-2007, 01:06 AM   #1
nexus2011
Registered User
 
Join Date: Mar 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
nexus2011 is on a distinguished road
Customer Discount To Product Discount Group

Hi I'm hoping someone can help me with this problem (the access problem.. not the other one ! ) I have a table of Products, each item belongs to a product group, I have a table of Customers, amoungst the normal customer fields I have fields named after the item discount groups so that each customer has his own discount for that group of products... when I enter an item into the sales order form I would like the discount for that customer for that item(item group) to go into the discount box.. sounds easy if you say it fast... I hope someone can help me with this !


Last edited by nexus2011; 03-22-2007 at 01:42 AM. Reason: spelling
nexus2011 is offline   Reply With Quote
Old 03-22-2007, 01:54 AM   #2
GaryPanic
Smoke me a Kipper,Skipper
 
Join Date: Nov 2005
Location: England
Posts: 3,167
Thanks: 2
Thanked 7 Times in 6 Posts
GaryPanic will become famous soon enough
Would it be 1 discount rate per cleint or would their be differnt discounts according to products

if the first, nice and easy put the discount on the client table
if however it depends upon the product then this is slightly more complicated

we would need to know how many products groups are we talking about
lets take five to begin with you could have 10 extra fields on your customer table (5 times 2) one for productcode and one for discount per product group
then have a lookup on your order table to check all 5 productscodes and get result

so if productcode 1 customer gets 5% off if productcode 2 customer gets 7.5% off
you would need some sort if statement in the background if productcode = 1 then a look up to the corresponding field on the customer table -- If statements driven me a bit loopy - did one laast night very easy - but took an hour to figure it out and get it working so best to ask soemone who can write this in there sleep .
GaryPanic is offline   Reply With Quote
Old 03-22-2007, 01:57 AM   #3
GaryPanic
Smoke me a Kipper,Skipper
 
Join Date: Nov 2005
Location: England
Posts: 3,167
Thanks: 2
Thanked 7 Times in 6 Posts
GaryPanic will become famous soon enough
just re-read your threrad
its if staements you require
if productcode = 1 then discount = x (from table )

easly doen - but I will bail out as I mentioned if staements make me a bit loopy

GaryPanic is offline   Reply With Quote
Old 03-22-2007, 02:09 AM   #4
nexus2011
Registered User
 
Join Date: Mar 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
nexus2011 is on a distinguished road
cheers bud... I had an idea that IF statements would come into it.. and they do the same to me ... I feel like I've climbed Everest when I get one to work properly.
nexus2011 is offline   Reply With Quote
Old 03-22-2007, 02:22 AM   #5
GaryPanic
Smoke me a Kipper,Skipper
 
Join Date: Nov 2005
Location: England
Posts: 3,167
Thanks: 2
Thanked 7 Times in 6 Posts
GaryPanic will become famous soon enough
Did one last night
if x="blur" then Y=combobox.coloumn(1)
else
it took me 1 hour (It was a lot more fancy than that but the whole if this then do that , else do something else

Ahhh - I should do more of em to get to understand them

g
GaryPanic is offline   Reply With Quote
Old 03-22-2007, 02:25 AM   #6
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
I think I sense a normalisation problem. When you say
Quote:
Originally Posted by nexus2011 View Post
amoungst the normal customer fields I have fields named after the item discount groups so that each customer has his own discount for that group of products...
Do you mean that you have a separate column in the table for each product group?
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 03-22-2007, 02:30 AM   #7
nexus2011
Registered User
 
Join Date: Mar 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
nexus2011 is on a distinguished road
Hi, Yes I have seperate columns for each of the 20 product groups so that when I create a new customer I can give then there own discount profile.

Steve

nexus2011 is offline   Reply With Quote
Old 03-22-2007, 03:47 AM   #8
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
That's not the right way to do it. You need a discount table that links to the customer table and the prodct group table and holds the discount rate relevat to each combination. Thus you will have 20 records instead of 20 columns. This is part of a process called normalisation and is essential to efficient, robust database design. Spreadsheet techniques don't transfer well to relational databases.

That will make your current issue easy to resolve. It will also make life a lot easier when you want to add or delete product groups.
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 03-22-2007, 04:52 AM   #9
nexus2011
Registered User
 
Join Date: Mar 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
nexus2011 is on a distinguished road
Cheers Neil, I'll make the changes to my tables and see how it goes from there.

Stevie

nexus2011 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
need help to present subtotals of group AccessWater Reports 5 07-19-2006 06:18 AM
Tracking Daily Various Mixtures of Product Cosmos75 Tables 12 02-24-2003 02:03 PM
[SOLVED] Advanced filters/queries ayc Queries 2 12-03-2002 04:13 AM
Table Structure AdamO Tables 5 07-18-2002 02:00 PM
Group Records and Find Product sarahjg Modules & VBA 7 06-07-2002 11:42 AM




All times are GMT -8. The time now is 04:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
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