|
 |
access thread: Relationships
Message #1 by "George Oro" <george@c...> on Wed, 21 Aug 2002 17:02:00 +0400
|
|
Hi Guys,
Just a thought...
Assume this is a view from the Relationships, as you can see tblCity is linked twice to the tblClient, is this a problem?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country City
Contact
ShippingAddress tblCity_1
ShippingCityID--------------->CityID
Country City
Contact
Cheers,
George
Message #2 by "Carnley, Dave" <dcarnley@a...> on Wed, 21 Aug 2002 09:24:29 -0500
|
|
From a theoretical, relational database perspective, it is just fine to have
multiple relationships between tables :)
but why 2 different city tables? Why not a single city table?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country | City
Contact ^
ShippingAddress |
ShippingCityID-----
Country
Contact
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 8:02 AM
To: Access
Subject: [access] Relationships
Hi Guys,
Just a thought...
Assume this is a view from the Relationships, as you can see tblCity is
linked twice to the tblClient, is this a problem?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country City
Contact
ShippingAddress tblCity_1
ShippingCityID--------------->CityID
Country City
Contact
Cheers,
George
Message #3 by "George Oro" <george@c...> on Wed, 21 Aug 2002 18:34:56 +0400
|
|
Hi Dave,
Thanks for the reply. Actually I'm trying to do that but there's a message saying "A Relationship is already exist. Do want to edit
the existing relationship bla bla bla?
Cheers,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 21, 2002 6:24 PM
To: Access
Subject: [access] Relationships
>From a theoretical, relational database perspective, it is just fine to have
multiple relationships between tables :)
but why 2 different city tables? Why not a single city table?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country | City
Contact ^
ShippingAddress |
ShippingCityID-----
Country
Contact
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 8:02 AM
To: Access
Subject: [access] Relationships
Hi Guys,
Just a thought...
Assume this is a view from the Relationships, as you can see tblCity is
linked twice to the tblClient, is this a problem?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country City
Contact
ShippingAddress tblCity_1
ShippingCityID--------------->CityID
Country City
Contact
Cheers,
George
Message #4 by "Gerald, Rand" <RGerald@u...> on Wed, 21 Aug 2002 09:38:42 -0500
|
|
Dave,
You have it wrong. There is only one tblCity.
TblCity and tblCity_1 are the same table. The relationship modeler
just
aliases them differently.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 21, 2002 9:24 AM
To: Access
Subject: [access] Relationships
From a theoretical, relational database perspective, it is just fine to
have
multiple relationships between tables :)
but why 2 different city tables? Why not a single city table?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country | City
Contact ^
ShippingAddress |
ShippingCityID-----
Country
Contact
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 8:02 AM
To: Access
Subject: [access] Relationships
Hi Guys,
Just a thought...
Assume this is a view from the Relationships, as you can see tblCity is
linked twice to the tblClient, is this a problem?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country City
Contact
ShippingAddress tblCity_1
ShippingCityID--------------->CityID
Country City
Contact
Cheers,
George
Message #5 by "George Oro" <george@c...> on Wed, 21 Aug 2002 18:41:59 +0400
|
|
True, any suggestion?
Cheers,
George
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Wednesday, August 21, 2002 6:39 PM
To: Access
Subject: [access] RE: Relationships
Dave,
You have it wrong. There is only one tblCity.
TblCity and tblCity_1 are the same table. The relationship modeler just
aliases them differently.
Rand E Gerald
Database Specialist
Information Services / Operations
Bahá'í National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 21, 2002 9:24 AM
To: Access
Subject: [access] Relationships
>From a theoretical, relational database perspective, it is just fine to have
multiple relationships between tables :)
but why 2 different city tables? Why not a single city table?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country | City
Contact ^
ShippingAddress |
ShippingCityID-----
Country
Contact
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 8:02 AM
To: Access
Subject: [access] Relationships
Hi Guys,
Just a thought...
Assume this is a view from the Relationships, as you can see tblCity is
linked twice to the tblClient, is this a problem?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country City
Contact
ShippingAddress tblCity_1
ShippingCityID--------------->CityID
Country City
Contact
Cheers,
George
Message #6 by jose.johnson@j... on Thu, 22 Aug 2002 09:28:45 +0900
|
|
Grorge
I may be bit late. This is due the time difference where I am now
I did not find any problem using the method which you presented and i a
m
using this in my applications
since in my company we have different types of contractor list which we
categorized based on their nature of contracts.
In our one record, we have multiple type of contractor code entered and
while generating reports, to display
the contractor name, i use the same method and i never encountered wit
h
any problems.
I think, if it is not a right method, Microsoft would not have allowed
us
to use the same table with an another alias name.
I don't know, if you were programming in FoxPro2.6, there is a use comm
and
called "USE TableName IN SELE(5) AGAIN ALIAS Table5"
(the syntax may not be correct as i forgot, long time passed, i am not
at
all using FoxPro, so the trend to ignore all that commands)
This allows us the use the same table in multiple work areas with diffe
rent
index. So we could seek for different options in the
same table without any problem. I assume that the same method is in th
e
query by just changing the ALIAS.
Best Regards
george@c...
om To: access@p...
x.com
cc:
08/21/2002 11:41 Subject: [access] RE: R
elationships
PM
Please respond to
access
True, any suggestion?
Cheers,
George
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Wednesday, August 21, 2002 6:39 PM
To: Access
Subject: [access] RE: Relationships
Dave,
You have it wrong. There is only one tblCity.
TblCity and tblCity_1 are the same table. The relationship modeler jus
t
aliases them differently.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 21, 2002 9:24 AM
To: Access
Subject: [access] Relationships
>From a theoretical, relational database perspective, it is just fine t
o
have
multiple relationships between tables :)
but why 2 different city tables? Why not a single city table?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country | City
Contact ^
ShippingAddress |
ShippingCityID-----
Country
Contact
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 8:02 AM
To: Access
Subject: [access] Relationships
Hi Guys,
Just a thought...
Assume this is a view from the Relationships, as you can see tblCity is
linked twice to the tblClient, is this a problem?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country City
Contact
ShippingAddress tblCity_1
ShippingCityID--------------->CityID
Country City
Contact
Cheers,
George
.
Message #7 by "enZo :-\)" <enzaux@g...> on Thu, 22 Aug 2002 09:58:03 +0800
|
|
Do you think is it ok if I don't use Relationships? Because I don't use any relationships
on tables because when I try to put relationships on the tables, I could not have a direct
access on some other parts of the table.
I do have encountered some situations wherein the database got corrupted and there were
loss of data. What I did is I paste the record from my backups to the repaired database
so that it will still have the complete records. This case was possible because it does
not have any table relationships. If there were table relationships this would not be
possible.
Do you think I'm doing the right thing here??? Please advice.
Thanks,
Enzo
Message #8 by "enZo :-\)" <enzaux@g...> on Thu, 22 Aug 2002 11:04:49 +0800
|
|
Actually the vb code is actually making sure that no DATA is left when deleted.
Is it alright to do the "referential integrity thing" through vb instead of
having them on the relationship table? Is this a good practice or not?
Please site some disadvantages and advantages
Thanks a lot,
Enzo
-----Original Message-----
From: simon larsen [mailto:Simon.larsen@h...]
Sent: Thursday, August 22, 2002 10:56 AM
To: ms_access@y...
Subject: RE: [ms_access] RE: Relationships
Nope it won't prevent corruption in terms of the file, it'll try hard to
stop a lot of bad data entering the db or being left behind after
deletes though.
If you don't use RI you have to do it in code.
Simon Larsen
DBA
<e.law> australia pty ltd
Mobile 0401 588 016
Phone (02) 8270 6237
Email address at the Commission Simon.larsen@h...
Email address at elaw s.larsen@e...
> From: enZo :-) [mailto:enzaux@g...]
> Simon,
>
> Are you sure that database relationship will prevent
> from my db from being corrupted?
> It's the only reason why I dont use relationship because I
> know I can't paste any record if
> I try to replace the lost one :(
>
> Thanks,
>
> Enzo
------------------------ Yahoo! Groups Sponsor ---------------------~-->
4 DVDs Free +s&p Join Now
http://us.click.yahoo.com/pt6YBB/NXiEAA/mG3HAA/q7folB/TM
---------------------------------------------------------------------~->
http://groups.yahoo.com/group/visual-basic-beginners/
http://groups.yahoo.com/group/ms_excel
http://groups.yahoo.com/group/screenprinting
http://groups.yahoo.com/group/powerpoint
http://groups.yahoo.com/group/pkunzip
http://groups.yahoo.com/group/photoshop-beginners
http://groups.yahoo.com/group/ms_access
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Message #9 by "George Oro" <george@c...> on Thu, 22 Aug 2002 08:32:53 +0400
|
|
Thanks for the tips Jose!!
Cheers,
George
-----Original Message-----
From: jose.johnson@j... [mailto:jose.johnson@j...]
Sent: Thursday, August 22, 2002 4:29 AM
To: Access
Subject: [access] RE: Relationships
Grorge
I may be bit late. This is due the time difference where I am now
I did not find any problem using the method which you presented and i am
using this in my applications
since in my company we have different types of contractor list which we
categorized based on their nature of contracts.
In our one record, we have multiple type of contractor code entered and
while generating reports, to display
the contractor name, i use the same method and i never encountered with
any problems.
I think, if it is not a right method, Microsoft would not have allowed us
to use the same table with an another alias name.
I don't know, if you were programming in FoxPro2.6, there is a use command
called "USE TableName IN SELE(5) AGAIN ALIAS Table5"
(the syntax may not be correct as i forgot, long time passed, i am not at
all using FoxPro, so the trend to ignore all that commands)
This allows us the use the same table in multiple work areas with different
index. So we could seek for different options in the
same table without any problem. I assume that the same method is in the
query by just changing the ALIAS.
Best Regards
george@c...
om To: access@p...
cc:
08/21/2002 11:41 Subject: [access] RE: Relationships
PM
Please respond to
access
True, any suggestion?
Cheers,
George
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Wednesday, August 21, 2002 6:39 PM
To: Access
Subject: [access] RE: Relationships
Dave,
You have it wrong. There is only one tblCity.
TblCity and tblCity_1 are the same table. The relationship modeler just
aliases them differently.
Rand E Gerald
Database Specialist
Information Services / Operations
Bahá'í National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 21, 2002 9:24 AM
To: Access
Subject: [access] Relationships
>From a theoretical, relational database perspective, it is just fine to
have
multiple relationships between tables :)
but why 2 different city tables? Why not a single city table?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country | City
Contact ^
ShippingAddress |
ShippingCityID-----
Country
Contact
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 8:02 AM
To: Access
Subject: [access] Relationships
Hi Guys,
Just a thought...
Assume this is a view from the Relationships, as you can see tblCity is
linked twice to the tblClient, is this a problem?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country City
Contact
ShippingAddress tblCity_1
ShippingCityID--------------->CityID
Country City
Contact
Cheers,
George
Message #10 by "George Oro" <george@c...> on Thu, 22 Aug 2002 08:32:59 +0400
|
|
For me relationship is a MUST.
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Thursday, August 22, 2002 5:58 AM
To: Access
Subject: [access] RE: Relationships
Do you think is it ok if I don't use Relationships? Because I don't use any relationships
on tables because when I try to put relationships on the tables, I could not have a direct
access on some other parts of the table.
I do have encountered some situations wherein the database got corrupted and there were
loss of data. What I did is I paste the record from my backups to the repaired database
so that it will still have the complete records. This case was possible because it does
not have any table relationships. If there were table relationships this would not be
possible.
Do you think I'm doing the right thing here??? Please advice.
Thanks,
Enzo
Message #11 by "Carnley, Dave" <dcarnley@a...> on Thu, 22 Aug 2002 09:14:53 -0500
|
|
I never use the Relationships features in Access, but all my databases are
SQL Server backend. I almost never use RI at that level either though. I
use stored procedures to do all inserts updates and deletes so all
referential integrity enforcement is coded at that level.
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 11:33 PM
To: Access
Subject: [access] RE: Relationships
For me relationship is a MUST.
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Thursday, August 22, 2002 5:58 AM
To: Access
Subject: [access] RE: Relationships
Do you think is it ok if I don't use Relationships? Because I don't use any
relationships
on tables because when I try to put relationships on the tables, I could not
have a direct
access on some other parts of the table.
I do have encountered some situations wherein the database got corrupted and
there were
loss of data. What I did is I paste the record from my backups to the
repaired database
so that it will still have the complete records. This case was possible
because it does
not have any table relationships. If there were table relationships this
would not be
possible.
Do you think I'm doing the right thing here??? Please advice.
Thanks,
Enzo
Message #12 by "Tom Milbourne" <tom@b...> on Thu, 22 Aug 2002 17:25:14 -0400
|
|
Years ago we opened tables more than once using open tblName
as alias tblNewName.
This could be done many times.
TomM
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 21, 2002 10:24 AM
To: Access
Subject: [access] Relationships
>From a theoretical, relational database perspective, it is
just fine to have
multiple relationships between tables :)
but why 2 different city tables? Why not a single city
table?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country | City
Contact ^
ShippingAddress |
ShippingCityID-----
Country
Contact
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 8:02 AM
To: Access
Subject: [access] Relationships
Hi Guys,
Just a thought...
Assume this is a view from the Relationships, as you can see
tblCity is
linked twice to the tblClient, is this a problem?
tblClient
ClientID
Company
BillingAddress tblCity
BillingCityID---------------> CityID
Country City
Contact
ShippingAddress tblCity_1
ShippingCityID--------------->CityID
Country City
Contact
Cheers,
George
---
Change your mail options at http://p2p.wrox.com/manager.asp
or
---
Change your mail options at http://p2p.wrox.com/manager.asp
or
Message #13 by "Steven White" <Steve.White@m...> on Fri, 23 Aug 2002 01:55:15
|
|
Most of the time, I find relationships tend to get in the way. The only time I use them is if I'm
using replication (it's a must for this), or if I'm going to do a lot of cascading updates.
I think they're definitely a good idea, and they should be used always unless you have your
database sorted out in such a way that data is only entered by forms, and the forms create
some mock referential integrity of their own
Message #14 by "enZo :-\)" <enzaux@g...> on Fri, 23 Aug 2002 11:05:18 +0800
|
|
Thanks Dave for sharing your method.
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Thursday, August 22, 2002 10:15 PM
To: Access
Subject: [access] RE: Relationships
I never use the Relationships features in Access, but all my databases are
SQL Server backend. I almost never use RI at that level either though. I
use stored procedures to do all inserts updates and deletes so all
referential integrity enforcement is coded at that level.
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, August 21, 2002 11:33 PM
To: Access
Subject: [access] RE: Relationships
For me relationship is a MUST.
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Thursday, August 22, 2002 5:58 AM
To: Access
Subject: [access] RE: Relationships
Do you think is it ok if I don't use Relationships? Because I don't use any
relationships
on tables because when I try to put relationships on the tables, I could not
have a direct
access on some other parts of the table.
I do have encountered some situations wherein the database got corrupted and
there were
loss of data. What I did is I paste the record from my backups to the
repaired database
so that it will still have the complete records. This case was possible
because it does
not have any table relationships. If there were table relationships this
would not be
possible.
Do you think I'm doing the right thing here??? Please advice.
Thanks,
Enzo
|
|
|
View Cart
