Phil Collins wrote:
Hi.
Thanks for the replies.However, I have still not solved the problem.
the data comes out of a CRM system as an excel report, I can only
influence the fields that are in the report.
I still need to get a query to get duplicate addressed where there
are three or more, then add the other fields that I require and finaly the
count of how many dupes there were.
I'm beginning to think that it cannot be done in access, It's driving
me nuts at the moment. any pointers would be very much appreciated.
Thanks again for taking the time to respond.
Phil
1. to create a query where there are duplicates of address... create a
query with all the fields you want on it (but leave out unique ID
fields if you have any) then click the sigma button to get a totals
query then add a field and make it a count field. Set the criteria to=3. If you really want, if the ID is unique, you can join back to the
original table and grab a value. Otherwise, you could use FIRST
instead.
Not sure it can be done? Umm... I'm sure it can. You just need to
perhaps describe it better or take on step at a time. I would start
with a totals query with FIRST and group by your duplicated fields.
then throw a count in there if you want. Just build it incrementally
or you will definitely go nuts.