3 Replies Latest reply on Dec 16, 2014 4:03 PM by philmodjunk

    Help building a find...

    EssexBiker

      Title

      Help building a find...

      Post

      Customer table ( with other fields such as names addresses etc)

                                                                                                                                                                                                               
                     

      CustomerID

                     
                     

      Customer001

                     
                     

      Customer002

                     
                     

      Customer003

                     
                     

      Customer004

                     

       

      Ordrers table

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                     

      CustomerID

                     
                     

      ValidFrom

                     
                     

      Expires

                     
                     

      CN_lastValidFrom

                     
                     

      CN_LastExpires

                     
                     

      Customer001

                     
                     

      01 Jan 15

                     
                     

      13 Feb 15

                     
                     

       

                     
                     

       

                     
                     

      Customer001

                     
                     

      03 Nov 14

                     
                     

      19 Dec 14

                     
                     

       

                     
                     

       

                     
                     

      Customer001

                     
                     

      03 Sept 14

                     
                     

      24 Oct 14

                     
                     

       

                     
                     

       

                     
                     

      Customer001

                     
                     

      04 Apr 14

                     
                     

      23 Jul 14

                     
                     

       

                     
                     

       

                     
                     

      Customer002

                     
                     

      03 Sep 14

                     
                     

      19 Dec 14

                     
                     

       

                     
                     

       

                     
                     

      Customer003

                     
                     

      01 Jan 12

                     
                     

      05 Apr 12

                     
                     

       

                     
                     

       

                     
                     

      Customer003

                     
                     

      03 Sept 11

                     
                     

      22 Dec 12

                     
                     

       

                     
                     

       

                     
                     

      Customer004

                     
                     

      03 Sept 14

                     
                     

      23 Jul 14

                     
                     

       

                     
                     

       

                     

       

       

       

       

       

       

       

       

       

       

       

       

       

      Due to business changes our company is migrating away from the current database that I have developed to keep track of ,amongst other things ,customers and their orders. We are effectively keeping records of travel passes issued. So this could be viewed as a subscription. We’ve moved over electronic smartcards, which will be managed by an external supplier. Ultimately all the administration will be carried out via  Client Management System they provide as a  S.O.S.

       

      As a working example:

       

      What I need is to create a simple list of Customers that have had a valid pass or passes issued after 01st September 2014 and that as of the 01st January no longer have a valid pass. (Those that have expiry prior to the 31st Dec 2014)

       

      These who have not renewed I need to pass to a supplier as a simple list of customers, ideally with their last expiry date for them to action a “Hotlist” of non-payers.

       

      I only want to show one line for each client, historic orders are irrelevant.

       

      I do not need to include any customers or their orders that have had passes historically prior to September 2014 but none after that date.

       

      You’ll see that passes have been sold for different validity periods.

       

      A few tricks up my sleeve have been to do a calculation to figure the most recent ValidFrom and expiry using the LAST function and sorting via the relationships table this though I am not entirely sure will bring me any benefit.

       

      I could do a calculation to say return a text string like valid or Expired, but of course I could end with 1000’s of irrelevant expired for orders prior to Sept 14!

       

      So, if any bright spark can help me build a query just to present those who've had a pass from September 14 onward but as of 01 Jan will no longer have a valid pass, I WOULD BE DEEPLY GRATEFUL!