Namespaces

[Entropique]

Trying to create two tables or two questions with the same name results in an error
Trying to create two fields with the same name in a single table is not possible

I have a few tables that relate to each other in such a way that they form a circle

In one of these I have a primary key, It would be called CountryID if I used english for naming the parts of my DB
Two other tables have fields that store this key as a foreign key. They are also called CountryID

The problem is these two tables should be able to connect to different Countries, but they don't
When I'm testing my question I only get the records where the countries choosen from either side are the same
This is only a small subset of the records I want this question to select. In fact I need it to select all the records

I have done circular designs before without problems, though never this small, only five tables
That's why I am curiuos about the possibility of using the same name for fields that are used to store differnet values

Do fieldnames have to be unique in a database, or perhaps just the combination of name and type

Grateful for any comments on this.

 

[Rebecca Riordan]

Field names have to be unique in a table, not within the db. That's not the
problem you're having.

What do you mean when you say "able to connect to different countries"? Are
you trying to link a single field to two different tables? That's not
possible.

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

Trying to create two tables or two questions with the same name results in an error.
Trying to create two fields with the same name in a single table is not possible.

I have a few tables that relate to each other in such a way that they form a circle.

In one of these I have a primary key, It would be called CountryID if I

used english for naming the parts of my DB.

Two other tables have fields that store this key as a foreign key. They are also called CountryID.

The problem is these two tables should be able to connect to different Countries, but they don't.
When I'm testing my question I only get the records where the countries

choosen from either side are the same.

This is only a small subset of the records I want this question to select.

In fact I need it to select all the records.

I have done circular designs before without problems, though never this small, only five tables.
That's why I am curiuos about the possibility of using the same name for

fields that are used to store differnet values.

 

[Ted Allen]

Hi,

If I understand your post correctly, you have one table
serving as a lookup table for countries, and multiple
tables using that table as a reference.

If that is the case, my guess as to what may be causing
your problem would be that in the relationships window,
or in the query relationship, you have linked the lookup
table to both other tables. In that case you are telling
access that all 3 values need to be equal to satisfy the
join.

If these assumptions are correct, try adding the
countries lookup table to the relationships window
twice. Access will assign an alias to the second
instance (something like adding _1 to the end of the
table name). Then, link each of these to one of the
tables using it as a lookup source. With this type of
relationship, you are telling access to find the
appropriate match from the Country lookup table, but that
the values in the two non-lookup tables do not have to be
equal to one another.

Hopefully this helps.

-Ted Allen

-----Original Message-----
Trying to create two tables or two questions with the same name results in an error.
Trying to create two fields with the same name in a single table is not possible.

I have a few tables that relate to each other in such a way that they form a circle.

In one of these I have a primary key, It would be called

CountryID if I used english for naming the parts of my DB.

Two other tables have fields that store this key as a

foreign key. They are also called CountryID.

The problem is these two tables should be able to

connect to different Countries, but they don't.

When I'm testing my question I only get the records

where the countries choosen from either side are the same.

This is only a small subset of the records I want this

question to select. In fact I need it to select all the
records.

I have done circular designs before without problems,

though never this small, only five tables.

That's why I am curiuos about the possibility of using

the same name for fields that are used to store differnet
values.

Do fieldnames have to be unique in a database, or

perhaps just the combination of name and type?

 

[Entropique]

Thanks

This did the trick

I should have figured it out myself though. In my large database at home a have a many-to-many relationship between a table and itself. To create this I obvoiusly had to duplicate the table in question in the relationship window

Not quite the same problem of course, but similar enough to suggest trying the same solution

- Kent Larso

----- Ted Allen skrev: ----

Hi

If I understand your post correctly, you have one table
serving as a lookup table for countries, and multiple
tables using that table as a reference

If that is the case, my guess as to what may be causing
your problem would be that in the relationships window,
or in the query relationship, you have linked the lookup
table to both other tables. In that case you are telling
access that all 3 values need to be equal to satisfy the
join

If these assumptions are correct, try adding the
countries lookup table to the relationships window
twice. Access will assign an alias to the second
instance (something like adding _1 to the end of the
table name). Then, link each of these to one of the
tables using it as a lookup source. With this type of
relationship, you are telling access to find the
appropriate match from the Country lookup table, but that
the values in the two non-lookup tables do not have to be
equal to one another

Hopefully this helps

-Ted Alle

-----Original Message----
Trying to create two tables or two questions with the same name results in an error
Trying to create two fields with the same name in a single table is not possible

CountryID if I used english for naming the parts of my DB

Two other tables have fields that store this key as a

foreign key. They are also called CountryIDconnect to different Countries, but they don't

When I'm testing my question I only get the records

where the countries choosen from either side are the same

This is only a small subset of the records I want this

question to select. In fact I need it to select all the
recordsthough never this small, only five tables

That's why I am curiuos about the possibility of using

the same name for fields that are used to store differnet
values