How do I Turn a Spreadsheet Type format INTO....PLEASE HELP!

[Guest]

a standard type spreadsheet Access report? I have the following fields in my
table and they look like this.

Start Date Effective Date Interest Rate
05/01/2001 05/01/2001 5.65
05/01/2001 05/01/2004 4.00
05/01/2001 06/01/2004 4.10
05/01/2001 05/01/2005 3.00
05/01/2001 05/01/2006 3.41
05/01/2001 06/01/2006 3.30

I need the report to look like this. Notice that the Interest rate above is
moved in the 3rd and 4th Contract Ann fields with no date?
Start Date End Date Interest Rate 3rd Contract Ann 4th Contract Ann
05/01/2001 07/01/2001 5.65 4.00 4.10
05/01/2001 05/31/2005 3.00 3.41 3.30

How do I take this type of data and make it look like a spread sheet? Can I
do it as a crosstab? I have tried but no luck. There is a lot more to it but
for now I need to take one step at a time. I WOULD APPRECAITE ANY HELP!

 

[DanielS via AccessMonster.com]

There must be more fields that we don't see?
3rd and 4th references, aer these identified by a check box maybe?
It seems that the data shown is incomplete to make a suggestion!

Daniel S.

 

[Guest]

No that is the complicated part that nobody wants to help with. I need to
have access put these in a a blank field or? as they becaome avail. If you
look down down the series of dates, you will notice the rates are filled into
the 3rd, 4th etc. That is what I need to do. I need to disreguard the dates
and have the interest rates move into the correct place. Notice that the
second date shows a 4.0? that goes into the 3rd and the next line goes into
the 4th etc. It is a spread sheet type format and I need to figure out a way
to break it apart so it all fills in correctly. Any help would be appreciated.

 

[DanielS via AccessMonster.com]

Got your reply, Iwill send a possible fix tomorrow!!!!

No that is the complicated part that nobody wants to help with. I need to
have access put these in a a blank field or? as they becaome avail. If you
look down down the series of dates, you will notice the rates are filled into
the 3rd, 4th etc. That is what I need to do. I need to disreguard the dates
and have the interest rates move into the correct place. Notice that the
second date shows a 4.0? that goes into the 3rd and the next line goes into
the 4th etc. It is a spread sheet type format and I need to figure out a way
to break it apart so it all fills in correctly. Any help would be appreciated.

There must be more fields that we don't see?
3rd and 4th references, aer these identified by a check box maybe?

[quoted text clipped - 23 lines]

 

[Guest]

Thank you very much. I will wait for your reply.

Got your reply, Iwill send a possible fix tomorrow!!!!

No that is the complicated part that nobody wants to help with. I need to
have access put these in a a blank field or? as they becaome avail. If you
look down down the series of dates, you will notice the rates are filled into
the 3rd, 4th etc. That is what I need to do. I need to disreguard the dates
and have the interest rates move into the correct place. Notice that the
second date shows a 4.0? that goes into the 3rd and the next line goes into
the 4th etc. It is a spread sheet type format and I need to figure out a way
to break it apart so it all fills in correctly. Any help would be appreciated.

There must be more fields that we don't see?
3rd and 4th references, aer these identified by a check box maybe?

[quoted text clipped - 23 lines]

do it as a crosstab? I have tried but no luck. There is a lot more to it but
for now I need to take one step at a time. I WOULD APPRECAITE ANY HELP!

 

[DanielS via AccessMonster.com]

Start Date End Date Interest Rate 3rd Contract Ann 4th Contract
Ann
05/01/2001 07/01/2001 5.65 4.00 4.10
05/01/2001 05/31/2005 3.00 3.41 3.30
Where do you get the end date from, not in the original data? Why 7-01-01 and
the 5-31-01?
Is there only and always 3 records? Is this always referencing 3rd and 4th
Contract Ann?

Thank you very much. I will wait for your reply.

Got your reply, Iwill send a possible fix tomorrow!!!!

