Simple AutoNumber query in Make-Table Queries

[fyshfysh]

Hi all,

A quick question about a function which surely should be possible in a Make-Table Query in Access. I wonder if it's possible to very simply have a field that numbers the rows in the resultant table from 1 to row n, no matter what the data in that table is.

In my example, I have a list of insurance brokers and a list of amounts (called DollarDaysGBP) as the result fields of my query. I want another field counting 1, 2, 3 etc. No matter what fields I drag into my query, or how I sort them, I want the Autonumber field to always be 1, 2, 3 etc.

Below is the source/SQL code of my query. I have found one bit of code online that works, but needs to be linked to another field to get an Autonumber list. I'd like my Autonumber field to be independent, if possible.

I'm not a proficient user of Access by any means but this seems something that a lot of people might need, and searching extensively the Help menus and this forum has yielded no results. Any help would be much appreciated.

Thanks
Ian

[NeoPa]

You omitted the SQL for your query.
I don't know the answer conceptually, but I'm interested in the question. I'll make a mental note to look out for something. It should be available I would think.

[NeoPa]

Having looked up the general syntax for a SELECT ... INTO (Make Table) query, it seems that this feature is not supported after all :( Sorry.

[ADezii]

Having looked up the general syntax for a SELECT ... INTO (Make Table) query, it seems that this feature is not supported after all :( Sorry.

NeoPa:
Here is a little Off-The-Wall technique that I always use whenever I want Query results to be numbered sequentially starting from 1 regardless of any Sort Order on any Field or Fields, number of Fields, etc. It is a little radical but there seems to be no other way to achieve the same result. Please look at it and let me know what you think. This code must be run prior to executing the Query and it also involves adding a [Counter] Field to the underlying Table then executing the Query with this ([Counter]) as the very 1st Column. It creates a Recordset based on the very Query that you want to sequentially number and writes the incremental values to the [Counter] Field starting at position 1. I'm sure that it can be improved - I just know that it works.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intRecNo As Integer
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("qryEmployee", dbOpenDynaset)
 
Do While Not MyRS.EOF
  intRecNo = intRecNo + 1
    MyRS.Edit
      MyRS![Counter] = intRecNo
    MyRS.Update
    MyRS.MoveNext
Loop
 
MyRS.Close

No matter what Criteria you specify for qryEmployee, and no matter how many Records are returned, they will always be numbered 1 to Recordset.RecordCount. If you think that this is not very useful, feel free to Delete this Post.

[NeoPa]

It's not something I'd recommend except in extreme circumstances. It's certainly worth having as a post, illustrating that you can do things using VBA which are not natively supported by the underlying SQL.
The reason I wouldn't recommend it is not because it won't work, but taking someone through the implementation can be difficult at times.

[fyshfysh]

Thanks gentlemen for your inputs.

ADezii's solution looks to be exactly the kind of thing I want, and NeoPa's reasoning is right - yes, I've got no idea how to perform that solution! Way above my head I'm afraid.

I'm surprised the such a thing isn't supported within Access as it seems to be something that a lot of people might want. I'll hash together some other solution - if it's not exact it doesn't matter (it's only for the directors of my company - no-one will question it!).

Thanks again for your efforts.

PS stupid, forgetting the SQL code, it was

SELECT (Select Count(*)
        FROM [649)  BC - T/M months - Final Table] as Temp
        WHERE [Temp].[DollarDays all £] < [649)  BC - T/M months - Final Table].[DollarDays all £])+1 AS RowNum,
        [649)  BC - T/M months - Final Table].[Broker Group],
        [649)  BC - T/M months - Final Table].[DollarDays all £]
INTO [650)  BC - T/M months - Position]
FROM [649)  BC - T/M months - Final Table]
ORDER BY [649)  BC - T/M months - Final Table].[DollarDays all £] DESC;

in case anyone is interested.

[ADezii]

It's not something I'd recommend except in extreme circumstances. It's certainly worth having as a post, illustrating that you can do things using VBA which are not natively supported by the underlying SQL.
The reason I wouldn't recommend it is not because it won't work, but taking someone through the implementation can be difficult at times.

I did warn you from the very beginning that I was a little extreme at times.

[NeoPa]

Using it is not so extreme. It's the idea of trying to take someone through it that would put me off.

[NeoPa]

Thanks gentlemen for your inputs.

ADezii's solution looks to be exactly the kind of thing I want, and NeoPa's reasoning is right - yes, I've got no idea how to perform that solution! Way above my head I'm afraid.

I'm surprised the such a thing isn't supported within Access as it seems to be something that a lot of people might want. I'll hash together some other solution - if it's not exact it doesn't matter (it's only for the directors of my company - no-one will question it!).

Thanks again for your efforts.

PS stupid, forgetting the SQL code, it was

SELECT (Select Count(*)
        FROM [649)  BC - T/M months - Final Table] as Temp
        WHERE [Temp].[DollarDays all £] < [649)  BC - T/M months - Final Table].[DollarDays all £])+1 AS RowNum,
        [649)  BC - T/M months - Final Table].[Broker Group],
        [649)  BC - T/M months - Final Table].[DollarDays all £]
INTO [650)  BC - T/M months - Position]
FROM [649)  BC - T/M months - Final Table]
ORDER BY [649)  BC - T/M months - Final Table].[DollarDays all £] DESC;

in case anyone is interested.

I've looked at your SQL and resolved it down to its basic logic. It won't work I'm afraid :(
I'll post an equivalent which may illustrate better whats going on (Your subquery will always resolve to 0 :().

SELECT 1 AS RowNum,[Broker Group],[DollarDays all £]
INTO [650)  BC - T/M months - Position]
FROM [649)  BC - T/M months - Final Table]
ORDER BY [DollarDays all £] DESC;

I don't think the sort of thing you're after can be done in Jet SQL without recourse to code (See ADezii's post #4).

[NeoPa]

I don't know if you're aware, but you can number items in a report by setting a field to 1 but setting the 'Running Sum' property to TRUE.

[fyshfysh]

I don't know if you're aware, but you can number items in a report by setting a field to 1 but setting the 'Running Sum' property to TRUE.

That's a good tip, I didn't know you could do that, but I needed my numbers in a table because I wanted to do more operations on them afterwards.

The SQL I've posted seems to work for what I need, it's not an ideal solution but I think it's the best I'm going to get.

Thanks everyone for your help!

Ian

[NeoPa]

Well, we're pleased you got a result of some form at least.
Please come back if you have any other questions.