Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 8th, 2006, 11:22 PM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need help

Im making a database for a hairsalonge, and i need a system that can give a discount for each 5th time a person has an appointment. Only problem is that i dont get how to do the "each 5th appointment" part.

At this URL ->"www.geocities.com/heilosan", you can find my database modell with relathions.

Anyone got a solution for this?

  #2 (permalink)  
Old March 9th, 2006, 04:42 AM
Authorized User
 
Join Date: Mar 2006
Location: Bratislava, , Slovakia.
Posts: 80
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Maybe you solve this problem when you typing new appointment.
After you save new appointment run query: "SELECT Count(CustomerID) FROM Appointment", then you get count of all appointments of some customer (or use Dcount function). And if you use Mod operator on this count (Mod 5) you get only reminder of this divide operation. And if the reminder is zero, it provide you solution of your problem.

Peko

P.S. sorry for my english

  #3 (permalink)  
Old March 9th, 2006, 09:20 PM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This sounded really interesting... But isnt "SELECT" SQL? Where am i supposed to type this into the query?

thx 4 help

  #4 (permalink)  
Old March 10th, 2006, 03:53 AM
Authorized User
 
Join Date: Mar 2006
Location: Bratislava, , Slovakia.
Posts: 80
Thanks: 0
Thanked 0 Times in 0 Posts
Default

On form vhere you type new appointment fire Event AfterInsert:
(something like this one)

Private Sub Form_AfterInsert()
Dim lngAppointmentCount As Long
Dim intReminder As Integer

  lngAppointmentCount = DCount("CustomerID", "Appointment", "CustomerID = " & Me.cboCustomerID.Value)

  intReminder = lngAppointmentCount Mod 5

  If intReminder = 0 Then
    'make special price and save it
  Else
    'check the mormal price and save it
  End If

End Sub

And I have one question. Where you store this special price? In Treatment table, or in other place?
Because, if you change the price in Treatment table, this is project on all treatments of all customers.
Maybe you make change on table A/V. Create there new field named ActualPrice, and when you typing new appointment, check the price from Treatment table, and insert new record into A/V table with TreatmentID, AppointmentID and really price (if your customer have discount then special price, or if haven't, then normal price form Treatment table).

Peko

  #5 (permalink)  
Old March 12th, 2006, 01:28 PM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thx Peko! :D ... I didnt do like you said... To advanced for me! :S...

But when you asked your question :P... It made me realise that i didnt have any place to store the new price... What i did was, as you said, made a new attribut called "discount" in the A/T table. I sat the datatype to "yes/no"... The customes gets discount/klipp cards, where its marked if they are to get a discount! This way they will lose their cards often and earn me more money! And! i will not have to store or make any bigg thing in my database, just use a IIf thing in a Query... It all worked out fine...

So thx for help!







Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.