[quoted text clipped - 12 lines]

 

[Guest]

OOps. I hit the wrong key on the last date.. it should look like this

I need the report to look like this. Notice that the Interest rate above is

moved in the 3rd and 4th Contract Ann fields with no date?

Start Date End Date Interest Rate 3rd Contract Ann 4th Contract Ann
05/01/2001 05/31/2001 5.65 4.00 4.10
06/01/2001 05/31/2005 3.00 3.41 3.30

The end date is based off the start date -1
No the data is not in any sequence and the Ann rates are the Initial Rates
+3%. I alraedy have that figured out but all those dates inbeween such as..
Start Date Effective Date Interest Rate
07/02/2001 07/01/2006 3.10
07/02/2001 08/01/2006 3.00
07/02/2001 07/01/2005 3.00
07/02/2001 07/01/2004 4.10
07/02/2001 07/02/2001 5.30

those Interest rates are actually the 3rd, 4th, etc contract rates THIER MAY
BE TWO, THREE, 4 IT JUST DEPENDS. In the example above thier would be 4
because the matching 07/02/2001 is an INTEREST RATE and the others are
Contract Ann Rates which would be moved to the right side of 07/02/2001.

All it is you would take the two matching dates START AND EFF DATE, any
other dates that are different in the eff date would become the 3rd, 4th etc
Ann Rates and if there is a date under Eff Date such as the 08/01..that would
be placed under the Start date and the year would be changed to match in this
case 2001. The end date follows along and takes -1 date off the start date.

Hoep this helps?

Start Date End Date Interest Rate 3rd Contract Ann 4th Contract
Ann
05/01/2001 07/01/2001 5.65 4.00 4.10
05/01/2001 05/31/2005 3.00 3.41 3.30
Where do you get the end date from, not in the original data? Why 7-01-01 and
the 5-31-01?
Is there only and always 3 records? Is this always referencing 3rd and 4th
Contract Ann?

Thank you very much. I will wait for your reply.

Got your reply, Iwill send a possible fix tomorrow!!!!

[quoted text clipped - 12 lines]

do it as a crosstab? I have tried but no luck. There is a lot more to it but
for now I need to take one step at a time. I WOULD APPRECAITE ANY HELP!

 

[Guest]

daniel, I think the easiest way for you to understand this I will post what
the
report is supose to look like and below that show you how I get it in the
Access tables.

Completed report. Remember this is a spreadsheet done manually. They want it
in Access and that is what I am trying to do.
Start Date End Date Initial Rate* 3rd 4th
5th
05/01/2001 05/31/2001 5.35 4.00 3.00 3.41
06/01/2001 06/30/2001 5.35 4.10 3.00 3.30
07/01/2001 07/01/2001 5.35 4.10 3.00
07/02/2001 08/31/2001 5.00 4.10 3.00
09/01/2001 09/14/2001 4.75 4.00 3.00
09/15/2001 09/30/2001 4.00 4.00 3.00
10/01/2001 10/01/2001 4.00 3.75 3.00
10/02/2001 10/31/2005 4.20 3.75 3.00
11/01/2005 11/06/2001 4.20 3.60 3.00
11/07/2001 11/19/2001 3.95 3.60 3.00
11/20/2001 11/30/2001 4.20 3.60 3.00
12/01/2001 12/25/2001 4.20 3.50 3.00
12/26/2001 12/31/2001 4.45 3.50 3.00
01/01/2002 02/06/2002 4.45 3.00 3.00
02/07/2002 03/21/2002 4.30 3.00 3.00
03/22/2002 03/31/2005 4.45 3.00 3.00
04/23/2002 04/30/2002 4.30 3.00 3.00
05/01/2002 05/31/2002 4.30 3.00 3.00
06/01/2002 06/12/2002 4.30 3.00 3.00
06/13/2002 06/30/2002 4.15 3.00

The Contract rates can grow in 6th 7th etc but will be awhile.

