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
|
|||
|
|||
Parent Tables and Referential Integrity
Hi,
I have a series of table containing data over the past 2 years I am analyzing with the table analyzer and I am dividing it up and I have one question. We have a column called 'Issues' and that has a series of specific issues that are selected from a drop down (or were - all of this data is being migrated from excel - about 3500 records). The thing is, the options in that drop down have changed over the past 2 years. The same options that were there 2 years ago are no longer an option to select as an issue - in fact there are over 300 different types of issues but we currently only choose from a list of 10. So, if I create a relationship and split this into two different tables, and I enforce referential integrity, it would delete any fields that do not have a reference to the parent table - my 'issues' table. Is that correct? What would you recommend if you were dividing up this table? Maybe I should just not enfore referential integrity when I am defining the relationships and just allow the drop down menu for current records to only select specific values from the parent Issues table. I just want to make sure I am doing this right.. |
#2
|
|||
|
|||
Parent Tables and Referential Integrity
Rob
Why? As in why are you "dividing up this table?" That is, what will having more than one table allow you to do? If you were using a spreadsheet, having a different spreadsheet for different (stores, years, products, fill-in your reason) would probably be how you'd handle this. Access is a relational database, though, and not only do you rarely need to "divide up a table", it's often not a very good idea. More info, please! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Rob" wrote in message news Hi, I have a series of table containing data over the past 2 years I am analyzing with the table analyzer and I am dividing it up and I have one question. We have a column called 'Issues' and that has a series of specific issues that are selected from a drop down (or were - all of this data is being migrated from excel - about 3500 records). The thing is, the options in that drop down have changed over the past 2 years. The same options that were there 2 years ago are no longer an option to select as an issue - in fact there are over 300 different types of issues but we currently only choose from a list of 10. So, if I create a relationship and split this into two different tables, and I enforce referential integrity, it would delete any fields that do not have a reference to the parent table - my 'issues' table. Is that correct? What would you recommend if you were dividing up this table? Maybe I should just not enfore referential integrity when I am defining the relationships and just allow the drop down menu for current records to only select specific values from the parent Issues table. I just want to make sure I am doing this right.. |
#3
|
|||
|
|||
Parent Tables and Referential Integrity
Currently these issues are tracked by about 5 different people. Each record
consists of 10 different fields. These fields have changed and most have been added over the last year or so. I want to have it so that each of these 5 people have a 'front end' where they can enter a ticket in. I want only one or two of those people to have the ability to change issues (I figured I would use a lookup column and have the form change the table the lookup column references), and I could easily track everything with different tables according to several of these fields in my main table. For example, I will have a manager field, an account field, a resolved by field, etc. I want to have a resolved by table, a manager table, etc so that I can easily see which tickets fall under each. From what I understand, access would be perfect for this. It is either this or the IT dept. will step in and start using this service-now product, which I don't like. Any suggestsions Jeff? Thanks in advance. "Jeff Boyce" wrote: Rob Why? As in why are you "dividing up this table?" That is, what will having more than one table allow you to do? If you were using a spreadsheet, having a different spreadsheet for different (stores, years, products, fill-in your reason) would probably be how you'd handle this. Access is a relational database, though, and not only do you rarely need to "divide up a table", it's often not a very good idea. More info, please! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Rob" wrote in message news Hi, I have a series of table containing data over the past 2 years I am analyzing with the table analyzer and I am dividing it up and I have one question. We have a column called 'Issues' and that has a series of specific issues that are selected from a drop down (or were - all of this data is being migrated from excel - about 3500 records). The thing is, the options in that drop down have changed over the past 2 years. The same options that were there 2 years ago are no longer an option to select as an issue - in fact there are over 300 different types of issues but we currently only choose from a list of 10. So, if I create a relationship and split this into two different tables, and I enforce referential integrity, it would delete any fields that do not have a reference to the parent table - my 'issues' table. Is that correct? What would you recommend if you were dividing up this table? Maybe I should just not enfore referential integrity when I am defining the relationships and just allow the drop down menu for current records to only select specific values from the parent Issues table. I just want to make sure I am doing this right.. |
#4
|
|||
|
|||
Parent Tables and Referential Integrity
If the "fields have changed", your table probably works more like a
spreadsheet than a relational database (not a good thing). A relational database (like Access), gives you both a way to efficiently store data, and a very capable searching tool (queries). Without understanding how your data is currently structured, it will be tough to suggest how to query it. But using a separate table for tickets handled one way vs other ways is a formula for a maintenance nightmare! Instead, a single table that has the ticket plus the handling method gives you a easy way to query by handling method (e.g., show me all the tickets handled by "manager"). Regards Jeff Boyce Microsoft Office/Access MVP "Rob" wrote in message ... Currently these issues are tracked by about 5 different people. Each record consists of 10 different fields. These fields have changed and most have been added over the last year or so. I want to have it so that each of these 5 people have a 'front end' where they can enter a ticket in. I want only one or two of those people to have the ability to change issues (I figured I would use a lookup column and have the form change the table the lookup column references), and I could easily track everything with different tables according to several of these fields in my main table. For example, I will have a manager field, an account field, a resolved by field, etc. I want to have a resolved by table, a manager table, etc so that I can easily see which tickets fall under each. From what I understand, access would be perfect for this. It is either this or the IT dept. will step in and start using this service-now product, which I don't like. Any suggestsions Jeff? Thanks in advance. "Jeff Boyce" wrote: Rob Why? As in why are you "dividing up this table?" That is, what will having more than one table allow you to do? If you were using a spreadsheet, having a different spreadsheet for different (stores, years, products, fill-in your reason) would probably be how you'd handle this. Access is a relational database, though, and not only do you rarely need to "divide up a table", it's often not a very good idea. More info, please! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Rob" wrote in message news Hi, I have a series of table containing data over the past 2 years I am analyzing with the table analyzer and I am dividing it up and I have one question. We have a column called 'Issues' and that has a series of specific issues that are selected from a drop down (or were - all of this data is being migrated from excel - about 3500 records). The thing is, the options in that drop down have changed over the past 2 years. The same options that were there 2 years ago are no longer an option to select as an issue - in fact there are over 300 different types of issues but we currently only choose from a list of 10. So, if I create a relationship and split this into two different tables, and I enforce referential integrity, it would delete any fields that do not have a reference to the parent table - my 'issues' table. Is that correct? What would you recommend if you were dividing up this table? Maybe I should just not enfore referential integrity when I am defining the relationships and just allow the drop down menu for current records to only select specific values from the parent Issues table. I just want to make sure I am doing this right.. |
Thread Tools | |
Display Modes | |
|
|