I am building a database that will catalog metadata about projects and their associated files. To make things easier for users, there is a form for data entry, and a separate process by which users can search for and edit existing records. This search is done on a search form where they can enter up to five words/phrases which are matched simultaneously against 10 fields. Some of these fields are in the project-level table and some are in the file library-level table. I designed a query that selects all the fields in both tables when the words/phrases are matched. To display the results in an editable format, after the query is run, a form and subform open up where the search results can be edited, and the results are applied to the tables (this is a dynaset recordset). I enabled the creation of new records for the subform but not for the project-level main form. Deletion is disabled for both form and subform.
This all works, but there is one issue. When I run the query and the form comes up, the project information in the main form is duplicated as many times as there are project files. Because some of the projects have a lot of files, I would like there to be only one record displayed per project on the main form. Users can scroll through the subform to see all of the files, so there is no need for duplicating the project-level records. Is there a way to make this change?
I have attached my query below:
Code:
SELECT Project.ProjectName, Project.ProjectDescription, Project.Author, Project.FileLocation, Project.Keywords, Project.Client, Project.Dissemination, Library.FileName, Library.FileFormat, Library.FileDescription, Library.Keywords, Library.DateEntered, Project.ProjectID, Library.ProjectFileLocation
FROM (Project INNER JOIN Library ON Project.ProjectID = Library.ProjectID) INNER JOIN ProjectSearch ON Library.Search = ProjectSearch.SearchLink
WHERE ((((((Project.ProjectName) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Project.ProjectDescription) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Project.Author) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Project.Keywords) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Project.Client) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Project.Dissemination) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Library.FileName) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Library.FileFormat) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Library.FileDescription) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR (((Library.Keywords) Like "*" & [ProjectSearch]![Word1] & "*") AND ((ProjectSearch.Word1) Is Not Null))
OR ((ProjectSearch.Word1) Is Null))
AND((((Project.ProjectName) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Project.ProjectDescription) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Project.Author) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Project.Keywords) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Project.Client) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Project.Dissemination) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Library.FileName) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Library.FileFormat) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Library.FileDescription) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR (((Library.Keywords) Like "*" & [ProjectSearch]![Word2] & "*") AND ((ProjectSearch.Word2) Is Not Null))
OR ((ProjectSearch.Word2) Is Null))
AND ((((Project.ProjectName) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Project.ProjectDescription) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Project.Author) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Project.Keywords) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Project.Client) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Project.Dissemination) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Library.FileName) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Library.FileFormat) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Library.FileDescription) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR (((Library.Keywords) Like "*" & [ProjectSearch]![Word3] & "*") AND ((ProjectSearch.Word3) Is Not Null))
OR ((ProjectSearch.Word3) Is Null))
AND ((((Project.ProjectName) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Project.ProjectDescription) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Project.Author) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Project.Keywords) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Project.Client) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Project.Dissemination) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Library.FileName) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Library.FileFormat) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Library.FileDescription) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR (((Library.Keywords) Like "*" & [ProjectSearch]![Word4] & "*") AND ((ProjectSearch.Word4) Is Not Null))
OR ((ProjectSearch.Word4) Is Null))
AND ((((Project.ProjectName) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR (((Project.ProjectDescription) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR (((Project.Author) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR (((Project.Keywords) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR (((Project.Client) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR (((Project.Dissemination) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR (((Library.FileName) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR (((Library.FileFormat) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR (((Library.FileDescription) Like "*" & [ProjectSearch]![Word5] & "*") AND ((ProjectSearch.Word5) Is Not Null))
OR ((ProjectSearch.Word5) Is Null))
AND((((Library.DateEntered)>=([ProjectSearch].[EnteredStartDate]) And (Library.DateEntered)<=([ProjectSearch].[EnteredEndDate])) OR (((ProjectSearch.EnteredEndDate) Is Null) AND ((ProjectSearch.EnteredStartDate) Is Null)))))
AND(ProjectSearch.Word1 Is Not Null OR ProjectSearch.Word2 Is Not Null OR ProjectSearch.Word3 Is Not Null OR ProjectSearch.Word4 Is Not Null OR ProjectSearch.Word5 Is Not Null OR ProjectSearch.EnteredStartDate Is Not Null OR ProjectSearch.EnteredEndDate Is Not Null));