 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 200645 Days between multiple occurrences. My brain may explode! (help)
Hi everyone. I really thought I was getting a handle on all this till this evening. Now I am worried that my brain is going to explode. My db has two kinds of customer services, I'll loosely define them as Type_A and Type_B. I have a list telling me which service category id falls under which type. Each service record has a unique service id, customer id, service category id, and a service date. There can be (and usually are) several different service records for each customer from both categories. All I want to see are the the customer ids of those who receive a Type_B service within 45 days of receiving a Type_A service. It gets confusing (and over my head), because of all the different services that can be delivered. A simple datediff won't do it, I don't think. Anyone have any thoughts on how I can do this? Can this even be done? I hope I explained this clearly enough. Any help would be ever so appreciated. Thanks! SJ Use a subquery to find the closest service. The specifics will depend on how your tables are set up. This example assumes that Table1 has these fields: ID primary key CustomerID relates to the primary key of your Customer table. ServiceDate date of the service ServiceType contains 'Type_A' or 'Type_B' or whatever. If that's the kind of thing you have, create a query using Table1. Then type this whole thing (it's one line) into a fresh column in the Field row: ClosestService: (SELECT TOP 1 ServiceDate FROM Table1 AS Dupe WHERE (Dupe.CustomerID = Table1.CustomerID) AND (Dupe.ServiceType = 'Type_B') ORDER BY (Dupe.ServiceDate - Table1.ServiceDate), Dupe.ID) In the Criteria row under the ServiceType field, enter: 'Type_A' For each Type_A service, the subquery will return the date of the closest Type_B service for the same customer. You can now add criteria under this field: Between Table1.ServiceDate - 45 And Table1.ServiceDate + 45 If subqueries are new, see: How to Create and Use Subqueries at: http://support.microsoft.com/?id=209066  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Hi everyone. I really thought I was getting a handle on all this till > this evening. Now I am worried that my brain is going to explode. [quoted text clipped - 13 lines] > Thanks! > SJ Use a subquery to find the closest service. The specifics will depend on how your tables are set up. This example assumes that Table1 has these fields: ID primary key CustomerID relates to the primary key of your Customer table. ServiceDate date of the service ServiceType contains 'Type_A' or 'Type_B' or whatever. If that's the kind of thing you have, create a query using Table1. Then type this whole thing (it's one line) into a fresh column in the Field row: ClosestService: (SELECT TOP 1 ServiceDate FROM Table1 AS Dupe WHERE (Dupe.CustomerID = Table1.CustomerID) AND (Dupe.ServiceType = 'Type_B') ORDER BY (Dupe.ServiceDate - Table1.ServiceDate), Dupe.ID) In the Criteria row under the ServiceType field, enter: 'Type_A' For each Type_A service, the subquery will return the date of the closest Type_B service for the same customer. You can now add criteria under this field: Between Table1.ServiceDate - 45 And Table1.ServiceDate + 45 If subqueries are new, see: How to Create and Use Subqueries at: http://support.microsoft.com/?id=209066 SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Hi everyone. I really thought I was getting a handle on all this till > this evening. Now I am worried that my brain is going to explode. [quoted text clipped - 13 lines] > Thanks! > SJ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.