Query that autonumber some columns

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

Hello. I'm creating a database for a website and I need a query that
Identifies how many records and that gives them numbers.

In my site I have 5 columns. There's a filters that loads for each column
their own data.

so if in column 1 it only loads records that has the 1 number in fields
ColumnID. And the same for the rest of the columns.

I was thinking if there's any way to autonumber this. Imagine. The query
result was 11 records.

This would give two records for each column but for the first column should
be 3 records.

I need the query identify how many records and then to returns the values.
Somethign like this:

Record ColumnID
Record1 1
Record2 2
Record3 3
Record4 4
Record5 5
Record6 1
Record7 2
Record8 3
Record9 4
Record10 5
Record11 1


Is it possible?

Regards,
Marco
 
This is only possible if you have a field that determines the sort order.
Once this has been determined in you use the field to determine a ranking
value which can be used with the MOD to provide the values 1-5.
 
If you want to create a columnID in the Orders table of the Northwind sample
mdb, the SQL would be:
SELECT (Select Count(*) FROM Orders O WHERE O.OrderID<Orders.OrderID) Mod
5+1 AS ColumnID, Orders.*
FROM Orders
ORDER BY Orders.OrderID;
 
Back
Top