Here is my query and just a sample. Notice that the End Date is less one day
before the next start date (see above). I will only show you the Start and
Effective Dates so I dont confuse you more. But notice how many extra dates
fall inbetween each month series. That is where the 3rd, 4th etc rates come
from. Hope this helps.

Start Date Effective Date
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
05/01/2001 05/01/2001
07/02/2001 07/01/2006
07/02/2001 08/01/2006
07/02/2001 07/01/2005
07/02/2001 07/01/2004
07/02/2001 07/02/2001
09/01/2001 09/01/2004
09/01/2001 09/01/2005
09/01/2001 09/01/2001
09/15/2001 10/01/2004
09/15/2001 09/01/2005
09/15/2001 09/15/2004
09/15/2001 09/15/2001
10/02/2001 10/02/2004
10/02/2001 11/01/2004
10/02/2001 10/01/2005
10/02/2001 10/02/2001
11/07/2001 11/07/2001
11/07/2001 11/01/2004
11/07/2001 11/01/2005
11/20/2001 11/01/2005
11/20/2001 11/20/2001
11/20/2001 11/01/2004
11/20/2001 12/01/2004
12/26/2001 01/01/2005
12/26/2001 12/26/2001
12/26/2001 12/01/2004
02/07/2002 02/07/2002
02/07/2002 02/01/2005
03/22/2002 03/22/2002
03/22/2002 03/01/2005
04/23/2002 04/23/2002
04/23/2002 04/01/2005
06/13/2002 06/13/2002
06/13/2002 06/01/2005
07/10/2002 07/01/2005
07/10/2002 07/10/2002
07/30/2002 07/30/2002
07/30/2002 07/01/2005
08/22/2002 08/22/2002
08/22/2002 08/01/2005
10/02/2002 10/02/2002
10/02/2002 10/01/2005
12/31/2002 12/31/2002
12/31/2002 12/01/2005
03/17/2003 06/01/2006
03/17/2003 03/17/2003
03/17/2003 03/01/2006
03/17/2003 05/01/2006
06/09/2003 07/01/2006
06/09/2003 06/09/2003
06/09/2003 06/01/2006
07/29/2003 07/29/2003
07/29/2003 07/01/2006
07/29/2003 08/01/2006
08/13/2003 08/13/2003
08/13/2003 08/01/2006
08/29/2003 08/29/2003
08/29/2003 08/01/2006
08/29/2003 09/01/2006
10/03/2003 10/03/2003
01/20/2004 01/20/2004
04/19/2004 04/19/2004
05/11/2004 05/11/2004
06/17/2004 06/17/2004
06/17/2004 06/17/2004
08/02/2004 08/02/2004
08/02/2004 08/02/2004
09/13/2004 09/13/2004
09/13/2004 09/13/2004
03/09/2005 03/09/2005
03/09/2005 03/09/2005
03/15/2005 03/15/2005
03/31/2005 03/31/2005
06/14/2005 06/14/2005
08/05/2005 08/05/2005
08/12/2005 08/12/2005
09/15/2005 09/15/2005
10/03/2005 10/03/2005
10/20/2005 10/20/2005
11/09/2005 11/09/2005
01/18/2006 01/18/2006
02/07/2006 02/07/2006

Start Date End Date Interest Rate 3rd Contract Ann 4th Contract
Ann
05/01/2001 07/01/2001 5.65 4.00 4.10
05/01/2001 05/31/2005 3.00 3.41 3.30
Where do you get the end date from, not in the original data? Why 7-01-01 and
the 5-31-01?
Is there only and always 3 records? Is this always referencing 3rd and 4th
Contract Ann?

Thank you very much. I will wait for your reply.

Got your reply, Iwill send a possible fix tomorrow!!!!

[quoted text clipped - 12 lines]

do it as a crosstab? I have tried but no luck. There is a lot more to it but
for now I need to take one step at a time. I WOULD APPRECAITE ANY HELP!

 

[Guest]

Daniel,

Were you able to help me on this? Please advise I am in desprate need.
Thanks again for all your help