count unique values from a field in a query into another column

[leanne123]

I have a table with FCR numbers. I need to have a new column label the first set of fcr numbers as 1, then second as 2 as in example below:

FCR Inv No.
abc123 1
abc123 1
abc123 1
abc345 2
abc345 2
def678 3

my query is currently:
begin code:
SELECT qryTLP_cmf1.[FCR Number], qryTLP_cmf1.InvoicePeriod
FROM qryTLP_cmf1 GROUP BY qryTLP_cmf1.[FCR Number], qryTLP_cmf1.InvoicePeriod;
end code:

 

[Golom]

Can you clarify?

You state that you want a "new column" ... presumably whenever the InvoicePeriod changes BUT your example shows only one column for "Inv No."

If that is really what you want ... why do you need that?

 

[leanne123]

Apologize. The period has nothing to do with it. I will simply concatenate the count with this column once it is calculated.

The Invoice number will change whenever the FCR changes. My code does include the column where this 'count' will be stored.

Hope that clarifies.

 

[Golom]

Hmmmm

Relational databases have no inherent ordering of records so concepts like FIRST or LAST don't have a ready interpretation.

For example if you

ORDER BY FCR

then the FCR numbers lowest in the collating sequence will appear FIRST but

ORDER BY FCR DESC

will show the highest values FIRST.

If you have assigned 1, 2, 3, ... using the first ORDER BY then you will see those numbers in reverse order with the second ORDER BY clause.

There is also the issue of what happens when an FCR is deleted (missing number) or a new one is added (??). The upshot of all this is that the number so assigned is a computed value and you should not store computed values in a table.

 

[PHV]

You wanted something like this ?

SELECT A.FCR,(SELECT Count(*) FROM (SELECT DISTINCT FCR FROM tblFCR) AS B WHERE FCR<=A.FCR) AS InvoiceNo
FROM tblFCR AS A

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886