Query Against Duplicate Valuess

[Ninabunny]

Please help !!! Need to create a report without duplicate addresses?  How?

 

[Lightning]

What is your report based on - a query or a table?<br><br>If it's based on a query, open the query and set the query properties to select Unique values or unique records - whichever suits your needs<br><br>If your running it from a table, open the report in design view, select your address field and set the &quot;Hide Duplicates&quot; property to yes.&nbsp;&nbsp;Note: this will still display multiple records where the address is the same - it simply won't show the address for those records.<br><br>Hope this helps.<br><br>Lightning

 

[Ninabunny]

Unique,<br>I need to set up a report based on a table (which has multiple duplicate last names and possibly addresses) if this query is run it should be run in order to generate a report not showing all the records with duplicate addresses, just one.<br>

 

[gcastillo]

Use this query:<br>SELECT DISTINCTROW FIRST([field]) AS myvar, COUNT([field]) AS anotherVar<br>FROM table&nbsp;&nbsp;GROUP BY [field]<br>HAVING COUNT([field])&gt;=1;<br><br>&nbsp;It's gonna give you only the list of the fields, even if they have duplicates. I mean, if you have 1,1,1,1,2,2,3,3,3 in your table, it's gonna give you 1,2,3<br>Hope it helps............<br>