putting order

  • Thread starter Thread starter Rodolfo Fontes
  • Start date Start date
R

Rodolfo Fontes

Hi group,

I've created a query that list the table below. I wanna do another
collun called "order" that list the order of this "Numero_NF".
It will be reseted everytime a new "Cod_NF_S" is called.
How can i do that?

Cod_NF_S Numero_NF
049040603093802 76
049040603093802 76
049040603093802 76
050040513120431 9
050040513120431 9
050040513120431 9
050040517145518 11
050040518181403 14
050040518182343 13
050040519110051 15
050040519152744 16
050040520153608 17
050040521175037 20
050040525110325 24
050040525110553 23
050040525110706 22


Thanks,
Rodolfo Fontes
 
Dear Rodolfo:

As I understand your request, you want to add a column to this that
ranks the rows according to the value of Numer_NF. Using the data
provided, this would look as shown below:

Cod_NF_S Numero_NF Rank
050040513120431 9 1
050040513120431 9 1
050040513120431 9 1
050040517145518 11 4
050040518182343 13 5
050040518181403 14 6
050040519110051 15 7
050040519152744 16 8
050040520153608 17 9
050040521175037 20 10
050040525110706 22 11
050040525110553 23 12
050040525110325 24 13
049040603093802 76 14
049040603093802 76 14
049040603093802 76 14

The query would look something like this:

SELECT Cod_NF_S, Numero_NF,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Numero_NF < T.Numero_NF) AS Rank
FROM YourTable T
ORDER BY Numero_NF

You must change YourTable to be the actual name of your table (or
query).

Notice how this handles ties.

As a query, it will "reset" any time you run the query, using the most
recent data.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom,

Sorry for not writing the way i want it to be.
So, you almost get the right point, the trouble is that, i wanna count
it, reseting every new Cod_NF_S
The SQL for this table is:

SELECT [T NF_S_Det].Cod_NF_S, [T NF_S_Det].Cod_NF_S_Det, [Order] AS Expr1
FROM [T NF_S_Det]
GROUP BY [T NF_S_Det].Cod_NF_S, [T NF_S_Det].Cod_NF_S_Det, [Order];

Cod_NF_S Numero_NF Rank
050040513120431 9 1
050040513120431 9 2
050040513120431 9 3
050040517145518 11 1
050040518182343 13 1
050040518181403 14 1
050040519110051 15 1
050040519152744 16 1
049040603093802 76 2
049040603093802 76 2
049040603093802 76 2


Tks,
Rodolfo Fontes
 
Dear Rodolfo:

No problem.

SELECT Cod_NF_S, Numero_NF,
(SELECT COUNT(*) + 1 FROM [T NF_S_Det] T1
WHERE T1.Cod_NF_S = T.Cod_NF_S
AND T1.Numero_NF < T.Numero_NF) AS Rank
FROM [T NF_S_Det] T
ORDER BY Cod_NF_S, Numero_NF

That should do it.

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


Hi Tom,

Sorry for not writing the way i want it to be.
So, you almost get the right point, the trouble is that, i wanna count
it, reseting every new Cod_NF_S
The SQL for this table is:

SELECT [T NF_S_Det].Cod_NF_S, [T NF_S_Det].Cod_NF_S_Det, [Order] AS Expr1
FROM [T NF_S_Det]
GROUP BY [T NF_S_Det].Cod_NF_S, [T NF_S_Det].Cod_NF_S_Det, [Order];

Cod_NF_S Numero_NF Rank
050040513120431 9 1
050040513120431 9 2
050040513120431 9 3
050040517145518 11 1
050040518182343 13 1
050040518181403 14 1
050040519110051 15 1
050040519152744 16 1
049040603093802 76 2
049040603093802 76 2
049040603093802 76 2


Tks,
Rodolfo Fontes


Tom Ellison said:
Dear Rodolfo:

As I understand your request, you want to add a column to this that
ranks the rows according to the value of Numer_NF. Using the data
provided, this would look as shown below:

Cod_NF_S Numero_NF Rank
050040513120431 9 1
050040513120431 9 1
050040513120431 9 1
050040517145518 11 4
050040518182343 13 5
050040518181403 14 6
050040519110051 15 7
050040519152744 16 8
050040520153608 17 9
050040521175037 20 10
050040525110706 22 11
050040525110553 23 12
050040525110325 24 13
049040603093802 76 14
049040603093802 76 14
049040603093802 76 14

The query would look something like this:

SELECT Cod_NF_S, Numero_NF,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Numero_NF < T.Numero_NF) AS Rank
FROM YourTable T
ORDER BY Numero_NF

You must change YourTable to be the actual name of your table (or
query).

Notice how this handles ties.

As a query, it will "reset" any time you run the query, using the most
recent data.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
Click to expand...
 
Back
Top