469,097 Members | 1,274 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

Simple AutoNumber query in Make-Table Queries

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
Jan 31 '07 #1
11 11622
NeoPa
32,159 Expert Mod 16PB
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.
Feb 1 '07 #2
NeoPa
32,159 Expert Mod 16PB
Having looked up the general syntax for a SELECT ... INTO (Make Table) query, it seems that this feature is not supported after all :( Sorry.
Feb 1 '07 #3
ADezii
8,800 Expert 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intRecNo As Integer
  2. Set MyDB = CurrentDb()
  3. Set MyRS = MyDB.OpenRecordset("qryEmployee", dbOpenDynaset)
  4.  
  5. Do While Not MyRS.EOF
  6.   intRecNo = intRecNo + 1
  7.     MyRS.Edit
  8.       MyRS![Counter] = intRecNo
  9.     MyRS.Update
  10.     MyRS.MoveNext
  11. Loop
  12.  
  13. 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.
Feb 1 '07 #4
NeoPa
32,159 Expert Mod 16PB
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.
Feb 1 '07 #5
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

Expand|Select|Wrap|Line Numbers
  1. SELECT (Select Count(*)
  2.         FROM [649)  BC - T/M months - Final Table] as Temp
  3.         WHERE [Temp].[DollarDays all ] < [649)  BC - T/M months - Final Table].[DollarDays all ])+1 AS RowNum,
  4.         [649)  BC - T/M months - Final Table].[Broker Group],
  5.         [649)  BC - T/M months - Final Table].[DollarDays all ]
  6. INTO [650)  BC - T/M months - Position]
  7. FROM [649)  BC - T/M months - Final Table]
  8. ORDER BY [649)  BC - T/M months - Final Table].[DollarDays all ] DESC;
in case anyone is interested.
Feb 1 '07 #6
ADezii
8,800 Expert 8TB
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.
Feb 1 '07 #7
NeoPa
32,159 Expert Mod 16PB
Using it is not so extreme. It's the idea of trying to take someone through it that would put me off.
Feb 1 '07 #8
NeoPa
32,159 Expert Mod 16PB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT (Select Count(*)
  2.         FROM [649)  BC - T/M months - Final Table] as Temp
  3.         WHERE [Temp].[DollarDays all ] < [649)  BC - T/M months - Final Table].[DollarDays all ])+1 AS RowNum,
  4.         [649)  BC - T/M months - Final Table].[Broker Group],
  5.         [649)  BC - T/M months - Final Table].[DollarDays all ]
  6. INTO [650)  BC - T/M months - Position]
  7. FROM [649)  BC - T/M months - Final Table]
  8. 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 :().
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 AS RowNum,[Broker Group],[DollarDays all ]
  2. INTO [650)  BC - T/M months - Position]
  3. FROM [649)  BC - T/M months - Final Table]
  4. 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).
Feb 1 '07 #9
NeoPa
32,159 Expert Mod 16PB
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.
Feb 1 '07 #10
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
Feb 5 '07 #11
NeoPa
32,159 Expert Mod 16PB
Well, we're pleased you got a result of some form at least.
Please come back if you have any other questions.
Feb 5 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Tom | last post: by
5 posts views Thread by ndn_24_7 | last post: by
4 posts views Thread by Jake Bee | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.