Tables with Relationships and Changing Variables

[nouveauricheinvestments]

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. So, if I create a relationship and
split this into two different tables, and I enforce referenctial
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? I just want to make sure I am doing this right..

 

[R Tanner]

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.  So, if I create a relationship and
split this into two different tables, and I enforce referenctial
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?  I just want to make sure I am doing this right..

 

[R Tanner]

- Show quoted text -

What I am thinking I should probably do is NOT enforce referential
integrity and just allow a lookup field in my child database with only
specific fields from the parent table...Any opinions from the experts?

 

[Ken Sheridan]

Bearing in mind that the definition of expert is that x is an unknown
quantity and spurt is a drip under pressure…..

As you presumably want to keep the rows in the referencing (child) table
with the outdated issues you will need to keep the rows in the referenced
(parent) Issues table to which they map if referential integrity is to be
enforced, which is what you have correctly concluded. In a situation like
this enforcement of referential integrity is desirable, so what I'd suggest
is that in the referenced Issues table you add a Boolean (Yes/No) column
named Current. You can then set the value of this to True for all issues
currently applicable.

For data entry purposes in a form bound to the referencing table you can use
a combo box whose list includes only current issues by means of a RowSource
property such as:

SELECT Issue FROM Issues WHERE Current ORDER BY Issue;

You can only do this satisfactorily, however, if you use 'natural' keys,
i.e. the primary key of Issues is Issue and the corresponding foreign key in
the referencing table is also Issue, both text columns. If you used
'surrogate' numeric keys IssueID and a combo box which hides the bound
IssueID first column in the usual way, then in the form those rows where the
issue is no longer current would show a blank in the combo box. The value of
the control would still be the underlying long integer IssueID value, but the
corresponding text value would not show, because the number which corresponds
to that value is not in the list returned by the RowSource query. Where
natural keys are used however, the text value would show even though the
value is not in the list of current issues.

There is a way of doing it while using surrogate keys; it involves using a
hybrid control of a text box overlying a combo box so they appear to be a
single combo box control, but it’s a little kludgy, and the use of natural
keys avoids the need. In your case I don't see any problems with using
natural keys as presumably all issue values will be distinct. In some
situations its not possible, e.g. with cities where a surrogate CityID is
necessary as city names can be duplicated (I believe there are 4 Staffords in
the USA as well as the original here, where I live – we are twinned with
Stafford Virginia). While I'd not recommend it in your case, if you are
interested in the technique you'll find a demo application which uses hybrid
controls at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[John W. Vinson]

What I am thinking I should probably do is NOT enforce referential
integrity and just allow a lookup field in my child database with only
specific fields from the parent table...Any opinions from the experts?

I would recommend exactly the opposite. For a critique of Lookup Fields see

http://www.mvps.org/access/lookupfields.htm

Note that a lookup field DOES create a lookup table and enforce referential
integrity, so you gain nothing (and lose a lot!) by using this misfeature.

 

[R Tanner]

I would recommend exactly the opposite. For a critique of Lookup Fields see

http://www.mvps.org/access/lookupfields.htm

Note that a lookup field DOES create a lookup table and enforce referential
integrity, so you gain nothing (and lose a lot!) by using this misfeature..
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Okay I am very very good with Excel, but you are both a bit beyond me
in Access - I have 'Access Inside Out' by Jeff Conrad and John Viescas
and I am studying it like it is the good book so give me a second to
see if I can figure out how this plays into action.

 

[R Tanner]

Okay I am very very good with Excel, but you are both a bit beyond me
in Access - I have 'Access Inside Out' by Jeff Conrad and John Viescas
and I am studying it like it is the good book so give me a second to
see if I can figure out how this plays into action.- Hide quoted text -

- Show quoted text -

It is not working...When I try to look up values in my issues table
based on the SQL statement it doesn't work. But I don't think it's a
problem with my SQL statement, because when I put it in a query, it
works just fine...

 

[John W. Vinson]

It is not working...When I try to look up values in my issues table
based on the SQL statement it doesn't work. But I don't think it's a
problem with my SQL statement, because when I put it in a query, it
works just fine...

"Not working" and "my SQL statement" are pretty vague. I have no way to know
what's not working, nor how you have implemented or used your SQL statement.
Give us a hand here, huh?

 

[R Tanner]

"Not working" and "my SQL statement" are pretty vague. I have no way to know
what's not working, nor how you have implemented or used your SQL statement.
Give us a hand here, huh?

I'm not sure what I was doing wrong, but I just deleted the original
form and I recreated it and it's working fine now. Thank you John.
Sorry for the vaguity.