Multiple Many-to-Many relationships

Hello,

I am working on an archival database of media materials related to the
accomplishments of a non-profit group. One section of the database is devoted
to articles. I have a series of tables separated into types of articles
(Newspaper, Magazine, Blog, etc.) For this question, I will be focusing on
the set of Magazine tables.

Table MagazineArticle contains:
MagazineAID (article ID, PK)
ArticleTitle
Date/Issue
Summary/Subj
StartPage
EndPage
MagazineID (for name of Magazine, FK)
Field1 (attachment field)

Related by a one to many relationship is the table Magazine which contains
MagazineID(PK)
MagazineName

Both an Author table (Author, Author.ID) and a Projects table (Project Name,
Projects.ID) are connected to the MagazineArticle table using the Junction
tables AuthorArticle and ProjectMagazine, respectively.

One magazine article can have many authors or can reference many projects.
Just as much as one Project can be referenced in many articles, or one author
can write multiple articles in multiple magazines, resulting in the
many-to-many relationships.

The problem I'm having is that because I have the two many-to-many
relationships, when I create a query showing all related article fields from
these tables, I am not allowed to create a query where I can edit the data in
the records. If I leave out projects, fore example, I can get article records
related to the multiple authors, and edit those records. Conversely if I
leave out Authors, I can get edit the resulting records grouped by project. I
would like, however, to create a synthesized query to show as much
information about the article as possible.

Is the problem in how I set up my relationships? Is there a way to get
access to recognize the multiple many-to-many relationships and create the
ideal query? Do i need to create queries with sub-queries? What is the best
strategy for me to get around this issue?

Thanks,

M

I do not think you need the many-many but tables like these --
Project—
ProjID – PK
Title
Manager
Start - DateTime
…etc

Article --
ArticleID - PK
Author - FK
Periodical FK
ArticleTitle
Date/Issue
Summary/Subj
StartPage
EndPage
Field1 (attachment field)

Periodical
PeriodicalID – PK
Title
Type – (Newspaper, Magazine, Blog, etc.)
PubPeriod – Weekly, Annual, As needed, etc.
… etc

Author –
AuthID – PK
FName
LName
…etc

Use form/subform to display the information.

--
Build a little, test a little.


"meaghantron" wrote:

Hello,

I am working on an archival database of media materials related to the
accomplishments of a non-profit group. One section of the database is devoted
to articles. I have a series of tables separated into types of articles
(Newspaper, Magazine, Blog, etc.) For this question, I will be focusing on
the set of Magazine tables.

Table MagazineArticle contains:
MagazineAID (article ID, PK)
ArticleTitle
Date/Issue
Summary/Subj
StartPage
EndPage
MagazineID (for name of Magazine, FK)
Field1 (attachment field)

Related by a one to many relationship is the table Magazine which contains
MagazineID(PK)
MagazineName

Both an Author table (Author, Author.ID) and a Projects table (Project Name,
Projects.ID) are connected to the MagazineArticle table using the Junction
tables AuthorArticle and ProjectMagazine, respectively.

One magazine article can have many authors or can reference many projects.
Just as much as one Project can be referenced in many articles, or one author
can write multiple articles in multiple magazines, resulting in the
many-to-many relationships.

The problem I'm having is that because I have the two many-to-many
relationships, when I create a query showing all related article fields from
these tables, I am not allowed to create a query where I can edit the data in
the records. If I leave out projects, fore example, I can get article records
related to the multiple authors, and edit those records. Conversely if I
leave out Authors, I can get edit the resulting records grouped by project. I
would like, however, to create a synthesized query to show as much
information about the article as possible.

Is the problem in how I set up my relationships? Is there a way to get
access to recognize the multiple many-to-many relationships and create the
ideal query? Do i need to create queries with sub-queries? What is the best
strategy for me to get around this issue?

Thanks,

M

How in this organization do I account for the fact that each article
needs to be able to accommodate multiple authors, each author can have
multiple articles, each article can reference more than one project,
and each project is related to multiple articles? From this
perspective, I am not sure why I would remove the many-to-many
configuration. In fact, your suggestion neglects any relationship
between article and project, which is extremely important for how I
have set up search criteria in my queries.

On Oct 26, 4:20*pm, KARL DEWEY
wrote:
I do not think you need the many-many but tables like these --
Project—
ProjID – PK
Title
Manager
Start - DateTime
* * …etc

