Solved Sequential Number in Query

[jdlewin1]

Hi,

I have a query that sets up my table for using at various points, want I want to do is have an Item number field created by the query. Essentially I just want it to number the records from 1 through N, like an AutoNumber field in a table but i want it to always start at 1 for whatever is displayed by the query.

The code below shows the SQL of the query, the first part "No start at 1 then +1" AS [Item No]" is what I want to be the numbered field.

SELECT "No start at 1 then +1" AS [Item No], "LABMATS" AS [Project Code], "" AS [Project Task], "" AS [Expense Code], Order_Holding.Qty, Order_Holding.Item, Order_Holding.Description, Order_Holding.Price_£, Order_Holding.Total_Price_£
FROM Order_Holding;

Thanks,
Jon

 

[theDBguy]

Do you have an Autonumber primary key field in your table?

 

[Ranman256]

you cant really. autonumbering in a query has its problems.
1 solution is to add the query to a table with autonumber field.
but in order to get the table to begin with 1, you must have an empty table ready to copy to the target data table.
this "tResults MT" table never gets data so the autonumber always begins with 1.

docmd.CopyObject ,"tResults",acTable ,"tResults MT"
docmd.openquery "qaAddResults"

 

[Isaac]

RowNumber (lebans.com)

 

[bastanu]

Or use a subquery like in this example:

How to show row number in Access query like ROW_NUMBER in SQL

I have a table in Microsoft Access, and I want to show row number in a column using a select query in Access just like using ROW_NUMBER() function in SQL Server. In SQL Server, I can using this qu...

stackoverflow.com

Cheers,

 

[gemma-the-husky]

I wouldn't bother, personally. It's not the way a dbs works, which is why it's not easy to do. Your view shows you a collection of data, and you can see the number of items you have. The actual numbering of those items is not really relevant.

 

[jdlewin1]

I wouldn't bother, personally. It's not the way a dbs works, which is why it's not easy to do. Your view shows you a collection of data, and you can see the number of items you have. The actual numbering of those items is not really relevant.

Hi, it is relevant as I am trying to create a report which matches in with the companies current system and that requires each record to be numbered as I have described.

I would have thought that having the records number 1, 2, 3, 4 , 5 etc would be a pretty straight forward thing to achieve. There's probably a simpler and better way around it hence why I am here asking the question and for help on it.

I can get a query to give me the numbers so 1, 2, 3, 4 etc which totals the number of records I have. I really now just need to be able to add these to the Item No field in the Order_Holding table and bingo job done!

I have tried an update query but that doesn't seem to work.

 

[theDBguy]

it is relevant as I am trying to create a report...

This is very easy to do in a report. Just add an unbound Textbox in the Details section and put =1 in its Control Source. Then, in the Running Sum property, select either Overall or Over Group (whichever you prefer). Cheers!

 

[jdlewin1]

Or use a subquery like in this example:

How to show row number in Access query like ROW_NUMBER in SQL

I have a table in Microsoft Access, and I want to show row number in a column using a select query in Access just like using ROW_NUMBER() function in SQL Server. In SQL Server, I can using this qu...

stackoverflow.com

Cheers,

Spot on that, did the job perfectly.

Thanks you very much!!!

 

[theDBguy]

Spot on that, did the job perfectly.

Thanks you very much!!!

Glad to hear you got it sorted out. That's why I asked if you had an Autonumber PK field. I was going in that direction. Because, otherwise, that approach won't work without it. Good luck with your project.

 

[gemma-the-husky]

@jdlewin1

The thing was, you said could you have numbers in a query, which is hard, as a query isn't sequential. You can allocate numbers, but then if you change the sort order the numbers won't be in sequence.

A report is different, because a report is a sequential exercise.

You can certainly add your own numbers to the records, either manually, or with an autonumber. Note though that an autonumber will not guarantee an intact sequence.

If you extract a query of only some records, then obviously you will see your reference, but as likely or not it won't run in numerical order. you might select an extract in client order, date order, and so on - each version will give you a different sort order.