Find Duplicate Record

[clk]

Hi. I have a database that I am using to manipulate data to export to
another program. I import a text file, make modification and then
export another text file with all the modifications. One thing that
happens is the original file that is imported may contain duplicate
records.

Below is a scenario that exists. What I need to do is

Point_Name x1 y1
x2 y2
1 123 465
2 888 999
2 777 000
4 456 566

End result should be that Point_Name 2 leaves the x1/y1 coordinates
alone but the second Point-Name2 x1/y1 coordinates get placed in x2/y2
fields.

Point_Name x1 y1
x2 y2
1 123 465
2 888 999
777 000
4 456 566


I hope this makes sense. x2 and y2 are blank unless there are two
Point-Names. Any help would be greatly appreciated.

Thank you.

 

[John Spencer]

Can there be more than two sets of x and y?

You could try a query like the following if there is a MAXIMUM of two sets.

SELECT Point_Name
, First([TableName].X1) as X1
, First([TableName].Y1) as Y1
, IIF First([TableName].X1) = Last([TableName].X1),Null,Last([TableName].X1))
as X2
, IIF First([TableName].Y1) = Last([TableName].Y1),Null,Last([TableName].Y1))
as Y2
FROM [TableName]
GROUP BY Point_Name

If there can be more than two sets of x and y for a point_name value then I
would probably use VBA and a record set to process and reformat the information.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County