If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Assistance with relationships
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 |
#2
|
|||
|
|||
Assistance with relationships
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" wrote: 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 |
#3
|
|||
|
|||
Assistance with relationships
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 "Alec M1BNK" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
Assistance with relationships
=?Utf-8?B?QmF1ZXI=?= wrote in
: 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 |
#5
|
|||
|
|||
Assistance with relationships
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? -- Rob M. Thanks for your help and If I helped I''m glad to be of assistance. "Tim Ferguson" wrote: =?Utf-8?B?QmF1ZXI=?= wrote in : 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 |
#6
|
|||
|
|||
Assistance with relationships
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 "Rob M." wrote: 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? -- Rob M. Thanks for your help and If I helped I''m glad to be of assistance. "Tim Ferguson" wrote: =?Utf-8?B?QmF1ZXI=?= wrote in : 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 |
#7
|
|||
|
|||
Assistance with relationships
=?Utf-8?B?Um9iIE0u?= wrote in
: 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 |
#8
|
|||
|
|||
Assistance with relationships
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. "Rob M." wrote: 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? -- Rob M. Thanks for your help and If I helped I''m glad to be of assistance. "Tim Ferguson" wrote: =?Utf-8?B?QmF1ZXI=?= wrote in : 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 |
#9
|
|||
|
|||
Assistance with relationships
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. "Bauer" wrote in message ... 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 "Alec M1BNK" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Moving Relationships Between Databases | kh | Running & Setting Up Queries | 4 | February 23rd, 2006 05:46 PM |
Using Relationships window | [email protected] | Database Design | 11 | October 2nd, 2005 06:44 PM |
Confused about one-to-many or many-to-many relationships | CAD Fiend | Database Design | 4 | July 7th, 2005 03:38 PM |
Mixed up with Relationships..help! | KrazyRed | New Users | 3 | January 26th, 2005 05:03 AM |
More assistance with Many to Many Relationships | Lynn | Database Design | 3 | July 9th, 2004 04:22 PM |