Re: Multiple Many-to-Many relationships

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <meaghantron@xxxxxxxxx> wrote in message
news:4ff4079d-aa67-474e-b37f-3703766f259e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 <KARLDE...@xxxxxxxxxxxxxxxxxxxxxxxxx>
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


.

Relevant Pages

  • Re: inserting multiple columns from combo list
    ... There are multiple things going on at the same time for a combo box. ... This is the ONE field from the query ... each field in that combo box will use in the display when you press ... the combobox space, and this may or may NOT be the value that is saved/ ...
    (microsoft.public.access.forms)
  • Re: Multiple Many-to-Many relationships
    ... needs to be able to accommodate multiple authors, ... Type – (Newspaper, Magazine, Blog, etc.) ... when I create a query showing all related article fields from ... and edit those records. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Running Parameter Query from a form
    ... What you can do is use the query you are using now but rather than just run ... You get multiple rows with multiple columns you need to see, ... Is it displaying in a sub form or is it popping up in a grid ... Is there a way to display the results within the form itself? ...
    (microsoft.public.access.formscoding)
  • Re: [PHP] IF with multiple values for a variable
    ... My query will display the ... statement assigning the multiple values. ... I can get it to work if I create a seperate if statement for each type ...
    (php.general)
  • Re: [PHP] IF with multiple values for a variable
    ... My query will ... display the ... statement assigning the multiple values. ...
    (php.general)