Article -- * * *
ArticleID - PK
Author - FK
Periodical FK
ArticleTitle
Date/Issue
Summary/Subj
StartPage
EndPage
Field1 (attachment field)

Periodical
PeriodicalID – PK
Title
Type – (Newspaper, Magazine, Blog, etc.)
PubPeriod – Weekly, Annual, As needed, etc.
* * … etc

Author –
AuthID – PK
FName
LName
*…etc

Use form/subform to display the information.

--
Build a little, test a little.

"meaghantron" wrote:
Hello,

I am working on an archival database of media materials related to the
accomplishments of a non-profit group. One section of the database is devoted
to articles. I have a series of tables separated into types of articles
(Newspaper, Magazine, Blog, etc.) For this question, I will be focusing on
the set of Magazine tables.

Table MagazineArticle contains:
MagazineAID (article ID, PK)
ArticleTitle
Date/Issue
Summary/Subj
StartPage
EndPage
MagazineID (for name of Magazine, FK)
Field1 (attachment field)

Related by a one to many relationship is the table Magazine which contains
MagazineID(PK)
MagazineName

Both an Author table (Author, Author.ID) and a Projects table (Project Name,
Projects.ID) are connected to the MagazineArticle table using the Junction
tables AuthorArticle and ProjectMagazine, respectively.

One magazine article can have many authors or can reference many projects.
Just as much as one Project can be referenced in many articles, or one author
can write multiple articles in multiple magazines, resulting in the
many-to-many relationships.

The problem I'm having is that because I have the two many-to-many
relationships, when I create a query showing all related article fields from
these tables, I am not allowed to create a query where I can edit the data in
the records. If I leave out projects, fore example, I can get article records
related to the multiple authors, and edit those records. Conversely if I
leave out Authors, I can get edit the resulting records grouped by project. I
would like, however, to create a synthesized query to show as much
information about the article as possible.

Is the problem in how I set up my relationships? Is there a way to get
access to recognize the multiple many-to-many relationships and create the
ideal query? Do i need to create queries with sub-queries? What is the best
strategy for me to get around this issue?

Thanks,

M

You need to update through forms, not a query. Create a main form which will
display a single record from the Article table. Put into it a subform which
will display a list of authors of that article. Then another subform which
will display a list of projects for that article.
For the subforms, I like datasheet or continuous form view, with a
double-click event on every field that opens up a single form showing
details for one author or one project.
This structure will allow you to update any of your tables.
You might then want to repeat the structure with the main form showing
projects, and subforms showing articles and authors, and/or main form of
authors with subforms of articles and projects.

-TedMi

"Meaghan S" wrote in message
...
How in this organization do I account for the fact that each article
needs to be able to accommodate multiple authors, each author can have
multiple articles, each article can reference more than one project,
and each project is related to multiple articles? From this
perspective, I am not sure why I would remove the many-to-many
configuration. In fact, your suggestion neglects any relationship
between article and project, which is extremely important for how I
have set up search criteria in my queries.

On Oct 26, 4:20 pm, KARL DEWEY
wrote:
I do not think you need the many-many but tables like these --
Project—
ProjID – PK
Title
Manager
Start - DateTime
…etc

Article --
ArticleID - PK
Author - FK
Periodical FK
ArticleTitle
Date/Issue
Summary/Subj
StartPage
EndPage
Field1 (attachment field)

Periodical
PeriodicalID – PK
Title
Type – (Newspaper, Magazine, Blog, etc.)
PubPeriod – Weekly, Annual, As needed, etc.
… etc

Author –
AuthID – PK
FName
LName
…etc

Use form/subform to display the information.

--
Build a little, test a little.

"meaghantron" wrote:
Hello,

I am working on an archival database of media materials related to the
accomplishments of a non-profit group. One section of the database is
devoted
to articles. I have a series of tables separated into types of articles
(Newspaper, Magazine, Blog, etc.) For this question, I will be focusing
on
the set of Magazine tables.

Table MagazineArticle contains:
MagazineAID (article ID, PK)
ArticleTitle
Date/Issue
Summary/Subj
StartPage
EndPage
MagazineID (for name of Magazine, FK)
Field1 (attachment field)

