If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Converting one table to many
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? |
#3
|
|||
|
|||
Converting one table to many
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? |
#4
|
|||
|
|||
Converting one table to many
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? |
#5
|
|||
|
|||
Converting one table to many
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? |
#6
|
|||
|
|||
Converting one table to many
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? |
#7
|
|||
|
|||
Converting one table to many
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. |
#8
|
|||
|
|||
Converting one table to many
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? |
#9
|
|||
|
|||
Converting one table to many
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? |
Thread Tools | |
Display Modes | |
|
|