Move Data into a blank field in a query

[Guest]

My data looks like this when it arrives into my tables (queries)...

Start Date End Date Initial Rate 3rd Contract 4th 5th
11/20/2001 12/25/2001 4.5
11/20/2001 11/30/2004 3.6
11/20/2001 12/31/2004 3.5
11/20/2001 11/08/2005 3.0

What I need to do is leave the top line intact up to the Initial Rate field
then move the next INITIAL RATE data to the 3RD CONTRACT, 4TH AND 5TH line
FIELDS without any other data needed to be shown.

It should look like this in the query when it is done.

Start Date End Date Initial Rate 3rd Contract 4th 5th
11/20/2001 12/25/2001 4.5 3.6 3.5 3.0

There is no data in the 3rd, 4th and 5th fileds. This is where the Initial
Rate data needs to be moved to. How do I go about doing this. I am so stumped
I dont know where to begin.

 

[Jeff Boyce]

Perhaps a confusion of terminology...

Access has tables. These are the "physical" containers that hold data.

Access has queries. These are the SQL statements that return a collection
of data from the tables. (and sometimes, depending on the type of and
fields in the query, it can be used to update the values in the fields in
the tables)

If your table structure is like what you described, you have ... a
spreadsheet! In a well-normalized relational database table, you don't have
"repeating fields".

Is there a reason you are using Access instead of a spreadsheet?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

It seems the data is on a spreadsheet but they want to put the data into
tables and generate a report from that. I wish it was normalized but it is
not so I need to figure out how to do it and extract the data to give them
exactly what they want. I will take any help you can provide.

 

[Guest]

I forgot to mention if the tables were broken down I would be able to keep
the dates minimized and would work much better. But because their is
different interest rates with each date (sometimes redundant) it is tougher
to figure out. Please help if you can I would really appreciate it!

 

[Jeff Boyce]

How the data comes to you and how you choose to store it in Access are two
totally separate issues. If "normalization" and "relational database" are
foreign terms for you, spend the time to learn about them before you
proceed. Otherwise, you'll be banging your head against Access the whole
time (hard on you both).

You won't get (easy) use of Access' features & functions if you feed it
spreadsheets...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

The terms are NOT foreign and I understand how it works 4 years of it. As I
stated the company wants it one way which is not the correct way. It is not
normlized, it is redundant etc but this is my job so I am trying to figure
out a way to handle this issue. I thought that is what this forum is for?

 

[Jeff Boyce]

Since all of us here are volunteers, you get what you pay for <g>!

I'm asking the questions to make sure I understand both the WHAT and the
WHY.

It sounds like you are saying "I know what I am trying to do is wrong and
will cause more work, but just tell me how to do it MY WAY."

Take a look at creating a "normalizing UNION query" as a way to gather all
the data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you I will start with that. Sorry for the harsh words but when I walk
into a situation blindfolded I get what you said I will get "you get what you
pay for". I am not the type of person to tell a company it cant be done when
it can. All I can do is tell them the way it should be done and if they go
that way then GREAT! if not we all know more problems down the highway for
them. Thanks again

 

[Guest]

I dont know what I was thinking I already have all the fields but they are
all in one field such as. Interest Rate 75 per cent of the data needs to be
distributed into two or three other fields that dont exist..such as 3rd
contract rate, 4th contact rate etc. The data is like a spreadsheet and I
need to make it look like one with a lot more stuff that needs to be done to
it besides the above. But being able to seprate some of those fields into
other fields is a place to start. Thanks again

 

[Guest]

Jeff 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
02/27/2006 02/27/2006
03/15/2006 03/15/2006
04/14/2006 04/14/2006
08/15/2006 08/15/2006

 

[Jeff Boyce]

Hopefully one of the other responders can offer clarification.

I'm more confused now than when we started.

I believe other responders have suggested the use of a crosstab query to
generate the data output you want.

You can export a query('s output) to Excel, if needed.

Please keep in mind that the form in which you receive data is NOT the
structure/form you need to store it in using Access. If your data structure
is well-normalized, you'll be able to use Access to build cross-tab queries
and reports. If it isn't, you (and Access) will struggle.

Regards

Jeff Boyce
Microsoft Office/Access MVP