Hide duplicate values in a report based on a query

[Guest]

I am not quite sure if this is a query or report question.
I want to make a tabular report on the basis of the following query:

SELECT tblPROJECTS.ProjectID, tblPROJECTS.Projectsymbol, tblPROJECTS.Title,
tblPROJECTS.Region, tblPROJECTS.Status, tblPROJECTS.StatusDate,
tblProjectSubject.SubjectName, tblPROJECTS.Budget,
tblProjectTargetGr.TargetgrName, tblPROJECTS.Projectdocument,
tblPROJECTS.Donor, tblPROJECTS.[Start Date], tblPROJECTS.[Finish Date],
tblPROJECTS.FinEvalRp, tblProjectCountry.CountryName
FROM ((tblPROJECTS INNER JOIN tblProjectCountry ON tblPROJECTS.ProjectID =
tblProjectCountry.ProjectID) INNER JOIN tblProjectSubject ON
tblPROJECTS.ProjectID = tblProjectSubject.ProjectID) INNER JOIN
tblProjectTargetGr ON tblPROJECTS.ProjectID = tblProjectTargetGr.ProjectID;

The tblPROJECTS have a one-to-many relationship to the other three tables
and ProjectID, SubjctName, TargetgrName and CountryName are all primary keys..
I want my report to show each project with all subjects, target groups and
countries (all of the three can be multiple in each project) but each value
only showing once for each project. Most of the duplicate fields I can get
rid of by setting Hide duplicate to Yes in the report properties. However,
each time, for example, a new subject is listed in a project the target
groups are repeated.
Is there a way to get around this, either in the query design or in the
report design?
Niels

 

[tina]

have you tried building a new report using the Report Wizard (*not* the
AutoReport option), and setting up grouping levels for your data?

hth

 

[Marshall Barton]

I am not quite sure if this is a query or report question.
I want to make a tabular report on the basis of the following query:

SELECT tblPROJECTS.ProjectID, tblPROJECTS.Projectsymbol, tblPROJECTS.Title,
tblPROJECTS.Region, tblPROJECTS.Status, tblPROJECTS.StatusDate,
tblProjectSubject.SubjectName, tblPROJECTS.Budget,
tblProjectTargetGr.TargetgrName, tblPROJECTS.Projectdocument,
tblPROJECTS.Donor, tblPROJECTS.[Start Date], tblPROJECTS.[Finish Date],
tblPROJECTS.FinEvalRp, tblProjectCountry.CountryName
FROM ((tblPROJECTS INNER JOIN tblProjectCountry ON tblPROJECTS.ProjectID =
tblProjectCountry.ProjectID) INNER JOIN tblProjectSubject ON
tblPROJECTS.ProjectID = tblProjectSubject.ProjectID) INNER JOIN
tblProjectTargetGr ON tblPROJECTS.ProjectID = tblProjectTargetGr.ProjectID;

The tblPROJECTS have a one-to-many relationship to the other three tables
and ProjectID, SubjctName, TargetgrName and CountryName are all primary keys..
I want my report to show each project with all subjects, target groups and
countries (all of the three can be multiple in each project) but each value
only showing once for each project. Most of the duplicate fields I can get
rid of by setting Hide duplicate to Yes in the report properties. However,
each time, for example, a new subject is listed in a project the target
groups are repeated.
Is there a way to get around this, either in the query design or in the
report design?

Normally, this is handled by using the report's Sorting and
Grouping (View menu) feature. You can then put the fields
you only want to see once in its group header section.