Adding row numbers to a query 3

[slames]

I am trying to add a number to each row of my query I have looked at the FAQs here but can not seem to get it to work. I am using Access 2000

This is my query:

SELECT (SELECT count(*) from dailycatchup where dailycatchup.trID <= Query1.trid), trID AS rownum
FROM Query1 ORDER BY trID;

The name of the query I want to add numbers to is dailycatchup, the name of this query is Query1,

Can someone point out where I'm going wrong please?

Thanks in advance

Steph

 

[dhookom]

Try:
SELECT (SELECT count(*) from dailycatchup d where dailycatchup.trID <= d.trid) as RowNum, *
FROM dailycatchup ORDER BY trID;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

Give this a try:

SELECT (SELECT count(*) from dailycatchup as Temp where Temp.trID <= dailycatchup.trid) AS rownum, dailycatchup.* 
FROM dailycatchup ORDER BY trID;

I added a reference to all fields of the dailycatchup query so that the other columns show.

Post back with the results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Happy100]

Hello, this sounds like just the thing I am trying to do with Access 2002, but I have only just started using Access and do not know anything about these query?

Please could anyone guide me through this process of setting it up?

Kind regards

Happy

 

[scriverb]

Copy the code that I provided in my last posting. Now we want to paste this SQL code over the exisiting SQL code that you already have in Query1. Open Query1 in design mode. Click the far left button on the toolbar and select SQL. Your SQL code will be highlighted. Now select Paste from the Edit menu. The new SQL will now be pasted over your old SQL. Save the query and run it. You should now have your a list of records with a sequencing RowNum column.

Post back if you have more questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Happy100]

Hi Bob

Thanks for help me, this is the sql code I have in there already, Africa being the table and LinkNumber being the first column where I want the row numbers to show.

SELECT Africa.LinkNumber
FROM Africa;

With the code you have kindly provided, which parts would I change to suit my database?

Would it be this? -

SELECT (SELECT count(*) from Africa.LinkNumber as Temp where Temp.trID <= LinkNumber.trid) AS rownum, LinkNumber.*
FROM Africa ORDER BY trID;

I'm not sure if that would be correct?

Many thanks

Happy

 

[scriverb]

I have made the necessary adjustments. Give this SQL a try and let me know how this works out for you:

SELECT (SELECT count(*) from Africa as Temp where Temp.trID <= Africa.trID) AS LinkNumber, Africa.*
FROM Africa ORDER BY Africa.trID;

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Happy100]

Thanks Bob, I don't get an error code now, but sorry, I'm still a little confused! :-(

I was hoping the code would add numbers to the first column in every row, starting at 1 and working upto how ever many rows there were.

Have I got the wrong end of the stick with this???

My Access database is called linkdata.mdb and the query is called NumberQuery, Table in Database is Africa and the first column where I want the numbers is LinkNumber.

When I double click on the query and data entry box opens wanting Temp.trID value, when I click ok and second box opens with Africa.teID.

Am I not ruuning the script correctly?

Hope you can guide me through this?

Many thanks

Happy.

 

[scriverb]

Okay the field trID is a field you provided in your initial posting. I am assuming that this is the primary index field and is unique and a numeric value. Most likely an AutoNumber field. Please verify that this is the case. The name of the database has no affect of this query. The table name is Africa and yes that is used here. The new column is going to be called LinkNumber and it should be the first column in the resulting query recordset. The rest of the fields in Africa should follow as additional columns.

Do you have an AutoNumber field and what is its name? If not what field is your primary index. This field is unqiue and we need to use it to sort the records as well as determine the numbering process.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Happy100]

Hi again Bob,

Just so you know I was re-directed here from a another question in this forum -

http://www.tek-tips.com/viewthread.cfm?spid=700&newpid=700&sqid=877166

I do have an AutoNumber column, it's the first one called NumberLink, but I don't really need a unique number for each record, as from time to time records will get deleted, but I still want the numbers in the first column of every row to show 1,2,3,4 etc. the numbers have no connection to the data in the records!

Is there anyway I can allow you access to the database?

Many thanks

Happy.

 

[scriverb]

That is what I needed. We need to use this field to sort and number consecutively the rows in the recordset:

SELECT (SELECT count(*) from Africa as Temp where Temp.NumberLink <= Africa.NumberLink) AS [red]RowNumber[/red], Africa.*
FROM Africa ORDER BY Africa.NumberLink;

Now I used the name NumberLink even though previously you called it LinkNumber. I named this new column RowNumber so as not to get this all confused. You can name it as you wish in the query. Just change the red code to a name that you want.

Give this a try and I think you should be all set.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Happy100]

BING0!!!!

That one worked!
Though I'm having a problem getting the asp web page to show the right numbers, but I think that's another problem!

http://www.related.comestaywithus.com/related/africa.asp

Many many thanks for your help and your patience with me.

Cheers

Happy.

 

[scriverb]

Good luck with your project.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]