0

How can I query a table to find duplicate Memo field entries in MS Access?

Also please note that I don't have a primary key in my table.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
Ibra
  • 7
  • 3

2 Answers2

1

Here is one possible method:

select t.MemoField
from YourTable t
group by t.MemoField
having count(*) > 1

To test for duplication across multiple fields, you might use:

select t.MemoField1, t.MemoField2, t.MemoField3
from YourTable t
group by t.MemoField, t.MemoField2, t.MemoField3
having count(*) > 1
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • select [table1].[Name], [table1].[Text], [table1].[Date], [table1].[Description], [table1].[TestDescription] from [table1] group by [table1].[Result Details] having count(*) > 1 and [table1].[Name] In (SELECT [Name] FROM [table1] As Tmp GROUP BY [Name]) ORDER BY [table1].[Name]; i had an error that says Your Query does not include the specified expression "Name" as part of an aggregate function – Ibra Jul 07 '19 at 12:48
  • That's because each field either needs to be aggregated using an aggregate function (such as `sum`, `min`, `max` etc.) or appear in the `group by` clause. – Lee Mac Jul 07 '19 at 12:53
  • All of these field are text which idon’t need an aggregated function with it. Please advice about how to fox it? – Ibra Jul 07 '19 at 13:17
  • It would depend on whether you are looking for duplication in one field, or duplication formed by multiple fields. If you have records for which the duplication is formed by multiple fields, then each field should appear in both the `select` and `group by` clause. – Lee Mac Jul 07 '19 at 13:21
  • What if i want to find duplicate rows ? – Ibra Jul 07 '19 at 13:52
  • Include all fields in both the `select` and `group by` clauses. – Lee Mac Jul 07 '19 at 14:00
  • What if i want to compare the result of this table with another table to find duplicate records ? – Ibra Jul 08 '19 at 06:54
  • You could source the data for the above query from a `union all` of the two tables, but there are also many other approaches. Regardless, this is starting to stray from the original question. – Lee Mac Jul 08 '19 at 12:14
  • https://stackoverflow.com/questions/56935593/how-to-find-out-duplicate-records-from-multiple-tables-in-ms-access-based-on-two/56935678#56935678 could you please help me here – Ibra Jul 09 '19 at 17:39
0

If Name is the primary key column and MemoColumn is the column for which you want to find duplicates, you can use EXISTS:

SELECT t.* FROM tablename AS t
WHERE EXISTS (
  SELECT 1 FROM tablename AS tt
  WHERE t.Name <> tt.Name and t.MemoColumn = tt.MemoColumn  
)
forpas
  • 160,666
  • 10
  • 38
  • 76
  • What if i want to compare the result of this table with another table to find duplicate records ? – Ibra Jul 08 '19 at 12:05
  • Then you have to post another question where you clearly explain what you want to do and provide sample data and expected results. – forpas Jul 08 '19 at 12:07