How can i add a counter for duplicate records in a query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want the query to create a counter for duplicate records from another column in the query. It will look something like this.

Letters Counte
A
B
B
B
C
C
D
E

Thanks

Corey Thac
 
I'm going to suggest you consider this output as sufficient:

Letters Count
A 1
B 3
C 2
D 1
E 1

When it says there are 3 "B"s, you can pretty well assume they could
be labelled as 1, 2, and 3. There's really no additional information
in listing B 3 times and putting 1, 2, and 3 beside them.

To do what I suggested, try something like:

SELECT Letters, COUNT(*)
FROM YourTable
GROUP BY Letters

It may be that you have other columns besides what you have shown, and
that there is a unique way or ordering the 3 rows with B in them. If
so, I have a solution for that. Please post more information and I
will give that a try.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Eventually I'm going to create a crosstab query and those numbers 1,2, and 3 will become columns. I want to number them exactly the way I laid out earlier. Please can you help? Thanks in advance
 
Dear Corey:

Here's a technical explanation of what's going on.

Given what you have shown so far, you may have 3 rows where the
Letters value is B. There is nothing that differentiates them one
from another. They are just members of an unordered set, called a
bag. It is not the case that any of them come before or after any
other. You will not be able to systematically place them in any
order, because there's nothing on which to base that order.

The only way I can think to differentiate between them is to assign
random values to them. Even doing that, there would be a small chance
that two of them may be assigned the same random value.

You could do something else. Using the query I already provided, you
could have columns that test the COUNT generated. The first column
would always have 1 in it. The second column could have 2 in it if
the COUNT were > 1, or null if not. The third column would be either
3 or null, and so on.

Basically, your cross tab idea still does nothing to increase the
information above what I gave in the COUNT query. Generating displays
that are not informative is not one of the designs behind query
language, so it's no surprise there's no tool readily available for
what you want.

Be aware that the crosstab, and any other approach, won't work when it
generates more than 255 columns.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I was trying to get away from a long explaination why I want access to do this but now I feel that I must do so. This is what I want the query to do. C1 is part numbers, C2 is the next higher assembly(The part C1 reports to to C2, one-to-many relationship), and C3 should start numbering them in sequence like the first example. Once I can get this data in this fashion, the crosstab query will make C3 into Column titles and the records in C2 will populate into the cells below the Fields as AA, AB, AC, etc.. If I don't do this, than the Field names will be called AA,AB, AC,and AD.

C1 C2 C
11 AA
11 AB
12 AB
13 AA
13 AB
13 AC
13 AD
14 AA

CoreyT
 
Dear Corey:

For me, this additional information is essential. It would appear
that C1 and C2 are unique taken together, and that C2 puts each set of
rows where C1 is the same into the order you want to display.

The SQL to do this is now quite apparent:

SELECT C1, C2,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.C1 = T.C1 AND T1.C2 < T.C2) AS C3
FROM YourTable T
ORDER BY C1, C2

This is not the crosstabbed version, but the intermediate query you
started asking about.

It meets my "information" requirement in that you could produce a
crosstab in this way, using the C2 values as the column headings, with
C3 values at the intersections.

C2 is an integral part of the problem and its solution.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top