Duplicating-RePost

[Bryan Hughes]

Hello,

I have a subreport in details section of a main report.

The sub report is duplicating the same records twice.

The Link Master Field and Link Child Field are set
The Query is Grouped that is bounded to subreport

I can't figure out why it duplicates the same recordset for the sub report

How can I fix this?

-Bryan

 

[Ken Snell]

Check the query that is the subform's recordsource. Is it producing the
double records?

 

[Bryan Hughes]

Ken,

No, the Query is not producing double records.

Also, what I can't figure out is why the subreport does not always duplicate
when it prints?

-Bryan

 

[Ken Snell]

See if you're getting duplicate records in the main report's recordsource
query...perhaps the doubling up is occurring there.

Often, duplication occurs when a join line is missing. If there are more
than one record in a joined table with the foreign key that is the joined
key, this can happen. If only one record, the duplication won't be seen.

Let's rule out the queries as the source of duplication before we go
further.

 

[Bryan Hughes]

Ken,

Here is the Main Reports Query:
SELECT DISTINCTROW tblMain_File.CFID, tblMain_File.IOD, tblMain_File.FCAD,
tblMain_File.FST, IIf([FST]<>"NA","OPEN","CLOSED") AS FS, tblFST.COD,
tblFST.CM, tblFST.ARS, tblFST.CSAPP, tblFST.FAN, tblFST.FL, tblFST.FSP,
tblFST.FSTC, IIf([CID]<>"",[CID],"NOT A CLIENT") AS ClientID,
tblPrimary_Contact.FN & " " & tblPrimary_Contact.LN AS Name,
tblPrimary_Contact.SA, tblPrimary_Contact.City, tblPrimary_Contact.State,
tblPrimary_Contact.ZipCode, tblPrimary_Contact.HP, tblPrimary_Contact.CP,
tblPrimary_Contact.MP, tblPrimary_Contact.WP
FROM (tblMain_File INNER JOIN tblFST ON tblMain_File.CFID = tblFST.CFID)
INNER JOIN tblPrimary_Contact ON (tblMain_File.CFID =
tblPrimary_Contact.CFID) AND (tblFST.CFID = tblPrimary_Contact.CFID)
WHERE
(((tblMain_File.CFID)=[Forms]![frmCase_File_Report_Parameter]![txtCFID]))
GROUP BY tblMain_File.CFID, tblMain_File.IOD, tblMain_File.FCAD,
tblMain_File.FST, IIf([FST]<>"NA","OPEN","CLOSED"), tblFST.COD, tblFST.CM,
tblFST.ARS, tblFST.CSAPP, tblFST.FAN, tblFST.FL, tblFST.FSP, tblFST.FSTC,
IIf([CID]<>"",[CID],"NOT A CLIENT"), tblPrimary_Contact.FN & " " &
tblPrimary_Contact.LN, tblPrimary_Contact.SA, tblPrimary_Contact.City,
tblPrimary_Contact.State, tblPrimary_Contact.ZipCode, tblPrimary_Contact.HP,
tblPrimary_Contact.CP, tblPrimary_Contact.MP, tblPrimary_Contact.WP;

It is not doubling up in the query as far as I know.

-Bryan

 

[Ken Snell]

Can't tell from this SQL (without seeing the tables themselves) if this
could cause duplication. But I do see something that appears as if it could
be a problem. You're linking the tblMain_File table to both the tblFST and
tblPrimary_Contact tables via the CFID field, and then you also link tblFST
and tblPrimary_Contact tables by that same field. Normally, I wouldn't
expect to see this "complete" circle of joins.

Are you sure you need to link tblPrimary_Contact to tblMain_File when you
already link to tblPrimary_Contact table through the tblFST table? This
could be the source of duplicate records. But you would see those duplicate
records in the datasheet view of the query when you look at it. And you're
saying you don't see duplicated records in that view?

If this isn't it, zip up the database (< 1 MB) and email it to me so that I
can take a look. My email address is obtained by deleting this is not real
from the address.

--
Ken Snell
<MS ACCESS MVP>

Ken,

Here is the Main Reports Query:
SELECT DISTINCTROW tblMain_File.CFID, tblMain_File.IOD, tblMain_File.FCAD,
tblMain_File.FST, IIf([FST]<>"NA","OPEN","CLOSED") AS FS, tblFST.COD,
tblFST.CM, tblFST.ARS, tblFST.CSAPP, tblFST.FAN, tblFST.FL, tblFST.FSP,
tblFST.FSTC, IIf([CID]<>"",[CID],"NOT A CLIENT") AS ClientID,
tblPrimary_Contact.FN & " " & tblPrimary_Contact.LN AS Name,
tblPrimary_Contact.SA, tblPrimary_Contact.City, tblPrimary_Contact.State,
tblPrimary_Contact.ZipCode, tblPrimary_Contact.HP, tblPrimary_Contact.CP,
tblPrimary_Contact.MP, tblPrimary_Contact.WP
FROM (tblMain_File INNER JOIN tblFST ON tblMain_File.CFID = tblFST.CFID)
INNER JOIN tblPrimary_Contact ON (tblMain_File.CFID =
tblPrimary_Contact.CFID) AND (tblFST.CFID = tblPrimary_Contact.CFID)
WHERE
(((tblMain_File.CFID)=[Forms]![frmCase_File_Report_Parameter]![txtCFID]))
GROUP BY tblMain_File.CFID, tblMain_File.IOD, tblMain_File.FCAD,
tblMain_File.FST, IIf([FST]<>"NA","OPEN","CLOSED"), tblFST.COD, tblFST.CM,
tblFST.ARS, tblFST.CSAPP, tblFST.FAN, tblFST.FL, tblFST.FSP, tblFST.FSTC,
IIf([CID]<>"",[CID],"NOT A CLIENT"), tblPrimary_Contact.FN & " " &
tblPrimary_Contact.LN, tblPrimary_Contact.SA, tblPrimary_Contact.City,
tblPrimary_Contact.State, tblPrimary_Contact.ZipCode, tblPrimary_Contact.HP,
tblPrimary_Contact.CP, tblPrimary_Contact.MP, tblPrimary_Contact.WP;

It is not doubling up in the query as far as I know.

-Bryan