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
|
|||
|
|||
How do I create two one to many relationships
I have multiple projects that can be located in multiple places
(muncipalities). I have a table of projects and a table of municipalities. I would like to be able to search and create reports by either municipality or project. How do I link these to allow one project to be in multiple municipalities (but still searchable by municipality) and one municipality to contain multiple projects (but still searchable by project). I have Access 2000. The method I attemptd was to create an "Interface" table to which the projects and municipalities tables both had a one to many relationship for a respective field. This table gave a unique ID to each project municipality combination. Whenever I try to create a query for to link the information from the municipalities and projects table with the interface table, however, I get a message that says, "Type mismatch in expression." I am unable to view or to create reports using this query. |
#2
|
|||
|
|||
"Type mismatch" implies that the fields you are trying to join on are not
defined as the same data type. Recheck your table definitions. -- Good luck Jeff Boyce Access MVP "justindula" wrote in message ... I have multiple projects that can be located in multiple places (muncipalities). I have a table of projects and a table of municipalities. I would like to be able to search and create reports by either municipality or project. How do I link these to allow one project to be in multiple municipalities (but still searchable by municipality) and one municipality to contain multiple projects (but still searchable by project). I have Access 2000. The method I attemptd was to create an "Interface" table to which the projects and municipalities tables both had a one to many relationship for a respective field. This table gave a unique ID to each project municipality combination. Whenever I try to create a query for to link the information from the municipalities and projects table with the interface table, however, I get a message that says, "Type mismatch in expression." I am unable to view or to create reports using this query. |
#3
|
|||
|
|||
The municipalities' primary key are numbers, the projects' primary keys are
text, and the interface table has an autonumber as the primary key. These are all linked to the proper data type... if I understand your suggestion properly. "Jeff Boyce" wrote: "Type mismatch" implies that the fields you are trying to join on are not defined as the same data type. Recheck your table definitions. -- Good luck Jeff Boyce Access MVP "justindula" wrote in message ... I have multiple projects that can be located in multiple places (muncipalities). I have a table of projects and a table of municipalities. I would like to be able to search and create reports by either municipality or project. How do I link these to allow one project to be in multiple municipalities (but still searchable by municipality) and one municipality to contain multiple projects (but still searchable by project). I have Access 2000. The method I attemptd was to create an "Interface" table to which the projects and municipalities tables both had a one to many relationship for a respective field. This table gave a unique ID to each project municipality combination. Whenever I try to create a query for to link the information from the municipalities and projects table with the interface table, however, I get a message that says, "Type mismatch in expression." I am unable to view or to create reports using this query. |
#4
|
|||
|
|||
On Wed, 1 Dec 2004 09:29:02 -0800, "justindula"
wrote: The municipalities' primary key are numbers, the projects' primary keys are text, and the interface table has an autonumber as the primary key. These are all linked to the proper data type... if I understand your suggestion properly. The interface table should have two foreign key fields, one, number (long integer or whatever type of number is the municipality's PK) linked to the municipality's primary key, the other text of the same size as the primary key of the Projects table. The datatype of the primary key of the interface table is irrelevant; in fact you might want to consider removing the autonumber altogether, and using a two-field joint Primary Key consisting of the two foreign keys. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#5
|
|||
|
|||
I can't figure out how to connect them automatically, and that would be
helpful. I can find how to do that with a form, but I am unable to create a form because it gives me the error "Type mismatch in expression." Would this method of numbering the interface table solve this problem? I'm getting very frustrated. I'm not sure if my basic premise is correct. What I'm gathering is this is the proper way to link two tables with multiple fields. There is some specific error with my program or table that is making this not work. I can't believe this isn't easier. This seems to be exactly the sort of operation this program was designed to do. "John Vinson" wrote: On Wed, 1 Dec 2004 09:29:02 -0800, "justindula" wrote: The municipalities' primary key are numbers, the projects' primary keys are text, and the interface table has an autonumber as the primary key. These are all linked to the proper data type... if I understand your suggestion properly. The interface table should have two foreign key fields, one, number (long integer or whatever type of number is the municipality's PK) linked to the municipality's primary key, the other text of the same size as the primary key of the Projects table. The datatype of the primary key of the interface table is irrelevant; in fact you might want to consider removing the autonumber altogether, and using a two-field joint Primary Key consisting of the two foreign keys. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#6
|
|||
|
|||
On Wed, 1 Dec 2004 12:31:06 -0800, "justindula"
wrote: I can't figure out how to connect them automatically, and that would be helpful. I can find how to do that with a form, but I am unable to create a form because it gives me the error "Type mismatch in expression." Would this method of numbering the interface table solve this problem? I'm getting very frustrated. I'm not sure if my basic premise is correct. What I'm gathering is this is the proper way to link two tables with multiple fields. There is some specific error with my program or table that is making this not work. I can't believe this isn't easier. This seems to be exactly the sort of operation this program was designed to do. It is; let's try to figure out why it's not working for you. A few questions: - What are the names of your Tables? - What are the names, datatypes, and size of each table's Primary Key? - What fields do you have in the junction table? - How are you trying to create the Form? Based on one table, all three tables, a query, or what? - At what point do you get the error message? John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
CREATE PROC syntax in ADP | aaron kempf | General Discussion | 2 | December 5th, 2004 03:01 AM |
CREATE PROC SYNTAX | aaron kempf | General Discussion | 0 | November 23rd, 2004 07:50 PM |
Setting Table Relationships- Why? | el zorro | Database Design | 4 | November 8th, 2004 10:29 PM |
Not seeing all relationships in layout window | jettabug | General Discussion | 3 | June 18th, 2004 05:42 PM |
When I try to open/save .rtf attachment I get the messages ?Can't create file. Right-click the folder you want to create item in and then click on the shortcut menu to check your permissions for the folder.? | Nie Geweune via AdminLife | Installation & Setup | 0 | April 27th, 2004 11:16 AM |