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
|
|||
|
|||
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 |
#2
|
|||
|
|||
Multiple Many-to-Many relationships
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 |
#3
|
|||
|
|||
Multiple Many-to-Many relationships
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 |
#4
|
|||
|
|||
Multiple Many-to-Many relationships
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 |
#5
|
|||
|
|||
Multiple Many-to-Many relationships
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? |
#6
|
|||
|
|||
Multiple Many-to-Many relationships
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? |
#7
|
|||
|
|||
Multiple Many-to-Many relationships
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 |
#8
|
|||
|
|||
Multiple Many-to-Many relationships
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 |
#9
|
|||
|
|||
Multiple Many-to-Many relationships
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 |
#10
|
|||
|
|||
Multiple Many-to-Many relationships
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 |
|
Thread Tools | |
Display Modes | |
|
|