Duplicates

[richard]

I had a problem a while ago which Allen Browne kindly sorted, however the
powers that be have now asked for the report to also have a date range. This
has now once again thrown up the original problem and this is as follows

The report is to show which batches have been completed. However each batch
can have hundreds of records upon which the date analysed is saved. What I
want the report to show is only the batch number once rather than alongside
every record within the batch. Below is the SQL code

SELECT [Batch Details].[Batch Number], [Sample Details].[Analysis Date]
FROM [Batch Details] INNER JOIN [Sample Details] ON [Batch Details].[Batch
Number] = [Sample Details].[Batch Number]
WHERE ((([Sample Details].[Analysis Date]) Between [Start Date] And [End
Date]) AND ((Exists (SELECT[Batch Number] FROM [Sample Details] WHERE [Sample
Details].[Batch Number] = [Batch Details].[Batch Number] AND [Sample
Details].Result Is Null))=False))
ORDER BY [Batch Details].[Batch Number];

Any help greatly appreciated

Richard

 

[Andy Hull]

Hi Richard

Try the following...
(I have put MAX() around the Analysis Date and changed the "ORDER BY" to
"GROUP BY")

SELECT [Batch Details].[Batch Number], MAX([Sample Details].[Analysis Date])
FROM [Batch Details] INNER JOIN [Sample Details] ON [Batch Details].[Batch
Number] = [Sample Details].[Batch Number]
WHERE ((([Sample Details].[Analysis Date]) Between [Start Date] And [End
Date]) AND ((Exists (SELECT[Batch Number] FROM [Sample Details] WHERE [Sample
Details].[Batch Number] = [Batch Details].[Batch Number] AND [Sample
Details].Result Is Null))=False))
GROUP BY [Batch Details].[Batch Number];


hth

Andy Hull