Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting the records (Access 2000)

    Could you help me with the building of a function that sets the value of Yes/No field from yes to No after counting two sucessive records? i do not know how to begin and it is better to ask form the start. My Yes/No field is called Fillip.
    The field Fillip should be set to No automatically after two successive payments.I have given an example in my attachement.In my example client 1 has a Fillip set to yes.There are 2 payments with him: payment 1800 and payment 1801. On the seocnd payment, which is 1801, i have to set the Value Fillip to No which means that for any further payment, for example payment 1803, the value should be set to No
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting the records (Access 2000)

    Apparently, there can only be a single payment for each order. What is the paymentID for?
    Moreover, since you have set the default value of paymentID to 0, this field will never be blank unless you explicitly clear it. So what exactly do you mean by "after two successive payments"?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting the records (Access 2000)

    Yes, each order has only a single paymentid. An order can be unpaid, then the paymentid = 0, or paid then paymentid is >0. Paymentid for us is the invoicenumber.Each order is converted into invoice if paymentid > 0. If not it is only an order. An order is considered as invoiced when it has some paymentid number. Actually paymentid = 1800 means that the invoice number is 1800.If the product is sold it means it has a paymentid >0. After 2 payments, in the example after paymentid = 1800 and 1801, the value of Fillip should be set to No. We have decided that bonuses will be given only when Fillip is set to yes, but only for two invoices or payments.After that no bonuses will be given.
    What i want is somehow to count the number of the payments and if the count number is more than 2 to set the value to No.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting the records (Access 2000)

    You can calculate whether a customer gets a bonus or not, so there is no need to set the value of Fillip to No.

    The query qryCountPayments in the attached version of the database calculates the number of payments (PaymentID > 0) per customer in the Orders table.
    The query qryGetsBonus calculates whether each customer in the Customers table gets a bonus: if Fillip is Yes and the number of payments is less than or equal to 2.

    As you see, even though Fillip is still set to Yes for customer 1 (aaa), the query indicates that they don't get a bonus anymore since there are three payments from this customer.
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting the records (Access 2000)

    Thank you so much dear Hans ! Of course it works the way i want. This solution is so unexpected and solves my probllem in such an elegant way !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •