Converting one table to many

Ok, when I first started making my database I didn't understand
relationships, and therefore created a database with just one table for many
different type of data. Now that I have taken the time to learn more and now
understand relationships I would like to divide the Ubertable into its
correct components. The problem is, while I have been learning, I have been
adding data to this table. 17,000+ records. So i dont want to reenter all of
this data.

Is there a way to divide these fields into their proper design without
losing all of my data?

Yes. You can create a related table, use a series of Append queries to
populate it, and then remove the unnecessary fields from the original table.

As an example, say you have a table of students, with a StudentID primary
key (autonumber), and yes/no fields named Basketball, Football, Baseball for
storing student preferences.

1. Create a Sport table, with SportID as primary key, and enter the 3
records.

2. Create the junction table with fields:
StudentID relates to Student.StudentID
SportID relates to Sport.SportID
Save as (say) StudentSport.

3. Create a query using your student table.
In the Criteria row under Basketball, enter:
True

4. Change it to an Append query (Append on Query menu.)
Answer Access that you want to append to the StudentSport table.
Access adds an Append row to the grid.

5. Drag StudentID into the grid.
In the Append row, you need StudentID.

6. Type into the Field row:
SportID: 1
or whatever is the right code for basketball.
In the Append row, choose SportID.

7. Run the query (red exclamation point on the toolbar.)

8. Remove the True from under Basketball.
Put it under Baseball instead.

9. Change the SportID to the code for Baseball, e.g.:
SportID: 2

10. Run the query.

11. Repeat steps 7 - 9 for Tennis, and any other fields.

12. After verifying that the right data is in the StudentSport table, open
the Student table in design view, and delete the yes/no fields.

There's no need to save the query.

It can take a few minutes if you have 40 sports to work through, but it
still beats re-entering 17k rows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
Ok, when I first started making my database I didn't understand
relationships, and therefore created a database with just one table for
many
different type of data. Now that I have taken the time to learn more and
now
understand relationships I would like to divide the Ubertable into its
correct components. The problem is, while I have been learning, I have
been
adding data to this table. 17,000+ records. So i dont want to reenter all
of
this data.

Is there a way to divide these fields into their proper design without
losing all of my data?

For those of us less expert / fluent than Allen, a low tech way to split a
table when most of the data between the future tables is "one to one" is to
add the linking field, copy the table, and then wipe out the unwanted fields
in each of the tables.


"Allen Browne" wrote:

Yes. You can create a related table, use a series of Append queries to
populate it, and then remove the unnecessary fields from the original table.

As an example, say you have a table of students, with a StudentID primary
key (autonumber), and yes/no fields named Basketball, Football, Baseball for
storing student preferences.

1. Create a Sport table, with SportID as primary key, and enter the 3
records.

2. Create the junction table with fields:
StudentID relates to Student.StudentID
SportID relates to Sport.SportID
Save as (say) StudentSport.

3. Create a query using your student table.
In the Criteria row under Basketball, enter:
True

4. Change it to an Append query (Append on Query menu.)
Answer Access that you want to append to the StudentSport table.
Access adds an Append row to the grid.

5. Drag StudentID into the grid.
In the Append row, you need StudentID.

6. Type into the Field row:
SportID: 1
or whatever is the right code for basketball.
In the Append row, choose SportID.

7. Run the query (red exclamation point on the toolbar.)

8. Remove the True from under Basketball.
Put it under Baseball instead.

9. Change the SportID to the code for Baseball, e.g.:
SportID: 2

10. Run the query.

11. Repeat steps 7 - 9 for Tennis, and any other fields.

12. After verifying that the right data is in the StudentSport table, open
the Student table in design view, and delete the yes/no fields.

There's no need to save the query.

It can take a few minutes if you have 40 sports to work through, but it
still beats re-entering 17k rows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
Ok, when I first started making my database I didn't understand
relationships, and therefore created a database with just one table for
many
different type of data. Now that I have taken the time to learn more and
now
understand relationships I would like to divide the Ubertable into its
correct components. The problem is, while I have been learning, I have
been
adding data to this table. 17,000+ records. So i dont want to reenter all
of
this data.

Is there a way to divide these fields into their proper design without
losing all of my data?

First let me say I appreciate you taking the time to write all that out, but
I get confused on a couple of things. First, when I make the SportID is it
supposed to be autonumber like the Student ID? Same for the StudentSportID.

2nd I lost you when you started talking about the Basektball/True thing. So
let me give you an example of what I am working with and maybe you could walk
me through it like your last reply, only with my real world appplication.

Real quickly my database is for my appraisal business. So when I need
comparable sales to do an appraisal, I look for these sales here. One of the
tables I want to seperate out from the main table is a lease data table,
based on lease information for each comparable sale. So I have fields such
as:

Lease Begin Date, Lease Rate, Lease Term, Expenses, Net Operating Income
(Which is a mathematical expression), Lessor, lesee, etc..

My main table right now is called "Building Data". The key field is "ID"
which is an auto number.

The table I want to create will be called "Lease Data"

As usual, thanks in advance for any help you can give me.

"Allen Browne" wrote:

Yes. You can create a related table, use a series of Append queries to
populate it, and then remove the unnecessary fields from the original table.

As an example, say you have a table of students, with a StudentID primary
key (autonumber), and yes/no fields named Basketball, Football, Baseball for
storing student preferences.

1. Create a Sport table, with SportID as primary key, and enter the 3
records.

2. Create the junction table with fields:
StudentID relates to Student.StudentID
SportID relates to Sport.SportID
Save as (say) StudentSport.

3. Create a query using your student table.
In the Criteria row under Basketball, enter:
True

4. Change it to an Append query (Append on Query menu.)
Answer Access that you want to append to the StudentSport table.
Access adds an Append row to the grid.

5. Drag StudentID into the grid.
In the Append row, you need StudentID.

6. Type into the Field row:
SportID: 1
or whatever is the right code for basketball.
In the Append row, choose SportID.

7. Run the query (red exclamation point on the toolbar.)

8. Remove the True from under Basketball.
Put it under Baseball instead.

9. Change the SportID to the code for Baseball, e.g.:
SportID: 2

10. Run the query.

11. Repeat steps 7 - 9 for Tennis, and any other fields.

12. After verifying that the right data is in the StudentSport table, open
the Student table in design view, and delete the yes/no fields.

There's no need to save the query.

It can take a few minutes if you have 40 sports to work through, but it
still beats re-entering 17k rows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
Ok, when I first started making my database I didn't understand
relationships, and therefore created a database with just one table for
many
different type of data. Now that I have taken the time to learn more and
now
understand relationships I would like to divide the Ubertable into its
correct components. The problem is, while I have been learning, I have
been
adding data to this table. 17,000+ records. So i dont want to reenter all
of
this data.

Is there a way to divide these fields into their proper design without
losing all of my data?

Okay, it's not worth pursuing the sports example as that's not what you are
doing. The basics of that example are here if you did want to follow through
on it:
http://allenbrowne.com/casu-23.html
The short answer to your question is that the sport names (text) could be
the primary key, or you could use an AutoNumber. Either way is fine.

But you are handling leasing of buildings. We can't know what's in the table
you need to split up, but I imagine you would want tables like this:

tblClient: one record for each person or company. Fields:
ClientID AutoNumber primary key
MainName Text Surname or company name.
OtherName Text First Name, or contact person.
...

tblBuilding: one record for each building (or part of a building) that gets
leased out.
BuildingID AutoNumber primary key
CurrentOwnerID Number the client who owns the buidling at
present.
Address Text
City Text
...

tblLease: one record for each time a building is leased to a client. Fields:
LeaseID AutoNumber primary key
BuildingID Number what is being leased.
LessorID Number the client offering the lease.
LesseeID Number the client taking out the lease.
LeaseStart Date first day of the lease
LeasePeriodCount Number number of periods between lease renewals.
LeasePeriod Text "d", "w", "m", "q", or "yyyy"
LeaseEnd Date Leave blank until the lease is to be
terminated.
...

A record in the 3rd table says something like building 23 is being leased
out by client 87 (owner) to client 123 (lesee) starting 1/1/2008, and to be
renewed every 2 years.

There will certainly be other fields, and probably other tables, but that's
the basics. That's a very simple structure that doesn't handle co-leases
(where multiple lessees are co-jointly responsible for a lease) etc. There
may well be a need for something more complex, but that's the core concept.

Most of it is pretty straightforward. The reason for breaking the lease term
into 2 fields is that you calculate when the current lease is due for
renewal. Just type into the Field row in query design:
DateAdd([LeasePeriod], [LeasePeriod], [LeaseStart])

And the reason for the LeaseEnd is to provide a way for you to enter the
premature termination of a lease.

Hope that's of use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
First let me say I appreciate you taking the time to write all that out,
but
I get confused on a couple of things. First, when I make the SportID is
it
supposed to be autonumber like the Student ID? Same for the
StudentSportID.

2nd I lost you when you started talking about the Basektball/True thing.
So
let me give you an example of what I am working with and maybe you could
walk
me through it like your last reply, only with my real world appplication.

Real quickly my database is for my appraisal business. So when I need
comparable sales to do an appraisal, I look for these sales here. One of
the
tables I want to seperate out from the main table is a lease data table,
based on lease information for each comparable sale. So I have fields
such
as:

Lease Begin Date, Lease Rate, Lease Term, Expenses, Net Operating Income
(Which is a mathematical expression), Lessor, lesee, etc..

My main table right now is called "Building Data". The key field is "ID"
which is an auto number.

The table I want to create will be called "Lease Data"

As usual, thanks in advance for any help you can give me.

"Allen Browne" wrote:

Yes. You can create a related table, use a series of Append queries to
populate it, and then remove the unnecessary fields from the original
table.

As an example, say you have a table of students, with a StudentID primary
key (autonumber), and yes/no fields named Basketball, Football, Baseball
for
storing student preferences.

1. Create a Sport table, with SportID as primary key, and enter the 3
records.

2. Create the junction table with fields:
StudentID relates to Student.StudentID
SportID relates to Sport.SportID
Save as (say) StudentSport.

3. Create a query using your student table.
In the Criteria row under Basketball, enter:
True

4. Change it to an Append query (Append on Query menu.)
Answer Access that you want to append to the StudentSport table.
Access adds an Append row to the grid.

5. Drag StudentID into the grid.
In the Append row, you need StudentID.

6. Type into the Field row:
SportID: 1
or whatever is the right code for basketball.
In the Append row, choose SportID.

7. Run the query (red exclamation point on the toolbar.)

8. Remove the True from under Basketball.
Put it under Baseball instead.

9. Change the SportID to the code for Baseball, e.g.:
SportID: 2

10. Run the query.

11. Repeat steps 7 - 9 for Tennis, and any other fields.

12. After verifying that the right data is in the StudentSport table,
open
the Student table in design view, and delete the yes/no fields.

There's no need to save the query.

It can take a few minutes if you have 40 sports to work through, but it
still beats re-entering 17k rows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
Ok, when I first started making my database I didn't understand
relationships, and therefore created a database with just one table for
many
different type of data. Now that I have taken the time to learn more
and
now
understand relationships I would like to divide the Ubertable into its
correct components. The problem is, while I have been learning, I have
been
adding data to this table. 17,000+ records. So i dont want to reenter
all
of
this data.

Is there a way to divide these fields into their proper design without
losing all of my data?

1st of all Allen, thanks that second one helped me out a lot.

Fred, while was waiting to hear back from Allen I made a copy of my Database
and did as you suggested. That worked in that I was able to break up the
tables and still retain the data.

Now my next questions is: I have a BUNCH of reports and forms that I had
created. When i try to look fields up that are not in the main table
anymore, it will not find these fields. How do i integrate the new tables
into the old forms and reports. I DEFINATELY don't want to redo all of those
as they each took hours to make.


"Fred" wrote:

For those of us less expert / fluent than Allen, a low tech way to split a
table when most of the data between the future tables is "one to one" is to
add the linking field, copy the table, and then wipe out the unwanted fields
in each of the tables.


"Allen Browne" wrote:

Yes. You can create a related table, use a series of Append queries to
populate it, and then remove the unnecessary fields from the original table.

As an example, say you have a table of students, with a StudentID primary
key (autonumber), and yes/no fields named Basketball, Football, Baseball for
storing student preferences.

1. Create a Sport table, with SportID as primary key, and enter the 3
records.

2. Create the junction table with fields:
StudentID relates to Student.StudentID
SportID relates to Sport.SportID
Save as (say) StudentSport.

3. Create a query using your student table.
In the Criteria row under Basketball, enter:
True

4. Change it to an Append query (Append on Query menu.)
Answer Access that you want to append to the StudentSport table.
Access adds an Append row to the grid.

5. Drag StudentID into the grid.
In the Append row, you need StudentID.

6. Type into the Field row:
SportID: 1
or whatever is the right code for basketball.
In the Append row, choose SportID.

7. Run the query (red exclamation point on the toolbar.)

8. Remove the True from under Basketball.
Put it under Baseball instead.

9. Change the SportID to the code for Baseball, e.g.:
SportID: 2

10. Run the query.

11. Repeat steps 7 - 9 for Tennis, and any other fields.

12. After verifying that the right data is in the StudentSport table, open
the Student table in design view, and delete the yes/no fields.

There's no need to save the query.

It can take a few minutes if you have 40 sports to work through, but it
still beats re-entering 17k rows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
Ok, when I first started making my database I didn't understand
relationships, and therefore created a database with just one table for
many
different type of data. Now that I have taken the time to learn more and
now
understand relationships I would like to divide the Ubertable into its
correct components. The problem is, while I have been learning, I have
been
adding data to this table. 17,000+ records. So i dont want to reenter all
of
this data.

Is there a way to divide these fields into their proper design without
losing all of my data?

Create a query that uses multiple tables.
Use the query as the source for the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...

Now my next questions is: I have a BUNCH of reports and forms that I had
created. When i try to look fields up that are not in the main table
anymore, it will not find these fields. How do i integrate the new tables
into the old forms and reports. I DEFINATELY don't want to redo all of
those
as they each took hours to make.

Ok, I have them all split into the follwoing tables:

Building Data
Appeals Data
Assessed Value data
Demolition Data
Building Permits
Sale Data
Lease Data

Each has an Autonumber ID named after the table (ex:Lease ID) that is the
key field. And those IDs are what I joined for their relationships.

So I went to create a query I was going to call Master Join so that I could
change the source on all of my forms and reports to this query. In the
creation process I added all of the fields from all of the tables into the
query and tried to run it. But I keep getting an error that says : "Too Many
Fields"

I had created one ( a master query) previoulsy that worked, but I found out
I had forgotten to add two of the tables. When I would run the query it
showed only 7,950 records. But when I would run a count on my building data
form it said there 17,150 records (which was the original amount I had in the
Uber table before I split them up.) That is why I went back and noticed the
missing tables.

What am I missing here. Please remember I am a newbie and don't understand
all of the complex stuff I read around here. I have thought of giving up and
just hiring someone to do this, buit I really want to learn it myself. I
tried to take a class my company (City Hall) offered in access, but all that
was available was Access I which just covered data entry stuff. Access II,
which covers this type of stuff is not available because they "can't find
enough people willing to sign up"!! But..they won't offer it for people to
sign up until there is enough interest???? AHHHH!!

