find dupplicate record in a table-and display it

  • Thread starter Thread starter Liora
  • Start date Start date
L

Liora

I have a table "t_qun_loc" that contain 4 fields:
hnum,lotnum,location,qun
I have to find all the record at the table that are
dupplicate - have the same "lotnum" and "hnum"
and after find them - display them on the screen in edit
mode so the user can fix it

What is the best way to do it?

Liora
 
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
 
Create a new query.
In query design view, switch to SQL View (View menu), and paste this in:

SELECT t_qun_loc.hnum, t_qun_loc.lotnum,
Count(t_qun_loc.hnum) AS CountOfhnum,
First(t_qun_loc.location) AS FirstOflocation,
First(t_qun_loc.location) AS FirstOflocation1,
Last(t_qun_loc.qun) AS LastOfqun,
Last(t_qun_loc.qun) AS LastOfqun1
FROM t_qun_loc
GROUP BY t_qun_loc.hnum, t_qun_loc.lotnum
HAVING (((Count(t_qun_loc.hnum))>1));

If works by grouping on the first two fields (since that is how you defined
a duplicate), and shows the number of duplicates and the Location and Qun of
the first and last match.
 
thanks the query display only one line
I want to display the two duplicate line - so the user
can edit the quantity for each line.

I preffer to display it in a form format
how can I do it

thanks,
liora
 
The query locates the duplicates for you, but it is not editable.

You could create a form (or edit the table if you prefer), and use the Find
toolbar button (binocular icon) to find the record that it identified, for
editing.
 
Back
Top