Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Custom VBA functions for data cleaning query

Been on my to-do list for too long, and it makes my head hurt to think about.  Simplified version is trying to combine two tables, CustSalesReps (each record matches a Customer ID to a SalesRep for distinct date range) and CustSalesRepLocations (matches cust&salesrep record to locations).  Usually customers will switch locations more often than sales reps - sales reps can work with customers out of any location.  In CustSalesRep, each record represents a distinct time period for a particular customer&salesrep relationship (ie, Cust001 assigned to Sales500 from Jan1998thruJuly2003.   Then they added CustSalesRepLocations, which lists change of location for a particular CustSalesRep record (date range), so each CustSalesRep can have zero to multiple records in this table showing a location and an "Effective" date.  

Currently got it to this point:

Cust SalesRep SR_EffDate  SR_TermDate  LocID  LocEffDate
1234   501   05/01/1998   05/01/1998   519   06/01/2000 2:53 PM      
1234   505   05/02/1998   01/31/2006   320   06/01/2000 2:53 PM      
1234   505   05/02/1998   01/31/2006   324   05/18/2004 7:22 AM      
1234   505   05/02/1998   01/31/2006   461   02/23/2006 2:51 PM      
1234   503   02/01/2006   02/28/2006   002   02/24/2006 2:42 PM      
1234   505   03/01/2006   12/31/9999   461   03/10/2006 2:48 PM      
1234   505   03/01/2006   12/31/9999   320   03/13/2006 8:53 AM      

Would like to get here:                    

Cust SalesRep SR_EffDate  SR_TermDate  LocID  LocEffDate              S_Loc_Eff                  S_Loc_Term
1234   501   05/01/1998   05/01/1998   519   06/01/2000 2:53PM      
1234   505   05/02/1998   01/31/2006   320   06/01/2000 2:53PM   05/02/1998 12:00AM   05/18/2004 7:22AM
1234   505   05/02/1998   01/31/2006   324   05/18/2004 7:22AM   05/18/2004 7:22AM     01/31/2006 12:00AM
1234   505   05/02/1998   01/31/2006   461   02/23/2006 2:51PM      
1234   503   02/01/2006   02/28/2006   002   02/24/2006 2:42PM   02/01/2006 12:00AM   02/28/2006 12:00AM
1234   505   03/01/2006   12/31/9999   461   03/10/2006 2:48PM   03/01/2006 12:00AM   03/13/2006 8:53AM
1234   505   03/01/2006   12/31/9999   320   03/13/2006 8:53AM   03/13/2006 8:53AM     12/31/9999 12:00AM
                     
Rules (so far):
                     
   If LocEffDate > SR_TermDate, Skip Row                  
                     
   If Different SalesRep than PreviousRec Then                  
      S_Loc_Eff = SR_EffDate              
      S_Loc_Term = lesser of (SR_TermDate or next record's LocEffDate)              
                     
   If Same Sales Rep                  
      S_Loc_Eff = LocEffDate              
      S_Loc_Term = lesser of (SR_TermDate or next record's LocEffDate)

thanks very much!  wes
0
new_wes
Asked:
new_wes
  • 5
  • 5
1 Solution
 
nico5038Commented:
Hmm, basically your CustSalesRepLocations table is obsolete as you have rules to extract the information from the CustSalesReps.

Why store this in a table ?

Nic;o)
0
 
new_wesAuthor Commented:
sorry, wasn't clear on that - the sample data is from a query that combines custsalesrep and locations - well, actually there are 4 tables, but trying to focus on where i'm stuck!  but if you're interested, basically:

CustSalesRep:
Cust SalesRep SR_EffDate  SR_TermDate
1234   501   05/01/1998   05/01/1998  
1234   505   05/02/1998   01/31/2006  
1234   503   02/01/2006   02/28/2006    
1234   505   03/01/2006   12/31/9999

SalesRepLocations:
Cust SalesRep SR_EffDate  LocID  LocEffDate
1234   501   05/01/1998   519   06/01/2000 2:53 PM      
1234   505   05/02/1998   320   06/01/2000 2:53 PM      
1234   505   05/02/1998   324   05/18/2004 7:22 AM      
1234   505   05/02/1998   461   02/23/2006 2:51 PM      
1234   503   02/01/2006   002   02/24/2006 2:42 PM  
1234   505   03/01/2006   461   03/10/2006 2:48 PM      
1234   505   03/01/2006   320   03/13/2006 8:53 AM  

so linking on Cust/SalesRep/SR_EffDate

thanks!!
0
 
new_wesAuthor Commented:
oh! wait - misread your question - my sample tables weren't actually the tables, the first sample was where i am now with my queries.  yes, you're correct i have the rules to get to final, i just don't know how to write the select query! (to get to 2nd sample format (or similar):

Cust SalesRep SR_EffDate  SR_TermDate  LocID  LocEffDate              S_Loc_Eff                  S_Loc_Term
1234   501   05/01/1998   05/01/1998   519   06/01/2000 2:53PM      
1234   505   05/02/1998   01/31/2006   320   06/01/2000 2:53PM   05/02/1998 12:00AM   05/18/2004 7:22AM
1234   505   05/02/1998   01/31/2006   324   05/18/2004 7:22AM   05/18/2004 7:22AM     01/31/2006 12:00AM
1234   505   05/02/1998   01/31/2006   461   02/23/2006 2:51PM      
1234   503   02/01/2006   02/28/2006   002   02/24/2006 2:42PM   02/01/2006 12:00AM   02/28/2006 12:00AM
1234   505   03/01/2006   12/31/9999   461   03/10/2006 2:48PM   03/01/2006 12:00AM   03/13/2006 8:53AM
1234   505   03/01/2006   12/31/9999   320   03/13/2006 8:53AM   03/13/2006 8:53AM     12/31/9999 12:00AM
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nico5038Commented:
To get a next/previous record in a query is possible, but rather "complex".
To get it in one query we'll need a trick to combine two rows to get all data in one.
In general I use an additional sequence number and a query with a correction of that sequencenumber with -1
Then the original sequencenumber is combined with the corrected sequencenumber query and that will combine two rows.

I prefer to use a VBA function in general as that gives more control. How good are your VBA skills ?

Nic;o)
0
 
new_wesAuthor Commented:
Good enough to troubleshoot, but too rusty to write from blank page (hence this question on EE!).  I think i understand what you mean about using a sequence # (nice idea!), but I assumed VBA would be easiest in long run.  This will probably be used monthly, and should plan for about 100k rows (curr only about 10-20k but growing monthly).
0
 
nico5038Commented:
OK,then I'll give a start and allowyou to troubleshoot :-)

function fncCustSalesRepLocations()

dim rs1 as DAO.recordset
dim rs2 as DAO.recordset

set rs1 = currentdb.openrecordset("select * from qryCustSalesRep")
set rs2 = currentdb.openrecordset("select * from qryCustSalesRep")
if rs1.eof  and rs1.bof then
   ' no record action(s)
   exit function
endif
' set rs1 to first record and rs2 to second
rs1.movefirst
rs2.movefirst
rs2.movenext
'init result tabel
currentdb.execute ("delete * from tblResult")
while not rs1.eof
    ' test same
   if rs1.CustSalesRep = rs2!CustSalesRep then
      if rs!LocEffDate > rs!SR_TermDate then
        'skip S_Loc_Eff and S_Loc_Term
        currentdb.execute ("insert into tblResult (Cust, SalesRep, ..) values (" & rs1!Cust & ", " & rs1!SalesRep & ",...")")
       else
         'Your other tests with craeting row having S_Loc_Eff and S_Loc_Term
      endif
   endif
   rs1.movenext
   ' prevent error processing last rs1
   if not rs2.eof then rs2.movenext
wend

end function

Getting the idea ?

Nic;o)
0
 
new_wesAuthor Commented:
hi nic - wait, how do you pronounce your name??

Can't get to it now but tomorrow afternoon will play with it, but looks good - thanks!
0
 
nico5038Commented:
It's "Nico" and the "i" is pronounced like the ee from see and the "o" like the o from show)

I'll be around, just tellme when and where stuck :-)

Nic;o)
0
 
new_wesAuthor Commented:
Got it working great - would post code but too customized to help others i think.  But learned a lot and got it working - thanks!!

- wes
0
 
nico5038Commented:
Well done Wes !
That's the way to become an expert :-)

Success with your application !

Nic;o)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now