How do I take the differnet dates

G

Guest

How do I take the dates that are different in the Effective Date field (see
bottom two dates) and incert them in the field named Start date. I know this
is redundant data but the tables have a lot more data to them and they have
been manually entered. So I must locate, sort extract then make a report
based on all this data. But for now I need to know how to do the below
everything a date if different in that series.
Start Date Effective Date
05/01/2001 05/01/2001
05/01/2001 05/01/2004
05/01/2001 06/01/2004
05/01/2001 05/01/2005
05/01/2001 05/01/2006
05/01/2001 06/01/2006
05/01/2001 07/01/2006
 
G

Guest

Somehow I do not see from your data example what constitutes a series of
dates in the Effective Date field. Can you explain it for me as I see
3-year, 1-month, 11-month, 1-year, 1-month, and 1-month separating the
entries. I do not see a pattern (series).
 
S

Smartin

TKM said:
How do I take the dates that are different in the Effective Date field (see
bottom two dates) and incert them in the field named Start date. I know this
is redundant data but the tables have a lot more data to them and they have
been manually entered. So I must locate, sort extract then make a report
based on all this data. But for now I need to know how to do the below
everything a date if different in that series.
Start Date Effective Date
05/01/2001 05/01/2001
05/01/2001 05/01/2004
05/01/2001 06/01/2004
05/01/2001 05/01/2005
05/01/2001 05/01/2006
05/01/2001 06/01/2006
05/01/2001 07/01/2006
Click to expand...

TKM, it seems you have been asking this question in different ways. On
this, my third read, I still do not understand at all what you are
trying to do.

Here, you indicate the last two rows are significant in some way but I
do not understand why?
- All rows have the same StartDate
- Three rows have an EffectiveDate in 2006
- Three rows have an EffectiveDate (Month) different from StartDate (Month)

What defines the "series"? What tells us that "06/01/2006" and
"07/01/2006" belong in StartDate as opposed to other Dates? Nothing in
this example makes it plain.

Sorry, we need a clearer explanation of your data and what you are
trying to do.
 
G

Guest

Thank you for your reply. I know it is hard to understand so I will try and
clarify and I will keep my question in this one post.
As you can see from the last two dates under Effective Date they are
6/01/2006 and 07/01/2006. Because the next series of dates under Start Date
is 07/02/2001 this means those numbers are missing from the entry and need to
begin under Start Date to keep them in sequence. In this case the 6/01/2006
and 07/01/2006 needs to be moved and the year also needs to reflect 2001 to
keep in sequence. This may happen from time to time so I need Access to run
though the dates and if the dates do not fall in sequence under Start date I
need to to put it there and start with the correct dte and year. Below this
data I have added another set of dates and you will see how it looks again on
my sheet. I really hope I explained this better and hope you can help. I am
in a bind. Thanks again!
Start Date Effective Date
05/01/2001 05/01/2005
05/01/2001 05/01/2006
05/01/2001 06/01/2006
05/01/2001 07/01/2006

07/02/2001 07/02/2001
07/02/2001 07/01/2004

Second series of dates and notice that the start date in the break begins
with 10/02/2001 and under the Effective Date it DOES HAVE A 10/01/2004. THIS
MEANS IT MUST BE PLACED UNDER START AND EFFECTIVE DATE the (10/01/2004) but
the year must be changed in sequence of the NEXT start date of 10/01/2001. I
really hope this helps and thanks again!

Start Date Effective Date
09/15/2001 09/15/2004
09/15/2001 10/01/2004
09/15/2001 09/01/2005

10/02/2001 10/02/2001
10/02/2001 10/02/2004
 
S

Smartin

TKM said:
Thank you for your reply. I know it is hard to understand so I will try and
clarify and I will keep my question in this one post.
As you can see from the last two dates under Effective Date they are
6/01/2006 and 07/01/2006. Because the next series of dates under Start Date
is 07/02/2001 this means those numbers are missing from the entry and need to
begin under Start Date to keep them in sequence. In this case the 6/01/2006
and 07/01/2006 needs to be moved and the year also needs to reflect 2001 to
keep in sequence. This may happen from time to time so I need Access to run
though the dates and if the dates do not fall in sequence under Start date I
need to to put it there and start with the correct dte and year. Below this
data I have added another set of dates and you will see how it looks again on
my sheet. I really hope I explained this better and hope you can help. I am
in a bind. Thanks again!
Start Date Effective Date
05/01/2001 05/01/2005
05/01/2001 05/01/2006
05/01/2001 06/01/2006
05/01/2001 07/01/2006

07/02/2001 07/02/2001
07/02/2001 07/01/2004

Second series of dates and notice that the start date in the break begins
with 10/02/2001 and under the Effective Date it DOES HAVE A 10/01/2004. THIS
MEANS IT MUST BE PLACED UNDER START AND EFFECTIVE DATE the (10/01/2004) but
the year must be changed in sequence of the NEXT start date of 10/01/2001. I
really hope this helps and thanks again!

Start Date Effective Date
09/15/2001 09/15/2004
09/15/2001 10/01/2004
09/15/2001 09/01/2005

10/02/2001 10/02/2001
10/02/2001 10/02/2004
Click to expand...

Is this correct then?

If there is a month/day in "Effective" that lies between any month/day
pair in "Start" then,
Insert the month/day from "Effective" into "Start" and
Change the "Start" year to match the latter of the matched pair in
"Start" and
Copy the original "Effective" day to the new row
 
G

Guest

For starters thank you for helping me with this problem. Yes I think you
almost have it. Yes if there is a different date such as the 06/01/XXXX that
does not match the rest of the dates such as this example (notice the
11/01/2004?) this date must be moved into the Start date column and the
Effective Date colunm and the year must to be changed to match the year from
the Start Date which would be 2001. Is this clear? Thanks you so much for
your help. I was thinking of a cross tab but I need to find a way to get them
in the correct order etc before I can make the crosstab. Thanks again and I
will be waiting for you reply!
Start Date Effective Date
10/02/2001 10/02/2001
10/02/2001 10/02/2004
10/02/2001 11/01/2004
10/02/2001 10/01/2005
 
S

Smartin

I can't think of a SQL approach to doing this. If it were me, I would
duck my tail and go running for VBA where I think this could be handled
most easily.

Here's what I would do in stub form:

- Load Start & Eff into a recordset
- Loop through Eff values
- Loop through Start values
- Append Eff values to the table where Eff.mm/dd does not have a
match in Start.mm/dd, doing any yyyy translations required

Sorry I can't be of more help with this.
 
G

Guest

This is IT! Thank you and how do I go about doing that. Please remember if
there is a different date other than what falls under that paticualr month.
Then all f those dates needs to be moved and show the correct year. Thank you
so much and I will check back for an answer. Take care
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

extract 3 numbers from a string 0
DATES - Grouping sets of sequential dates 3
How do I Turn a Spreadsheet Type format INTO....PLEASE HELP! 8
Date Parameter - Desperate for HELP!!!!!!!! 4
Find the most recent date for a record 2
How can I do this date manipulation 3
next date?? 1
Average interpurchase times 4

Top