Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Location
    Toronto Ontario
    Posts
    10

    Talking Unanswered: Customer list query AND UI Awesomeness

    I have two tables setup being in a one to many relationship as:
    customer details in table: (customer)
    service details in table: (tblservice)

    I am trying to display customer data with a few details about their recent service if they havent completed a service within the past 30 days with the customers phone number.

    With this list an employee is using an interface calling whoever shows up on the list which works great except sometimes there are entries which are old entries that do not have the customer.Followup30 checkbox checked which are showing up.

    The only reason why i have the checkbox (customer.Followup30) is so that the entry gets filtered out of the UI.


    I'm not too sure how to handle this.

    I do not want to show any old entries just recent ones which are older than 30 days and if the most recent service entry has been checked by the employee than any service older than that entry should be automagically checked and not shown in the list.

    Any help would be greatly appreciated.
    thanks


    Here is the query:

    Code:
    SELECT customer.FirstName, customer.LastName, customer.Male, customer.Female, customer.Phone, tblservice.Followup30, tblservice.Timing, tblservice.Service1, tblservice.Total 
    FROM customer INNER JOIN tblservice ON customer.ID=tblservice.customerID 
    
    WHERE (((tblservice.Followup30)=No) 
    AND ((tblservice.Timing)<=DateAdd("d",-30,Date())) 
    AND ((customer.Phone) Is Not Null)) 
    
    ORDER BY tblservice.Timing;

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Why don't you put in a time boundary, like
    Code:
    AND ((tblservice.Timing) Between Date()-90 And Date()-30
    or some other hard bound?

    Sam

  3. #3
    Join Date
    Mar 2012
    Location
    Toronto Ontario
    Posts
    10

    Response

    Quote Originally Posted by Sam Landy View Post
    Why don't you put in a time boundary, like
    Code:
    AND ((tblservice.Timing) Between Date()-90 And Date()-30
    or some other hard bound?

    Sam

    Hey Sam Thank you for responding, greatly appreciate it.

    I see what your saying regarding the time boundary, the only problem with that is for example using your boundaries above:

    If a customer had come on in say 50 days ago he would show up on the list which is fine. BUT say he came in again say today his older entry would still be in the call list for the employee to call and ask why he hasn't come on in. There is no way for the employee to know if he came in the recent 30 days which poses a problem for the customer as when the employee calls, the customer says "Well I came in today".

    I hope this is clear.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The way I see it, you have the same problem right now. The only way around it is to make the query a Totals query, and search for the Max(Timing) WHERE it is between ...

    Get it?

    I suggest you use Access' query designer to make the change. It's not easy for a novice.

    Sam
    Last edited by Sam Landy; 05-31-12 at 23:48. Reason: Added a suggestion

Tags for this Thread

Posting Permissions

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