Main Report and Subreports repeating data

[PinkLady50]

I have a main report with 5 subreports. The main report starts by pulling the Contractor from a combobox on the Main form which I pass to the query. (Works fine). The main report is pulling the data for this Contractor multiple times in the Detail section for the same record and the 5 subreports are repeating the data for that record as well.



How do I get the report to only report one instance of each record and one instance for each subreport.

I would appreciate any help you can provide.

[Bulzie]

I'd say one of your subreport recordsets has multiple records for that contractor? Make a backup of the database first. Then in your report, remove all the subreports and see if it only gives the main record 1 time. Then add the first subreport(use ContractorID if that is the linking field in the LinkMaster and LinkChild properties on the Subform). See if that gives 1 record as you are wanting. If it gives multiple you have multiple records for that Contractor. If gives single record, add 2nd Subreport, etc. until you see where issue is.

What field from your main are you linking to the subreports?

[aytee111]

I'd say this is your problem:

The main report is pulling the data for this Contractor multiple times

Pretend that there are no subreports, what do you want the report to show in the detail section? Just the contractor name, once for each contractor? Get the record source for the main report correct.

[PinkLady50]

Ok. You are correct in that I have several records for each Contractor. How do I edit the query ( I used the wizard) to tell the query to only give me one instance on the record when it meets the following criteria (ContractNum,TaskOrder and CLIN). This is how the report should be detailing. I'm thinking Select DISTINCT but I am not sure how to use this in the query or with a statement in the Record Source. I put SELECT DISTINCT at the beginning and it didn't work.

SELECT tblPerfIssues.IssueKey, tblPerfIssues.ContractNum, tblPerfIssues.TaskOrder, tblPerfIssues.CLIN, tblPerfIssues.Contractor, tblPerfIssues.MTFDTF, tblPerfIssues.LaborBand, tblPerfIssues.LaborCat, tblPerfIssues.ClinicalArea, tblPerfIssues.IndCov, tblPerfIssues.IssueID FROM tblPerfIssues GROUP BY tblPerfIssues.IssueKey, tblPerfIssues.ContractNum, tblPerfIssues.TaskOrder, tblPerfIssues.CLIN, tblPerfIssues.Contractor, tblPerfIssues.MTFDTF, tblPerfIssues.LaborBand, tblPerfIssues.LaborCat, tblPerfIssues.ClinicalArea, tblPerfIssues.IndCov, tblPerfIssues.IssueID, tblPerfIssues.IssueKey, tblPerfIssues.IssueKey HAVING (((tblPerfIssues.Contractor)=[Forms]![frmPerfIssuesMain]![cboContractorSelection]));

[aytee111]

Select only the fields that you need - the fields to be displayed and any fields used in linking the subreports. This should be enough to make single records. If your source table contains more than one record per contractor then GROUP BY will not work. Which is the field(s) that are duplicated? These must be FIRST or MAX or something. Get this query working prior to coming back to the report.

[ChaseC]

Try opening the query and adding in the tblContractors table to the builder window. A line should automatically connect tblPerfIssues to tblContractors (based on what looks like to me the field 'Contractor'). Double click on the connection to edit it. It sounds like you need the third option: include ALL records from tblContractors and only those records from tblPerfIssues where the joined fields are equal - if not, try the second option as well. Let me know if this works!

[PinkLady50]

OK. I have this working to a point!!! But I still need help!! The headers in the report are as follows:

MTFDTF Contract Number Task Order CLIN Labor Category Coverage Clinical Area

When I use this Select statement it gives me the correct records for this Contractor. However, when I try to add the other fields to the end of the Select statement to get them to appear on the form it stops working correctly.

How can I add the other fields I need without messing up the Select DISTINCT statement?

SELECT DISTINCT tblPerfIssues.ContractNum, tblPerfIssues.TaskOrder, tblPerfIssues.CLIN FROM tblPerfIssues WHERE (((tblPerfIssues.Contractor)=[Forms]![frmPerfIssuesMain]![cboContractorSelection]));

[aytee111]

Select Distinct is the same as having Group By on every field. Which, as I said, isn't working because there are multiple records per contractor on your source. Reread post #5.

[aytee111]

Relooking at your picture, the Group By should have worked! There seems to be only one field in your SQL which isn't being displayed, LaborBand. Could this be the problem?

[PinkLady50]

It is working!!! It is grouping the records on the report by unique ContractNum, TaskOrder and CLIN just the way I want. Now I want to get the additional data for the fields on the report. Is there a way to write this in the Record Source or is there some VBA code I could use to get this to work.

I have 4 reports that will all run the same logic so once I get this report working I am good to go for all the other reports.

[PinkLady50]

When I add the other fields like MTF and LaborCategory to the end of the SELECT DISTINCT tblPerfIssues.ContractNum, tblPerfIssues.TaskOrder, tblPerfIssues.CLIN statement it changes the output. It continues to want to find a unique record with more than these 3 fields so it comes back wrong.

[aytee111]

What are you trying to print on the main report? I keep asking the same question - what fields are duplicated and what do you want done with them! Why not make them a subreport too?

[PinkLady50]

I would the following fields which are in the report header to display with data.
Currently only the ContractNum, TaskOrder and CLIN are displaying data.

MTF, ContractNum, TaskOrder, CLIN, LaborCat, Coverage, Clinical Area

[PinkLady50]

I would the following fields which are in the report header to display with data related to the ContractNum, TaskOrder and CLIN.

[aytee111]

I think it is time to see the database, is that possible? Remove all non-essential data, provide only a few samples.