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
Reply to group, rather than allenbrowne at mvps dot org.
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.
:
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
Reply to group, rather than allenbrowne at mvps dot org.
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?