Related by a one to many relationship is the table Magazine which
contains
MagazineID(PK)
MagazineName

Both an Author table (Author, Author.ID) and a Projects table (Project
Name,
Projects.ID) are connected to the MagazineArticle table using the
Junction
tables AuthorArticle and ProjectMagazine, respectively.

One magazine article can have many authors or can reference many
projects.
Just as much as one Project can be referenced in many articles, or one
author
can write multiple articles in multiple magazines, resulting in the
many-to-many relationships.

The problem I'm having is that because I have the two many-to-many
relationships, when I create a query showing all related article fields
from
these tables, I am not allowed to create a query where I can edit the
data in
the records. If I leave out projects, fore example, I can get article
records
related to the multiple authors, and edit those records. Conversely if I
leave out Authors, I can get edit the resulting records grouped by
project. I
would like, however, to create a synthesized query to show as much
information about the article as possible.

Is the problem in how I set up my relationships? Is there a way to get
access to recognize the multiple many-to-many relationships and create
the
ideal query? Do i need to create queries with sub-queries? What is the
best
strategy for me to get around this issue?

Thanks,

M

I suppose I should clarify the purpose of this query. This is a query
that uses an unbound form to enter criteria and find specific records
relating to specific sets of information. When the query is run, it
should return a set of information about the articles (Author, Date,
Title, Etc...) narrowed down by the criteria entered by the user. In a
format that anyone using the database can scan the list of records for
the appropriate one, and read the attached file. If the record happens
to be missing information (an attached file for instance) I would like
one set of users (volunteers) to be able to enter that specific
information. I do have a form which shows single records with subforms
for data entry/editing purposes, but it is cumbersome at the moment to
find specific articles to edit. Ideally, that is the problem I would
like to solve with this particular parameter query. (which would then
be the underlying basis for an edit form- however, if i am unable to
add attachments on the query, I am also unable to add them to a form
based on such a query)

I'm sorry if this is difficult to follow, I am not a super-advanced
access designer. And I'm sure I've made it more complicated than I
need to. So, to clarify from what I already have, I will state what I
would like to happen: I want a database where the user can perform a
search for specific articles, (by date, by project, by author, by
source, by keyword), a list of matching records appears with its
identifying information, the user is then able to select the correct
record and view it/edit it.

I can make it work as long as I don't include both many-to-many
relationships in the query. Unfortunately, this means some of the
information returned to the user is missing (which project the article
is related to, or which group of authors authored it.)

Does this help clarify my situation at all?

Meaghan

What was your design requirement that led to using an unbound form?

Access offers considerable strengths and features for working with bound
forms...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Meaghan S" wrote in message
...
I suppose I should clarify the purpose of this query. This is a query
that uses an unbound form to enter criteria and find specific records
relating to specific sets of information. When the query is run, it
should return a set of information about the articles (Author, Date,
Title, Etc...) narrowed down by the criteria entered by the user. In a
format that anyone using the database can scan the list of records for
the appropriate one, and read the attached file. If the record happens
to be missing information (an attached file for instance) I would like
one set of users (volunteers) to be able to enter that specific
information. I do have a form which shows single records with subforms
for data entry/editing purposes, but it is cumbersome at the moment to
find specific articles to edit. Ideally, that is the problem I would
like to solve with this particular parameter query. (which would then
be the underlying basis for an edit form- however, if i am unable to
add attachments on the query, I am also unable to add them to a form
based on such a query)

I'm sorry if this is difficult to follow, I am not a super-advanced
access designer. And I'm sure I've made it more complicated than I
need to. So, to clarify from what I already have, I will state what I
would like to happen: I want a database where the user can perform a
search for specific articles, (by date, by project, by author, by
source, by keyword), a list of matching records appears with its
identifying information, the user is then able to select the correct
record and view it/edit it.

I can make it work as long as I don't include both many-to-many
relationships in the query. Unfortunately, this means some of the
information returned to the user is missing (which project the article
is related to, or which group of authors authored it.)

Does this help clarify my situation at all?

the unbound form is a vessel for the search criteria. temporary
variables. the information entered into the form is not saved in a
table, but creates the parameters for the query.

any suggestions to my actual questions?????

thanks,

m

I asked because I didn't understand enough to offer suggestions. Until I
have a sense of what problem is being solved, I have trouble assessing
whether the solution-as-designed fits it.

