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..

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..

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..

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..