AnsweredAssumed Answered

Help building a find...

Question asked by EssexBiker on Dec 16, 2014
Latest reply on Dec 16, 2014 by philmodjunk

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!

 

 

 

 

 

Outcomes