Duplicate Reports as an output

[ramez75]

Hi,
I have been trying to make this work for 3 days and hasn't been successful.
I have a form and a subform and within the subform I have a button that allow the user to create a report. The report also has a subreport.

All works great when the subform which in turn the subreport have one record but when the subform and has lets say 3 records then I will have identical reports but all I need is one

I tried the "Hide Duplicate" feature but that doesn't work for me as I am getting the same report multiplied by the number of records in the subform.

My Report and Subreport are linked to a query. The query has 2 tables that are used in the Form and Subform.

"NCRNumber" is the common field between the 2 tables and links the form to the subform.

Example:

RecordID (Autonumber) = 1 (Main Table)
NCRNumber = 16-0001 (Main Table & Second Table) (Consist of 4 "PartNo")
PartNo =1234, 4567, 0987, 1098 (Second Table)
Qty = 5, 2, 4, 2 (Second Table)

So what's happening when I run the query which feeds the report make me get in this case for similar reports

Query Output
Record ID NCRNumber PartNo QTY
1 16-0001 1234 5
1 16-0001 4567 2
1 16-0001 0987 4
1 16-0001 1098 2

Right now I save the report as pdf and use PDFCreator to delete the duplicate reports. Its time consuming

Hope I am making sense.

Any tips on this is greatly appreciated

Thanks

RB

 

[JHB]

..
Query Output
Record ID NCRNumber PartNo QTY
1 16-0001 1234 5
1 16-0001 4567 2
1 16-0001 0987 4
1 16-0001 1098 2

Is the above what you get 3 times?
Else show what you want the output should be?
Mostly when a report repeats, the recordsource is wrong.

 

[ramez75]

JHB, I have attached a copy of the report I get now. All I need is the first 2 pages but the pages keep repeating as you can see.

What do I need to add to the query or report to stop that

Thank you

RB

 

[JHB]

What is the recordsource for the main report, I think the problem lays here, (as mention in my first post).
It should only be one row of data, but I think it is 13 rows.
Else post your database with some sample data, zip it.

 

[ramez75]

JHB,

Attached is a copy of the database. So you see what I am talking about. go for example to NCR 16-0155 and click on the "Print/Email SOP MT005.F1"

Hope it make sense now.

Thanks for looking into it

RB

 

[sneuberg]

I see it and I think it has something to do with the filter on the report. If you take the filter (qryforSOPMT005F1Filter) off from the line DoCmd.OpenReport strDocName, acViewPreview and make it the Record Source for the rptSOPMT005F1 report you don't get the repetition.

Is there any reason why you aren't doing it this more direct way?

 

[ramez75]

But if I remove "qryforSOPMT005F1Filter" like you suggest then I am getting all the records in one report that's why I used qryforSOPMT005F1Filter.

Can you show me how you could make it work without qryforSOPMT005F1Filter

Thanks

RB

I see it and I think it has something to do with the filter on the report. If you take the filter (qryforSOPMT005F1Filter) off from the line DoCmd.OpenReport strDocName, acViewPreview and make it the Record Source for the rptSOPMT005F1 report you don't get the repetition.

Is there any reason why you aren't doing it this more direct way?

 

[sneuberg]

You make the qryforSOPMT005F1Filter query the Record Source for the report as I have done in the attached database.

 

[sneuberg]

If you don't want to make qryforSOPMT005F1Filter the record source for the report because you want to reuse the report with different criteria, then I recommend changing the WHERE parameter in the report open.

 

[ramez75]

I looked at your "test mod" database but it still shows if you look at NCR 16-0155 a duplicated report when you click on "Print/Email SOP MT005F1". The report should be 2 pages but its showing 26 pages

You make the qryforSOPMT005F1Filter query the Record Source for the report as I have done in the attached database.

 

[ramez75]

The issue I am having in the report is when the NCR has multiple part numbers other than that its all good. Currently I have to save the report as PDF and use PDFCreator to delete the duplicate pages and then use it.

 

[sneuberg]

I looked at your "test mod" database but it still shows if you look at NCR 16-0155 a duplicated report when you click on "Print/Email SOP MT005F1". The report should be 2 pages but its showing 26 pages

Oops, Yes it does. I'll get you a fix shortly.

 

[sneuberg]

Maybe not so shortly. I now see the subreport, but I can't see it's purpose. Why was this done with a subreport rather than just group the data from the tblNCR?

 

[ramez75]

I thought that's how to do it from when I was designing database. If it's not to much to ask can you show me how u group them....
I am open to suggestions if it resolve the issue and make the database better

Thanks

RB

 

[JHB]

Try it now.
The problem was the way you had the recordsource.

 

[sneuberg]

Edited: I think I had a brain fart here. Rather than what follows it would probably be simpler just to leave the tblNCRSubform out of the Record Source query. Then the DISTINCT keyword wouldn't be needed.

It's probably easier to resolve this problem using the report the way it is. The problem is that the record source for the main report has a record for each part so the report gets repeated for each part. So we want a record source for the main report to have only one record. I created one in the attached database with the name "qrySOPMT005F1 Record Source" I made it the record source of the main report rptSOPMT005F1. The SQL of this is:

SELECT DISTINCT tblNCR.NCRRecordID, tblNCR.DateNCObs, tblNCR.ProductLine, tblNCR.DecNC, tblNCR.ScrapDate, tblNCR.Accounts, tblNCR.NCDetectedBy, tblNCRSubform.NCRNumber
FROM tblNCR INNER JOIN tblNCRSubform ON tblNCR.NCRNumber = tblNCRSubform.NCRNumber
WHERE (((tblNCR.Accounts) Is Not Null) AND ((tblNCRSubform.NCRNumber)=[Forms]![frmNCR]![NCRNumber]));

The thing to note in this query is the DISTINCT keyword which retrieves only one record with only the fields required in the main report. This query doesn't have any part number information. That's left to the subreport.

I think I got it right this time. Please check it and let me know.

 

[sneuberg]

The "qrySOPMT005F1 Record Source" query in the attached database has been simplified to:

SELECT tblNCR.NCRRecordID, tblNCR.DateNCObs, tblNCR.ProductLine, tblNCR.DecNC, tblNCR.ScrapDate, tblNCR.Accounts, tblNCR.NCDetectedBy, tblNCR.NCRNumber
FROM tblNCR
WHERE (((tblNCR.Accounts) Is Not Null) And ((tblNCR.NCRNumber)=Forms!frmNCR!NCRNumber));

 

[sneuberg]

Maybe not so shortly. I now see the subreport, but I can't see it's purpose. Why was this done with a subreport rather than just group the data from the tblNCR?

I figured out what you were doing and the subreport is the smart way to do this. Grouping the data would have required a lot of groups.

 

[ramez75]

sneuberg thank you so much, that's exactly what I needed. I would have never thought of the way you did it. You made it look so simple wow

Thank you again for taking the time to help out

RB

The "qrySOPMT005F1 Record Source" query in the attached database has been simplified to:

SELECT tblNCR.NCRRecordID, tblNCR.DateNCObs, tblNCR.ProductLine, tblNCR.DecNC, tblNCR.ScrapDate, tblNCR.Accounts, tblNCR.NCDetectedBy, tblNCR.NCRNumber
FROM tblNCR
WHERE (((tblNCR.Accounts) Is Not Null) And ((tblNCR.NCRNumber)=Forms!frmNCR!NCRNumber));