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
|
|||
|
|||
Hide duplicate values in a report based on a query
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 |
#2
|
|||
|
|||
Hide duplicate values in a report based on a query
have you tried building a new report using the Report Wizard (*not* the
AutoReport option), and setting up grouping levels for your data? hth "NielsE" wrote in message ... 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 |
#3
|
|||
|
|||
Hide duplicate values in a report based on a query
NielsE wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reporting subreport total on main report | BobV | Setting Up & Running Reports | 22 | November 1st, 2005 03:19 AM |
Office 2003 installation problem, log file attached.... | Ryan | Setup, Installing & Configuration | 0 | January 20th, 2005 06:57 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |