auto number field in query

[Leo_Polla_Psemata]

Hi
I don't know why is it so complicated to add an auto number field in a query.
Please see this example from Northwind , I would like to add a increment number (auto number) on each line and then an auto number on each product.
Please see examples

It is

I want to get

 

[plog]

As my farmer grandfather used to say: You can't piss in the same river twice. Meaing, its not a static entity, its a constant flow. Queries are like rivers, they aren't static. If the underlying data changes and a row gets added, or taken away, that query isn't going to be the same the next time you run it and your numbering system is screwed.

So even if adding an 'autonumber' to a query was possible it would be pointless. So, give up on that, and instead focus on what you hope to accomplish with that autonumber. What did you think adding an autonumber would allow you to do? Maybe there's another way.

 

[CJ_London]

autonumbers are a field type so you cannot add to a query as you want.

Instead you can create a counter which would be something like

dcount("*","[Product Sales Total by Date]","[Order Date]<=" & [Order Date])

To use this, you will need another query which will be like this

SELECT *, dcount("*","[Product Sales Total by Date]","[Order Date]<=" & [Order Date]) AS Counter 
FROM [Product Sales Total by Date]

 

[Leo_Polla_Psemata]

Well, the reason i am asking this is because i want to convert this query into xml and upload it. Autonumbering in reports is so easy, why not in queries. May be I should build a report and convert it to xml.

 

[plog]

Again, think past the whole autonumber concept. What does an autonumber allow you? Essentially its a meaningless number used only to uniquely identify records. Which is the exact reason it won't work on a query for the reasons I detailed before--you can't guarantee the same rows will always be produced in the same order from query run to query run.

CJ's method is the way to go to numerate records in a query for one run, but remember it's not valid for anything else than populating a number--you can't use it to link back anything to that query.

 

[CJ_London]

Autonumbering in reports is so easy

Can you demonstrate how you do this

 

[Leo_Polla_Psemata]

Can you demonstrate how you do this

In a report that outline data, i create a text box, countrol source =1 , running sum ,
see picture, you get a line number

 

[CJ_London]

OK - so not actually autonumbering but a counter but I guess it does what you want - the dcount suggestion I made does the same thing in a query