Relations between 2 tables problem

  • Thread starter Thread starter Rob Hofkens
  • Start date Start date
R

Rob Hofkens

Hello everyone !

I am fairly new to database programming but I am excited :)
So I started out with 2 tables and wend to the relations editor.
And...I got stuck.

Here is what I did:

TABLE :
tblCities
FIELDS :
CityID (primairy key)
CityName

TABLE :
tblRelations
FIELDS :
RelationID (primairy key)
Name
Adress
Zipcode
CityID -> I want this to be linked to tblCities -> CityID
POBox
POZipcode
POCityID -> I want this to be linked to tblCities -> CityID

The big problem I have here is that 2 fields in table tblRelations should be
linked to table tblCities
Perhapse this is something weird to do I dunno...yet.
The relations editor in Access doesn't like my idea at all.
I also want to use a SQL Server to build my application upon, dunno if that
is important.

I hope someone sees what I attempt to do and knows how to proceed.

Thx in advance.

Rob Hofkens.
 
What you have to do in the Relationship editor is add tblCities twice.
Access will label the second one tblCities_1. Link one of your fields to
tblCities, and the other to tblCities_1.
 
Yes I noticed that behaviour.
But if you then go to make a query and drop the 2 tables in , the relations
is different then in relation manager.
When I went back to check the relation manager it suddendly showed a thirth
tabel called tblCities_2
So somehow it messed up ?

Besides that I tried it when I was connected to a SQL Server and it didn't
alow me to create an additional table in the relation manager.

Any thought about this ?
 
Are these tables in SQL Server or Access? AFAIK, you can't create
relationships in Access using external tables: you'd need to set it up in
SQL Server.
 
Yes I tried it first in an .mdb database and in that case you can create
dublicate tables with _1, _2 etc. extensions.
Because I want to setup the database on a SQL Server I created an .adp and
tried it again.
I discovered that you can't create the duplicate tables like in a .mdb
 
Back
Top