Assistance with relationships

[Bauer]

I'm having difficulty with the relationships. They connect OK but when I
come to click on the plus sign in any of the tables I get the message:-

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplfying the expression by assigning parts of the expression to
variables.

I've made up three individual tables with the following fields:-
SomethingID - Autonumber
Something - Text

I've added all the those in with the Insert code table as child tables, so
the table looks like

InsertCodeID - Autonumber
InsertCode - Text
Type of InsertID - Text
Manufacturer - Text
Supplier - Text
Notes - Text

 

[Alec M1BNK]

The only time I can recall seeing that message is when i changed the datatype
of one half of the join and ended up with an integer field joined to an
autonumber one - is that pssible here?

Alec

 

[Bauer]

Yes it is possible

In my table the ID is autonumber and when you view the field size it is Long
integer
Then when I go into the table SomethingID - Text the field size give a
numeric of 50

 

[Tim Ferguson]

I've made up three individual tables with the following fields:-
SomethingID - Autonumber
Something - Text

This does not really make much sense: you need some kind of meaning in
order to define a semantic relationship.

For examples:

Somethings(
SomeID autonumber primary key,
SomeDescription text(),
etc)

Dingbats (
DingNumber autonumber primary key,
RelatedSomething long integer foreign key references Somethings,
DingDescription,
etc)

Widgets (
WidgieCode autonumber primary key,
OwnedByDing long integer foreign key references Dingbats,
WidgetColour,
etc)


This means that every Widget is owned by a Dingbat (and each Dingbat may
own zero, one or more Widgets); each Dingbat is related to a Something
(and each Something may have relatives in zero, one or more Dingbats).

Note that foreign keys that point at autonumbers are long integers,
because FKs must match their targets' data type exactly, in type and
size. An autoumber is just a long integer with some fancy allocation
rules.

If you want to know which Widgets are owned by relatives of a particular
Something, then you'll need to join all three tables; similar if you want
to know the Something related to the owner of a particular Widget. This
could be avoided with a small adjustment to the design if it were
important.

Hope that helps


Tim F

 

[Rob M.]

I believe when I read the book Access 2002 Inside Out, the author mentioned
a note where the Autonumber field (Data Type) in a relationship may require
you to use Replication ID Field in the field Properties. I'm not sure if
this will solve your problem. I'm still desighning my database and I may
incur this same problem? This just came from the book, and it may be related
to your relationship problem, but not necessarily?

 

[Bauer]

Hi Rob

Yes it has helped. I'd never thought to click on the Replication ID Field.

Many thanks to Alec as well you have both helped me with this query.

Regards

Bauer

 

[Tim Ferguson]

I believe when I read the book Access 2002 Inside Out, the author
mentioned a note where the Autonumber field (Data Type) in a
relationship may require you to use Replication ID Field in the field
Properties.

I don't think so...


Tim F

 

[mnature]

To quote from Access 2003 Inside Out (page 109):

In general, the Replication ID field size should be used only in a database
that is managed by the Replication Manager.

 

[Pat Hartman\(MVP\)]

Change the data type from text to long integer. Autonumbers are long
integer data types. Therefore, foreign keys MUST be defined as long integer
in order for the join to work.