One way to manage the use of 'junction' tables (resolve many-to-many
relationships) is to use the main-form/subform construction.

If the data on the "one" side is shown in the main form, you can use a
subform to display the related "many" records.

So I thought your original task was to update, not search. Are you doing
both?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



"Meaghan S" wrote in message
...
the unbound form is a vessel for the search criteria. temporary
variables. the information entered into the form is not saved in a
table, but creates the parameters for the query.

any suggestions to my actual questions?????

thanks,

m

Some queries cannot be edited. More information he
http://allenbrowne.com/ser-61.html

Imagine an Invoice table, with 3 related Line Items (or invoice details). A
query can be devised to show the invoice with its three related records, but
what if you want to change information in the main invoice record? You
cannot change information in one query row, as it would need to apply to all
rows. While it is true that a change to detail (line item) information could
theoretically be changed in the query, other information cannot be. Access
does not distinguish within such a query which fields may be edited and which
may not, so the whole query is read-only.

Instead, use forms and subforms as suggested. Rather than using an unbound
form to set query criteria you could use unbound controls on a bound form to
filter or redefine the form's recordset.

In a many-to-many situation such as you describe, you can look at an article
record on the main form and see a list of authors on the subform (that is
boudn to the junction table). However, to edit Author information you will
need to go to a separate Author form. You may be able to use the Not In List
event of the Author combo box on the subform to add author information, but
you would still need an Author form to edit Author information.

By the way, the Author form could use a subform based on the junction table
to show articles by that author, in the same manner as an Article form can
show the author list.

Meaghan S wrote:
I suppose I should clarify the purpose of this query. This is a query
that uses an unbound form to enter criteria and find specific records
relating to specific sets of information. When the query is run, it
should return a set of information about the articles (Author, Date,
Title, Etc...) narrowed down by the criteria entered by the user. In a
format that anyone using the database can scan the list of records for
the appropriate one, and read the attached file. If the record happens
to be missing information (an attached file for instance) I would like
one set of users (volunteers) to be able to enter that specific
information. I do have a form which shows single records with subforms
for data entry/editing purposes, but it is cumbersome at the moment to
find specific articles to edit. Ideally, that is the problem I would
like to solve with this particular parameter query. (which would then
be the underlying basis for an edit form- however, if i am unable to
add attachments on the query, I am also unable to add them to a form
based on such a query)

I'm sorry if this is difficult to follow, I am not a super-advanced
access designer. And I'm sure I've made it more complicated than I
need to. So, to clarify from what I already have, I will state what I
would like to happen: I want a database where the user can perform a
search for specific articles, (by date, by project, by author, by
source, by keyword), a list of matching records appears with its
identifying information, the user is then able to select the correct
record and view it/edit it.

I can make it work as long as I don't include both many-to-many
relationships in the query. Unfortunately, this means some of the
information returned to the user is missing (which project the article
is related to, or which group of authors authored it.)

Does this help clarify my situation at all?

--
Message posted via http://www.accessmonster.com

Ok, this is the best response by far. There are still a couple of
things I would like to ask about/get at.

Here is what I want: A database that can be used by the user in two
distinct ways.

One: To enter data about articles/media
Two: To search for articles based on specific criteria entered in by
the user.

This is what I have:

Part One: Forms with subforms showing the article, the many projects
it is related to, and the many authors it is related to. I am able to
edit Author information by use of a separate form when 'not in list'.
This I have used for the data entry portion of the DB, and it works
perfectly. (it is in fact what a couple of you have already suggested
to me for part 2...)

Part Two is what I would like to come back to. "you could use unbound
controls on a bound form to
filter or redefine the form's recordset."
Although I can apply filters using the form when emI/em want to
find specific data, not everyone who is going to use this is so access
savvy. That is why I am trying to set up a search form, akin to search
forms you may find on an electronic journals database. the user,
knowing little about access, enters in search criteria, and BAM, a
list of related articles appear. So, perhaps due to the limitations of
Access, the list (in this case a query, could be a form or even a
report based on the query) is read only. Fine. But is there a way to
point to the detail of the specific record on my Data Entry Form,
directed from the list created by the query?

thanks, (I know my tone sounds frustrated, it's just at access, not
the help I am receiving. For all of your words and patience, I am
grateful)

M