Anywho...Thanks again.



"Allen Browne" wrote:

Okay, it's not worth pursuing the sports example as that's not what you are
doing. The basics of that example are here if you did want to follow through
on it:
http://allenbrowne.com/casu-23.html
The short answer to your question is that the sport names (text) could be
the primary key, or you could use an AutoNumber. Either way is fine.

But you are handling leasing of buildings. We can't know what's in the table
you need to split up, but I imagine you would want tables like this:

tblClient: one record for each person or company. Fields:
ClientID AutoNumber primary key
MainName Text Surname or company name.
OtherName Text First Name, or contact person.
...

tblBuilding: one record for each building (or part of a building) that gets
leased out.
BuildingID AutoNumber primary key
CurrentOwnerID Number the client who owns the buidling at
present.
Address Text
City Text
...

tblLease: one record for each time a building is leased to a client. Fields:
LeaseID AutoNumber primary key
BuildingID Number what is being leased.
LessorID Number the client offering the lease.
LesseeID Number the client taking out the lease.
LeaseStart Date first day of the lease
LeasePeriodCount Number number of periods between lease renewals.
LeasePeriod Text "d", "w", "m", "q", or "yyyy"
LeaseEnd Date Leave blank until the lease is to be
terminated.
...

A record in the 3rd table says something like building 23 is being leased
out by client 87 (owner) to client 123 (lesee) starting 1/1/2008, and to be
renewed every 2 years.

There will certainly be other fields, and probably other tables, but that's
the basics. That's a very simple structure that doesn't handle co-leases
(where multiple lessees are co-jointly responsible for a lease) etc. There
may well be a need for something more complex, but that's the core concept.

Most of it is pretty straightforward. The reason for breaking the lease term
into 2 fields is that you calculate when the current lease is due for
renewal. Just type into the Field row in query design:
DateAdd([LeasePeriod], [LeasePeriod], [LeaseStart])

And the reason for the LeaseEnd is to provide a way for you to enter the
premature termination of a lease.

Hope that's of use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
First let me say I appreciate you taking the time to write all that out,
but
I get confused on a couple of things. First, when I make the SportID is
it
supposed to be autonumber like the Student ID? Same for the
StudentSportID.

2nd I lost you when you started talking about the Basektball/True thing.
So
let me give you an example of what I am working with and maybe you could
walk
me through it like your last reply, only with my real world appplication.

Real quickly my database is for my appraisal business. So when I need
comparable sales to do an appraisal, I look for these sales here. One of
the
tables I want to seperate out from the main table is a lease data table,
based on lease information for each comparable sale. So I have fields
such
as:

Lease Begin Date, Lease Rate, Lease Term, Expenses, Net Operating Income
(Which is a mathematical expression), Lessor, lesee, etc..

My main table right now is called "Building Data". The key field is "ID"
which is an auto number.

The table I want to create will be called "Lease Data"

As usual, thanks in advance for any help you can give me.

"Allen Browne" wrote:

Yes. You can create a related table, use a series of Append queries to
populate it, and then remove the unnecessary fields from the original
table.

As an example, say you have a table of students, with a StudentID primary
key (autonumber), and yes/no fields named Basketball, Football, Baseball
for
storing student preferences.

1. Create a Sport table, with SportID as primary key, and enter the 3
records.

2. Create the junction table with fields:
StudentID relates to Student.StudentID
SportID relates to Sport.SportID
Save as (say) StudentSport.

3. Create a query using your student table.
In the Criteria row under Basketball, enter:
True

4. Change it to an Append query (Append on Query menu.)
Answer Access that you want to append to the StudentSport table.
Access adds an Append row to the grid.

5. Drag StudentID into the grid.
In the Append row, you need StudentID.

6. Type into the Field row:
SportID: 1
or whatever is the right code for basketball.
In the Append row, choose SportID.

7. Run the query (red exclamation point on the toolbar.)

