Need to count duplicate records....

  • Thread starter Thread starter Lee Bassom
  • Start date Start date
L

Lee Bassom

Got a simple problem that has me stumpped :(

I have some data records that contain duplicate records.
What I want to do is count how many of each duplicate I have and record
the count in the record.

e.g.

(records)

field1 field2 field3 count
rec1 test1 red
rec2 test2 red
rec3 test1 blue
rec4 test4 blue
rec5 test7 blue

(updated records)

field1 field2 field3 count
rec1 test1 red 2
rec2 test2 red 2
rec3 test1 blue 3
rec4 test4 blue 3
rec5 test7 blue 3

Sounds simple...

Any ideas ?

Thanks in advance.
 
In a new Totals query, add the field that is causing the duplicates (in this
case field3) twice. Group By the first entry, and Count the second one.
Typical SQL would look like:

SELECT field3, Count(field3) AS DupeCount
FROM [MyDupeQuery]
GROUP BY field3;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
That helped a little, but still not getting quite what I want.

UniqueID FieldA FieldB FieldC FieldABC Count
1 xx yy zzzz
2 aa bb cccc
3 xx yy zzzz

UniqueID is automatically generated (primary key)
User enters FieldA, FieldB and FieldC

What I want to be able to do is have FieldABC automatically created from
FieldA, FieldB and FieldC, e.g. aa:bb:cccc

I tried using the DefaultValue for FieldABC but couldn't figure out
anyway to set that up to reference FieldA, FieldB and FieldC

I also want to have the Count field entered automatically as a count of
FieldABC

UniqueID FieldA FieldB FieldC FieldABC Count
1 xx yy zzzz xx:yy:zzzz 2
2 aa bb cccc aa:bb:cccc 1
3 xx yy zzzz xx:yy:zzzz 2

I can get a query that gives me FieldABC and Count, but as soon as I try
to add UniqueID, all the counts go to 1

Currently I have a bodge that does the following:
1. Create a tempData table with FieldABC and Count
2. Update FieldABC in main data table to be FieldA:FieldB:FieldC
3. Update Count in main data table by linking (main)FieldABC to
(tmp)FieldABC

I'm sure there must be a better way to do this, but my knowledge of
access/sql is a little thin :(

I'm not using any forms, simply the data table.

Any help much appreciated
 
Back
Top