How do I create two one to many relationships

[justindula]

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.

 

[Jeff Boyce]

"Type mismatch" implies that the fields you are trying to join on are not
defined as the same data type. Recheck your table definitions.

 

[justindula]

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.

 

[John Vinson]

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

 

[justindula]

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]

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