Join question

[nooB]

hi,
2 quick questions,

In some queries i get the Error message 'Access cannot
represent the join between ModID and MSModID' - most times
I use this join it works fine, any ideas why?

secondly,
does it make any difference if the foreign key in a linked
table has the same name as the primary key in the main
table?

thanks,

nooB

 

[Steve Schapel]

nooB,

In some queries i get the Error message 'Access cannot
represent the join between ModID and MSModID' - most times
I use this join it works fine, any ideas why?

In my experience, the main reason for this error is if you change the
name of a field in the table, and the query is still trying to use the
old name.

does it make any difference if the foreign key in a linked
table has the same name as the primary key in the main
table?

One time it does make a difference, is if you are making a calculated
field in a query which includes both tables, and if the fields have the
same names, you have to specify the table name in the calculation
expression, for example you can't just have...
Voucher: "X" & Format([ModID],"0000")
.... you have to use...
Voucher: "X" & Format([OneOfTheTables].[ModID],"0000")

 

[nooB]

The field names haven't changed, or if they have
historically they are still valid, If I close the query
and DON'T save I can reopen the query and it works fine,
it's only trying to view Design View that spanners it!

I take it from your msg that it's actually advantageous to
have different Primary and Foreign keys if I'm reading you
right.

many thanks,

nooB

-----Original Message-----
nooB,

In some queries i get the Error message 'Access cannot
represent the join between ModID and MSModID' - most times
I use this join it works fine, any ideas why?

In my experience, the main reason for this error is if you change the
name of a field in the table, and the query is still trying to use the
old name.

does it make any difference if the foreign key in a linked
table has the same name as the primary key in the main
table?

One time it does make a difference, is if you are making a calculated
field in a query which includes both tables, and if the fields have the
same names, you have to specify the table name in the calculation
expression, for example you can't just have...
Voucher: "X" & Format([ModID],"0000")
.... you have to use...
Voucher: "X" & Format([OneOfTheTables].[ModID],"0000")

--
Steve Schapel, Microsoft Access MVP


.

 

[Steve Schapel]

nooB

Well, there have been times when I have had weird things happen like
this, and in the end I have just had to rebuild the query, delete the
old one, and compact the .mdb. Why? I don't know.

No, you are not reading me right. Some people use a naming convention
which involves identifying fields by the table, and primary key fields
therefore named differently from the corresponding foreign key fields.
But I would imagine the majority of people, including me, would nearly
always have them named the same. I was answering your question "does it
make any difference?", not "is it better?"