Incrementing Numbers in Query

[rich.barry]

This query is the data source for a chart on a report.
First column of the query is the sorted data.
Second column should be = n / ( count + 1 )
where n is the row number and count is the total number of rows or values in the first column.
Any ideas how this is written as a calculated expression in a query?

Thanks

Richard

 

[Memnoch1207]

n = row number
count = total rows

2nd column:[row number / SUM[total rows] + 1]

 

[rich.barry]

Unfortunately its not quite that easy.
The query has to work out what the row number is and what the total number of rows is.
The only thing it has from the table is a column of sorted numbers. Column 2 must use built in functions to determine the rest

ie

Col1 Col2
12.4 =1/(6+1)
13.2 =2/(6+1)
13.9 =3/(6+1)
16.4 =4/(6+1)
19.1 =5/(6+1)
20.3 =6/(6+1)

Count=6

 

[Pat Hartman]

This is a multi-step process.
1. Create a MakeTable query that selects the data you need and orders it the way you want it to be numbered.
2. Create a DDL query that adds an autonumber column to the new table.
3. Create a query that counts the number of rows in the new table or retreives the Max autonumber.
4. Create a query to join the new table to the table created in step 3. Then divide the autonumber column by the value obtained in query3.

 

[rich.barry]

Thanks Pat

It makes one appreciate the simplicity of Excel for such a task!

Richard

 

[Pat Hartman]

Of course if you know how to write code, you can avoid the temporary table and DDL query.