Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-05-2011, 04:30 PM   #1
joe_madeira
Newly Registered User
 
Join Date: Dec 2011
Posts: 29
Thanks: 15
Thanked 0 Times in 0 Posts
joe_madeira is on a distinguished road
HELP! Trying to make a query where every 5 orders a customer receives a discount.

I've made an ordering system which includes a query that counts how many orders each individual customer has made.

Every 5 Orders a customer gets a discount on their order. How do I apply this?

Is there a function in access that recounts values? For example, access would count all a customers orders. Once that customer reaches 6 orders the query would automatically change the value to 1 and start recounting again. Can this be done?

Or do I have to do this via an expression whereby every nth term the discount applies?

Any help is much appreciated! I've been searching this for hours!

joe_madeira is offline   Reply With Quote
Old 12-05-2011, 11:32 PM   #2
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 165 Times in 158 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: HELP! Trying to make a query where every 5 orders a customer receives a discount.

You can use Dcount() function to count record on your ordertable and if the result of the count in Mod 5 = 0 then you give a discount.

Here is a test done in a function to only display every 5th number:

Code:
Function CountOrders()
Dim x As Integer
For x = 1 To 50
If x Mod 5 = 0 Then
    Debug.Print x
End If
Next
End Function
JR
JANR is offline   Reply With Quote
Old 12-06-2011, 08:11 AM   #3
joe_madeira
Newly Registered User
 
Join Date: Dec 2011
Posts: 29
Thanks: 15
Thanked 0 Times in 0 Posts
joe_madeira is on a distinguished road
Re: HELP! Trying to make a query where every 5 orders a customer receives a discount.

JANR,
sorry to be a pain but I'm relatively new to MS Access and VBA isn't one of my strengths.
Having said that, i'm not sure if what you posted is exactly what I wanted to do. Perhaps I can elaborate on my situation so that maybe you can give me an answer that I would understand.
I have 6 tables which are linked as so:
(Click "DATABASE RELATIONSHIP" Attachment)
(bare in mind this is a school project)
Here are my queries:
(Click "DATABASE QUERIES" Attachment)
As you can see I have a query that's called "Number of Orders a Customer has made". This query includes a total count.
Here is what that query displays:
(Click "DATABASE test data" Attachment)
As you can see Stavro has made a total of 8 orders.
In my system those who are members get a discount every 5th order they make. This discount is a discount of 50% off. So, when Stavro made his 5th order how do i make a query that would show that on this 5th Order Stavro received a discount of 50%. This query would then go into a Form whereby, whenever Stavro makes an order the count of his orders will keep going up until he reaches order number 10 which would then display that he gets a 50% discount so the discount will apply in this form to Stavro's total pay.

I don't know how to apply this discount on every 5th order. Can this be done by query? If its not too much to ask can you explain to me step by step? If not, then i understand its cool.

I appreciate your first reply =) Any extra help is much more appreciated!
This is literally the last query I need to complete my databaseee. Once this is done the burden on my shoulders will be lifted!
Attached Images
File Type: png DATABASE QUERIES.png (59.5 KB, 57 views)
File Type: png DATABASE RELATIONSHIP.png (20.6 KB, 55 views)
File Type: png DATABASE test data.png (11.1 KB, 60 views)

joe_madeira is offline   Reply With Quote
Old 12-06-2011, 11:25 AM   #4
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 165 Times in 158 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: HELP! Trying to make a query where every 5 orders a customer receives a discount.

See attached mdb.

Its an over simplyfied db just to show the mechanics of the expressions. I'v added a Current count on the form so you can see when a customer gets his count to his fifth count and watch how the locked discount control displays the discount.

There is little VBA in the form exept in the Forms BeforeUpdate event where the discount is recorded in the table, default discount is zero.

Here is the expression behind the unbound discount control:

=IIf(Nz(DLookUp("CurrentCount","CurrentOrders","CustId=cb oCustomer") Mod 5=0,0),[cboProduct].[column](2)*0,5*[NumberUnits],0)

The Nz marked in red is just there to ensure that you don't get an error if a customer dosen't have recorded any orders.

To keep track of the current count of order I used a query similar to what you have.

Hope this helps

JR
Attached Files
File Type: mdb joeMedeiraMDB.mdb (424.0 KB, 41 views)

Last edited by JANR; 12-06-2011 at 01:18 PM.
JANR is offline   Reply With Quote
The Following User Says Thank You to JANR For This Useful Post:
joe_madeira (12-06-2011)
Old 12-06-2011, 03:19 PM   #5
joe_madeira
Newly Registered User
 
Join Date: Dec 2011
Posts: 29
Thanks: 15
Thanked 0 Times in 0 Posts
joe_madeira is on a distinguished road
Re: HELP! Trying to make a query where every 5 orders a customer receives a discount.

JANR,

Thanks man, I think I understand it its just a matter of applying that into my database now. It may take me some time but i'm sure i'll figure it out. If i can't and I have a problem i'll let you know.

Much appreciated G!
joe_madeira is offline   Reply With Quote
Old 12-06-2011, 11:25 PM   #6
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 165 Times in 158 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: HELP! Trying to make a query where every 5 orders a customer receives a discount.

No problem Joe

You could also use DCount directly on the ordertable, and since DCount dosen't error on missing customer you don't need the Nz() wrapper function. It is a lot cleaner I think.

=IIf(DCount("*","tblOrder","CustFK=cboCustomer") Mod 5=0,[cboProduct].[Column(2)*0,5*[NumberUnits],0)

JR
JANR is offline   Reply With Quote
Old 12-08-2011, 04:02 PM   #7
joe_madeira
Newly Registered User
 
Join Date: Dec 2011
Posts: 29
Thanks: 15
Thanked 0 Times in 0 Posts
joe_madeira is on a distinguished road
Re: HELP! Trying to make a query where every 5 orders a customer receives a discount.

Hey man,

I've come stuck! My discount field and Order Sum field just say "#NAME?".
I'm struggling to understand this DCount function. I'll research it a bit more in the meantime but if your free could you try taking a look at a version of my database that just consists of the tables and the relationships?

You don't have to, any extra help is appreciated! Gotta finish this database by friday night! its taken days off of my life already. Ima be so relieved when its complete and perfected.
Attached Files
File Type: mdb Just Tables DB.mdb (308.0 KB, 35 views)

joe_madeira is offline   Reply With Quote
Old 12-08-2011, 11:49 PM   #8
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 165 Times in 158 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: HELP! Trying to make a query where every 5 orders a customer receives a discount.

See attached.

Added a form which uses DCount() to find who gets Discount, note the criteria parameter of the function which points to a form control called MyID that has its controlsource bound to the Customer ID field in the recordsource.

JR
Attached Files
File Type: mdb Just Tables DBRev.mdb (440.0 KB, 39 views)

JANR 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
Customer Discount To Product Discount Group nexus2011 Tables 8 03-22-2007 04:52 AM
Customer report, showing all orders by that customer Banaticus Reports 1 01-23-2006 04:51 PM
report which give me orders per customer Dinictus Reports 2 12-07-2004 01:00 AM
Customer not showing in Orders Table 3239 Tables 1 12-21-2003 01:05 PM
Creating A Query showing all orders customer has made TedMurphy Queries 6 05-08-2003 08:37 AM




All times are GMT -8. The time now is 04:32 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