Hi!
In Access,
I am trying to compare date field on one record to the next record grouped by product names:
Can someone please give me some directions on how it can be done?
Here is one example:
UNIQ_KEY PROD_CAT_CD PROD_SH_NM ENR_EFF_DT_SK ENR_TERM_DT_SK
123456 DNTL DENTPC 1998-11-15 2000-07-31
123456 DNTL DENTPC 2002-06-01 2004-01-31
123456 DNTL DENTPC 2004-05-01 2008-09-30
123456 MED PCB 1998-11-15 2000-07-31
123456 MED PCB 2002-06-01 2004-01-31
123456 MED PCB 2004-05-01 2008-09-30
123456 MED PCB 2010-07-01 2010-12-31
123456 MED PCB 2011-01-01 2199-12-31

Iam trying to compare the ENR_TERM_DT_SK on first record to the ENR_EFF_DT_Sk on the next record grouped by PROD_SH_NM and if it’s the same product name on the next record and if the Term Date on the first record is just a day before the Effective date on next record then it’s called unbroken enrollment and will be populated as one record in final table.
UNIQ_KEY PROD_CAT_CD PROD_SH_NM ENR_EFF_DT_SK ENR_TERM_DT_SK
123456 DNTL DENTPC 1998-11-15 2000-07-31
123456 DNTL DENTPC 2002-06-01 2004-01-31
123456 DNTL DENTPC 2004-05-01 2008-09-30
123456 MED PCB 1998-11-15 2000-07-31
123456 MED PCB 2002-06-01 2004-01-31
123456 MED PCB 2004-05-01 2008-09-30
123456 MED PCB 2010-07-01 2199-12-31

So, I am trying to figure out how it can be written in access to figure out if the enrollment is unbroken or not.