COUNT + HELP

  • Thread starter Thread starter J.J.
  • Start date Start date
J

J.J.

Hi
I have little problem and need help. The problem is next:
in my query I have Manufactory_ID, Product_ID and Manufacture_Date.
i need to number the Manufacture_dates as they follow with regard to Manufactory_ID and Product_ID.
an example
Manufactor_ID: TERRA
Product_ID: PGT
M_Date: 12/06
NUMBER:1
Manufactor_ID: TERRA
Product_ID: PGT
M_Date: 14/08
NUMBER:2...etc,

but
Manufactor_ID: TERRA
Product_ID: NINA
M_Date: 12/06
NUMBER:1

and also duplicates (if they exist) doesn't need to count

Any sugestiones about the expression solving this problem?
many thanks
J.J.
 
I have little problem and need help. The problem is next:
in my query I have Manufactory_ID, Product_ID and Manufacture_Date.
i need to number the Manufacture_dates as they follow with regard to Manufactory_ID
and Product_ID.
an example
Manufactor_ID: TERRA
Product_ID: PGT
M_Date: 12/06
NUMBER:1
Manufactor_ID: TERRA
Product_ID: PGT
M_Date: 14/08
NUMBER:2...etc,

but
Manufactor_ID: TERRA
Product_ID: NINA
M_Date: 12/06
NUMBER:1

and also duplicates (if they exist) doesn't need to count

Hi JJ,

try (change "yourtable" to actual name of your table):

SELECT
t1.Manufactor_ID,
t1.Product_ID,
t1.M_Date,
(SELECT COUNT(*)
FROM yourtable as t2
WHERE
t2.Manufactor_ID = t1.Manufactor_ID
AND
t2.Product_ID = t1.Product_ID
AND
t2.M_Date > t1.M_Date) + 1 As [Number]
FROM
yourtable As t1;


or:


Save a DISTINCT query first:

qryUnique:

SELECT DISTINCT
Manufactor_ID,
Product_ID,
M_Date
FROM yourtable;

then:

SELECT
t1.Manufactor_ID,
t1.Product_ID,
t1.M_Date,
(SELECT COUNT(*)
FROM qryUnique as t2
WHERE
t2.Manufactor_ID = t1.Manufactor_ID
AND
t2.Product_ID = t1.Product_ID
AND
t2.M_Date > t1.M_Date) + 1 As [Number]
FROM
yourtable As t1;

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Back
Top