Quick questions on "double" relationship for a table

[Jeff Conrad]

Hi,

Using Access 97 here.

I have a table of states with the following fields:

StateID - PK Autonumber
StateCode - Text - 2 digits
StateName - Text

(I realize an autonumber PK wasn't really needed since the
state codes and names are short, not duplicated, and
stable, but I made this quite a while ago)

Anyway, I have several 1-M relationships with other tables
in the database from tblStates.

One table of contacts (tblContacts) now has 2
relationships with this table. This is the first time I've
run into this situation so I wanted to make sure this is
the correct way to handle this scenario. I have
address/city/state information already in the table, but I
needed to have an alternate address/city/state as well.

Incidentally, I know that perhaps setting up a third table
of address information for a M-M relationship might be the
better road to go, but let me just stick with this for the
moment.

When I dragged the StateID field from tblStates to
tblContacts in the relationships window Access said
something like "A relationship already exists with this
table. Do you wish to edit that one?" I said No and a
tblStates_1 appeared in the window. I was then able to
make a relationship between this alias (???) table and
tblContacts.

So first question: Is this OK as far as the relationship
window is concerned? Will data integrity still be OK since
I checked enforce RI?

I ran into some problems with a query on tblContacts used
to generate contact reports. To be honest, I can't write
SQL all that well so I rely on playing in the Design View
until I get things just right. It works very well for me.

My troubles came if there was no information in the
alternate StateID field. The query would then return
nothing in the first state fields even if there was data
there! So lots of trial and error later with join lines I
was able, I think, to make it work by bringing a second
tblStates into the query design grid. Again it did a
tblStates_1 thing. I adjusted the join lines and
everything "seems" to be working fine. I get data
displayed properly whether there are empty fields or not.

So second question: Is it all right to have this "alias"
table in the query?? Am I screwing things up??

Thanks for any help,
Jeff Conrad
Access Junkie
Bend, Oregon

 

[John Vinson]

"A relationship already exists with this
table. Do you wish to edit that one?" I said No and a
tblStates_1 appeared in the window. I was then able to
make a relationship between this alias (???) table and
tblContacts.

So first question: Is this OK as far as the relationship
window is concerned? Will data integrity still be OK since
I checked enforce RI?

Yes, and yes.

I ran into some problems with a query ...

So second question: Is it all right to have this "alias"
table in the query?? Am I screwing things up??

Yes, and no.

 

[Jeff Conrad]

Hi John,

Ok, thanks for the answers.
Those were the answers I was hoping for, just wanted to be
sure. I had not run into this situation before, but I
remember something about these alias tables being bad.
Maybe I was thinking of the look-up field relationships??

Thanks for the help,
Jeff Conrad
Access Junkie
Bend, Oregon

 

[Ben]

Yup, you're thinking of lookup relationships being bad. Access lookup
fields are one of the worst 'features' I've ever seen.

BK

 

[John Vinson]

I had not run into this situation before, but I
remember something about these alias tables being bad.
Maybe I was thinking of the look-up field relationships??

The multiple aliases are the way that Access displays multiple
relationships. Sometimes, as in this case, you quite legitimately have
multiple relationships between two tables, and it's fine.

If you've used a Lookup field, Access will have created a new
relationship - *even if a relationship already exists*. Since these
would be two identical relationships, between the same pair of fields,
that would NOT be OK; you should delete one of the redundant
relationships. The extra table icon is just a red flag that this has
happened.

 

[Jeff Conrad]

Yep, I know the pitfalls of using them, that's why I just
wanted to be sure this alias table had not been "spawned"
accidently!

Thanks,
Jeff Conrad
Access Junkie
Bend, Oregon

 

[Jeff Conrad]

Hi John,

The multiple aliases are the way that Access displays
multiple relationships. Sometimes, as in this case, you
quite legitimately have multiple relationships between
two tables, and it's fine.
Gotcha.

If you've used a Lookup field...

Please slap me silly if I ever do.

...Access will have created a new relationship - *even if
a relationship already exists*. Since these would be two
identical relationships, between the same pair of fields,
that would NOT be OK; you should delete one of the
redundant relationships. The extra table icon is just a
red flag that this has happened.

I see.
Thanks for the extra explanation.

Jeff Conrad
Access Junkie
Bend, Oregon