Help Me Fix Duplicate Transactions With 2 Or More Attachments

[aamer]

I am having a problem in a report.


When I attach a single attachment to a transaction the report works perfectly but when I attach 2 or more attachments it duplicates the tranaction and simlarly the totals are effected.
Can someone please guide me where the mistake is, I could not figue it out.
To eleborate this propblem I have attached the Snapshot of the report where the transaction # 4497 is duplicated.
The field name where attachments are attached is "purattach"

Following is the Query used for this report.

SELECT Pchase.PurCompanyName, Pchase.PurTransId, Pchase.[Purchase Date], Pchase.[Purchase Description], Pchase.[Party Challan #], Pchase.[Invoice #], Pchase.[Purchase Qty], Pchase.[Purchase Price], Pchase.[Purchase Units], Company.CoId, Pchase.[Pur Seller], ([Payment Made]+[Mobile Deduction]) AS Dbt, Pchase.[Amount Returned], [Purchase Remarks] & " " & [Stored At] & " " & [D2Party] & " " & [SB] & " " & [PPMTTYP] & " " & [TrNo] & " " & [Cheque PO #] & " " & [Podtd] & " " & [Ref] & " " & [PSQ] AS Rem, IIf([Amount Returned]=0,([Purqty]*[Purchase Price]+[GST Amount]),[Amount Returned]) AS PurCredit, Pchase.[Pur Varification], Pchase.[Cheque PO #], [Purchase Description] & " " & [Salery Month] AS Des, IIf([Supplier Bank]="Nil",Null,[Supplier Bank]) AS SB, IIf([Payment Type]="Nil",Null,[Payment Type]) AS PPMTTYP, IIf([Delivered To Party]="Nil",Null,[Delivered To Party]) AS D2Party, Pchase.purattach, Pchase.[Mobile Deduction], Pchase.[Payment Made], IIf(IsNull([purattach].[FileData])," ","Yes") AS Patt, Company.[NIC No], Voucher.VoucherNo, [Party Challan #] & " " & [VoucherNo] AS GPVOU, IIf([Truck #]="Nil"," ",[Truck #]) AS TrNo, Company.CoAttach, Company.NICcopyFront, Company.NICcopyBack, Pchase.[GST Rate], Pchase.[GST Amount], Pchase.[Total Amount], Pchase.GSTR, Pchase.Purqty, Pchase.[Purchase Cash Qty], Pchase.[Purchase Sales Tax], IIf(IsNull([purattach].[FileData])," ","@") AS ACatt, [Purchase Remarks] & " " & [Ref] AS ACattRem, Company.Mobile, Pchase.PurDate, IIf([Cheque PO #]>0,[Chq PO Date]," ") AS Podtd, Pchase.[EntryPLogin By], IIf([EntryPLogin By]="Aamer Sheikh","AAS",[EntryPLogin By]) AS TransBy, Pchase.[SupervisePLogin By], Company.ComapnyName, Company.EmployeeWorkLoc, Pchase.[Delivered To Party], Pchase.PPrb
FROM (Company INNER JOIN Pchase ON Company.ComapnyName = Pchase.PurCompanyName) LEFT JOIN Voucher ON Pchase.PurTransId = Voucher.[Vou ID]
ORDER BY Pchase.PurTransId, Pchase.[Purchase Date];






Your help is needed to solve this issue

[ranman256]

What do you mean 'attachment'? Email attachment? Field attachment?
if you mean JOIN another table then yes, you can get duplicate records.

any other context using attachments have no affect on records.

[NTC]

I share R256's confusion....but having said that, generically speaking, if one is getting multiple duplicate records when multiple attachments are involved - it strikes me as a classic 1:Many table join and that those 'attachments' represent records.....

Which is to say if I join the table with just Parent Name with the table of Children Names - then the resulting record set will repeat that Parent Name in as many records as there are Children Names - even if the Children Name field is not included in the query result. So I believe it is your join between tables that is the issue.

[aamer]

ranman256
attaching files i.e jpeg, excel, word etc as attachment to the transaction.

[aamer]

I have found the problem which was creating the duplicates.

In the Query I removed the following

IIf(IsNull([purattach].[FileData])," ","Yes") AS Patt
IIf(IsNull([purattach].[FileData])," ","@") AS ACatt

And

Added the following in the field of the report where the result of PATT was showing

=IIf(IsNull([purattach])," ","Yes")

And Added the following in the field of the report where the result of ACATT was showing

=IIf(IsNull([purattach])," ","@")


Now the duplicates are gone.



I don't know if this procedure is the correct way or not.




Now The Final Query On Report is as under.


SELECT Pchase.PurCompanyName, Pchase.PurTransId, Pchase.[Purchase Date], Pchase.[Purchase Description], Pchase.[Party Challan #], Pchase.[Invoice #], Pchase.[Purchase Qty], Pchase.[Purchase Price], Pchase.[Purchase Units], Company.CoId, Pchase.[Pur Seller], ([Payment Made]+[Mobile Deduction]) AS Dbt, Pchase.[Amount Returned], [Purchase Remarks] & " " & [Stored At] & " " & [D2Party] & " " & [SB] & " " & [PPMTTYP] & " " & [TrNo] & " " & [Cheque PO #] & " " & [Podtd] & " " & [Ref] & " " & [PSQ] AS Rem, IIf([Amount Returned]=0,([Purqty]*[Purchase Price]+[GST Amount]),[Amount Returned]) AS PurCredit, Pchase.[Pur Varification], Pchase.[Cheque PO #], [Purchase Description] & " " & [Salery Month] AS Des, IIf([Supplier Bank]="Nil",Null,[Supplier Bank]) AS SB, IIf([Payment Type]="Nil",Null,[Payment Type]) AS PPMTTYP, IIf([Delivered To Party]="Nil",Null,[Delivered To Party]) AS D2Party, Pchase.[Mobile Deduction], Pchase.[Payment Made], Company.[NIC No], Voucher.VoucherNo, [Party Challan #] & " " & [VoucherNo] AS GPVOU, IIf([Truck #]="Nil"," ",[Truck #]) AS TrNo, Company.NICcopyFront, Company.NICcopyBack, Pchase.[GST Rate], Pchase.[GST Amount], Pchase.[Total Amount], Pchase.GSTR, Pchase.Purqty, Pchase.[Purchase Cash Qty], Pchase.[Purchase Sales Tax], [Purchase Remarks] & " " & [Ref] AS ACattRem, Company.Mobile, Pchase.PurDate, IIf([Cheque PO #]>0,[Chq PO Date]," ") AS Podtd, Pchase.[EntryPLogin By], IIf([EntryPLogin By]="Aamer Sheikh","AAS",[EntryPLogin By]) AS TransBy, Pchase.[SupervisePLogin By], Company.ComapnyName, Company.EmployeeWorkLoc, Pchase.[Delivered To Party], Pchase.PPrb, Company.CoAttach, Pchase.purattach
FROM (Company INNER JOIN Pchase ON Company.ComapnyName = Pchase.PurCompanyName) LEFT JOIN Voucher ON Pchase.PurTransId = Voucher.[Vou ID]
WHERE (((Company.CoId)=230))
ORDER BY Pchase.PurTransId, Pchase.[Purchase Date];