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.
Here is the query:
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
AND ((customer.Phone) Is Not Null))
ORDER BY tblservice.Timing;
AND ((tblservice.Timing) Between Date()-90 And Date()-30
or some other hard bound?
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".