Relations between 2 tables problem

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

 

[Douglas J. Steele]

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.

 

[Rob Hofkens]

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 ?

 

[Douglas J. Steele]

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.

 

[Rob Hofkens]

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