8. Remove the True from under Basketball.
Put it under Baseball instead.

9. Change the SportID to the code for Baseball, e.g.:
SportID: 2

10. Run the query.

11. Repeat steps 7 - 9 for Tennis, and any other fields.

12. After verifying that the right data is in the StudentSport table,
open
the Student table in design view, and delete the yes/no fields.

There's no need to save the query.

It can take a few minutes if you have 40 sports to work through, but it
still beats re-entering 17k rows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
Ok, when I first started making my database I didn't understand
relationships, and therefore created a database with just one table for
many
different type of data. Now that I have taken the time to learn more
and
now
understand relationships I would like to divide the Ubertable into its
correct components. The problem is, while I have been learning, I have
been
adding data to this table. 17,000+ records. So i dont want to reenter
all
of
this data.

Is there a way to divide these fields into their proper design without
losing all of my data?

You will not be able to use one query containing all the tables like that,
to enter everything in one form.

For example, you can't enter a new lease for a building until the building
is in the Building table, and you can't enter a new lessee until that client
is in your Client table. Therefore you will need to provide separate forms
where you enter the buildings, and the clients, and the leases.

The reason your master query did not return all records is probably due to
bad data, or nulls in key fields. To prevent bad data, make sure you create
relationships between the tables. Use the Relationships window (Database tab
of ribbon in A2007, or Tools menu in older versions.) When you create
relationships, always check the box for Referential Integrity.

For an explanation of the issue with nulls and joins, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Additionally, there's a fairly good chance that the monster query you build
containing all those tables will not be editable. That's fine if you are
just wanting to create a report from the data, but not useful for a form
where you want to edit it.

So it probably does mean designing new forms to handle the new table
structure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
Ok, I have them all split into the follwoing tables:

Building Data
Appeals Data
Assessed Value data
Demolition Data
Building Permits
Sale Data
Lease Data

Each has an Autonumber ID named after the table (ex:Lease ID) that is the
key field. And those IDs are what I joined for their relationships.

So I went to create a query I was going to call Master Join so that I
could
change the source on all of my forms and reports to this query. In the
creation process I added all of the fields from all of the tables into the
query and tried to run it. But I keep getting an error that says : "Too
Many
Fields"

I had created one ( a master query) previoulsy that worked, but I found
out
I had forgotten to add two of the tables. When I would run the query it
showed only 7,950 records. But when I would run a count on my building
data
form it said there 17,150 records (which was the original amount I had in
the
Uber table before I split them up.) That is why I went back and noticed
the
missing tables.

What am I missing here. Please remember I am a newbie and don't understand
all of the complex stuff I read around here. I have thought of giving up
and
just hiring someone to do this, buit I really want to learn it myself. I
tried to take a class my company (City Hall) offered in access, but all
that
was available was Access I which just covered data entry stuff. Access
II,
which covers this type of stuff is not available because they "can't find
enough people willing to sign up"!! But..they won't offer it for people to
sign up until there is enough interest???? AHHHH!!

Anywho...Thanks again.



"Allen Browne" wrote:

Okay, it's not worth pursuing the sports example as that's not what you
are
doing. The basics of that example are here if you did want to follow
through
on it:
http://allenbrowne.com/casu-23.html
The short answer to your question is that the sport names (text) could be
the primary key, or you could use an AutoNumber. Either way is fine.

But you are handling leasing of buildings. We can't know what's in the
table
you need to split up, but I imagine you would want tables like this:

tblClient: one record for each person or company. Fields:
ClientID AutoNumber primary key
MainName Text Surname or company name.
OtherName Text First Name, or contact person.
...

tblBuilding: one record for each building (or part of a building) that
gets
leased out.
BuildingID AutoNumber primary key
CurrentOwnerID Number the client who owns the buidling at
present.
Address Text
City Text
...

