Results 1 to 5 of 5

Thread: Unique records

  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: Unique records

    I have a query which checks for True in several check fields. If they are ALL true, then a payment is made for a fixed sum.

    However, a customer might appear more than once and have the check fields for multiple records. The payment is to be made only ONCE for the checked fields, irrespective of how many records a customer might have with repeating check marks. How do I restrict the query to a payment to be made only once if the criteria is satisfied?

    Thanks in advance.

    Jabo

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    experiment with distinct or distinct row.

    something like:
    SELECT DISTINCT myCustomer FROM thisTable WHERE ((this = True) AND (that = true));
    will probably take you where you want to be.

    there's probably some way of doing this in A's GUI but i never looked for it. just type the word DISTINCT after the SELECT in the SQL view of the query.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    Posts
    82

    Unique records

    Thanks for the response, Izy.
    I was aware of your idea, but it would not work. Let me elaborate on my problem.

    I have customers turning in data for three different time points: call them A, B, and C. All of these time points ask for the same data, but each is analysed on its own. Each time point asks for a series of data that, when completed, is "check marked". So, as an example, if I am requesting five series of data (1,2,3,4,5) for each time point, when the Customer turns in data for Time Point A that is complete for all five series, it would have five check marks associated with each of these. The customer is then paid a fixed sum for the trouble.

    But, the customer can turn in data more than once for any time point (for reasons not material here - the data would be different). If, say, a customer turns in data twice for Time Point A that is complete for all five series (ie, the check marks are true twice for A), they cannot be paid the amount twice, but only once.

    I have a Union query to handle all three time points that identify the customer, quarter, an IIf statement to check that all data series are complete. I now need a way to handle the condition referred above - payment only once for any time point for a customer.

    What I did was to create another query referring the union query that does a sum and an expression field that works on the sum to "cap" the amount. Not very tidy. I would like to embed the "cap" within the union query.

    Thanks in advance.

    Jabo

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about a really simple idea:

    add payA, payB, payC to your customer table (byte, default = 1)
    after you make a payment for "A" to a given customer, set payA = 0

    the amount to pay is (payA * whateverYouAreDoingNow)


    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Jablonski

    its better not to use PM - post questions here on the site so lots of people can have a go at a solution.

    anyhow, here is what you said:

    One additional question: when my criteria (for A, B, or C) is fulfilled, I have a report that issues a payment authorization. It is here that I have an event handling piece of code. So, if the payment happens, my code will have to write to the table to change the values of your suggested fields. Since I cannot write an event on the table, I guess I would have to write a SQL to update the table. How would I do that?


    if you already use a query "qryDrivingReport" to feed your report and it includes IDcustomer (if not, add it) of the customers you are paying.
    and if you are doing this one payment-type per report (e.g. PayA but not PayB, PayC), then PayB, then PayC

    UPDATE tblCustomer SET PayA = 0
    WHERE IDcustomer IN (SELECT CustID FROM qryDrivingReport)
    ...should fix it.

    i cant think of a way to handle a single report that does payments for A, B, C (i.e. one query handles all three payment cases at the same time) -AND- make a simple UPDATE based on the IDs in that query. i'm sure there is a way but it doesn't come to mind for the moment.
    you could do it with three updates:
    UPDATE tblCustomer SET PayA = 0 WHERE ...whatever are the conditions to make Payment A
    UPDATE tblCustomer SET PayB = 0 WHERE ...whatever are the conditions to make Payment B
    UPDATE tblCustomer SET PayC = 0 WHERE ...whatever are the conditions to make Payment C
    this hits all PayX that qualify even if PayX already = 0 but that does no great harm
    you could overcome this sledgehammer inelegance with:
    UPDATE tblCustomer SET PayC = 0 WHERE ((...whatever are the conditions..) AND (PayC=1))


    afterthoughts:

    you are talking payments here so you don't want things to go banana-shaped halfway thru. for example you make the payments "report" so the payments get done but the UPDATEs fail so you don't record that payment is made in your db.

    it would be nice to wrap this up in an explicit transaction, but that is not very tidy with a report in the middle. i suppose in a single-user situation you could stick a transaction around the whole select/report/update block ...maybe?

    or maybe instead of feeding the report directly from qryDrivingReport, use a similar query to fill a temporary table. use the temporary table as an ID source for the UPDATES.
    wrap the MAKE TABLE and the three UPDATEs in an explicit transaction
    only if the transaction completes go to the reporting=payment stage based on the temporary table and then delete the temporary table. there is still that loose connection between printing the payment authorisations and the db changes -- it is never pretty when the inner workings of a database have to interact with the real world!

    good luck! if you decide to go the transaction route you have many happy moments ahead of you looking up begintrans, committrans, rollback etc (or whatever their ADO equivalents are) on the internet.

    izy
    currently using SS 2008R2

Posting Permissions

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