tblLease: one record for each time a building is leased to a client.
Fields:
LeaseID AutoNumber primary key
BuildingID Number what is being leased.
LessorID Number the client offering the lease.
LesseeID Number the client taking out the lease.
LeaseStart Date first day of the lease
LeasePeriodCount Number number of periods between lease renewals.
LeasePeriod Text "d", "w", "m", "q", or "yyyy"
LeaseEnd Date Leave blank until the lease is to be
terminated.
...

A record in the 3rd table says something like building 23 is being leased
out by client 87 (owner) to client 123 (lesee) starting 1/1/2008, and to
be
renewed every 2 years.

There will certainly be other fields, and probably other tables, but
that's
the basics. That's a very simple structure that doesn't handle co-leases
(where multiple lessees are co-jointly responsible for a lease) etc.
There
may well be a need for something more complex, but that's the core
concept.

Most of it is pretty straightforward. The reason for breaking the lease
term
into 2 fields is that you calculate when the current lease is due for
renewal. Just type into the Field row in query design:
DateAdd([LeasePeriod], [LeasePeriod], [LeaseStart])

And the reason for the LeaseEnd is to provide a way for you to enter the
premature termination of a lease.

Hope that's of use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
First let me say I appreciate you taking the time to write all that
out,
but
I get confused on a couple of things. First, when I make the SportID
is
it
supposed to be autonumber like the Student ID? Same for the
StudentSportID.

2nd I lost you when you started talking about the Basektball/True
thing.
So
let me give you an example of what I am working with and maybe you
could
walk
me through it like your last reply, only with my real world
appplication.

Real quickly my database is for my appraisal business. So when I need
comparable sales to do an appraisal, I look for these sales here. One
of
the
tables I want to seperate out from the main table is a lease data
table,
based on lease information for each comparable sale. So I have fields
such
as:

Lease Begin Date, Lease Rate, Lease Term, Expenses, Net Operating
Income
(Which is a mathematical expression), Lessor, lesee, etc..

My main table right now is called "Building Data". The key field is
"ID"
which is an auto number.

The table I want to create will be called "Lease Data"

As usual, thanks in advance for any help you can give me.

"Allen Browne" wrote:

Yes. You can create a related table, use a series of Append queries to
populate it, and then remove the unnecessary fields from the original
table.

As an example, say you have a table of students, with a StudentID
primary
key (autonumber), and yes/no fields named Basketball, Football,
Baseball
for
storing student preferences.

1. Create a Sport table, with SportID as primary key, and enter the 3
records.

2. Create the junction table with fields:
StudentID relates to Student.StudentID
SportID relates to Sport.SportID
Save as (say) StudentSport.

3. Create a query using your student table.
In the Criteria row under Basketball, enter:
True

4. Change it to an Append query (Append on Query menu.)
Answer Access that you want to append to the StudentSport table.
Access adds an Append row to the grid.

5. Drag StudentID into the grid.
In the Append row, you need StudentID.

6. Type into the Field row:
SportID: 1
or whatever is the right code for basketball.
In the Append row, choose SportID.

7. Run the query (red exclamation point on the toolbar.)

8. Remove the True from under Basketball.
Put it under Baseball instead.

9. Change the SportID to the code for Baseball, e.g.:
SportID: 2

10. Run the query.

11. Repeat steps 7 - 9 for Tennis, and any other fields.

12. After verifying that the right data is in the StudentSport table,
open
the Student table in design view, and delete the yes/no fields.

There's no need to save the query.

It can take a few minutes if you have 40 sports to work through, but
it
still beats re-entering 17k rows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tomtheappraiser" wrote in
message
...
Ok, when I first started making my database I didn't understand
relationships, and therefore created a database with just one table
for
many
different type of data. Now that I have taken the time to learn more
and
now
understand relationships I would like to divide the Ubertable into
its
correct components. The problem is, while I have been learning, I
have
been
adding data to this table. 17,000+ records. So i dont want to
reenter
all
of
this data.

Is there a way to divide these fields into their proper design
without
losing all of my data?