0

In MS Access I need to print some labels with a unique increasing serial number on them (1, 2, 3...) This should be fairly simple, but I can't figure out a query which will generate a list of numbers given a start and end range. Anyone have an idea?

-- Geoff

Geoff Schultz
  • 141
  • 1
  • 10
  • Access and SQL Server are very different. SQL 2005 and SQL 2012 are very different! Do you need the increasing serial number as a return value in a query or in the table itself? – Eli Gassert Sep 17 '13 at 13:47
  • possible duplicate of [Returning row number on MS Access](http://stackoverflow.com/questions/1701243/returning-row-number-on-ms-access) – Mike Perrenoud Sep 17 '13 at 13:47
  • If it is an auto-incrementing serial number, Can't you just use `Select SerialNumber FROM Table1 WHERE SerialNumber <= 23 AND SerialNumber >= 8` ? – Amber Sep 17 '13 at 13:48
  • AS I said in the "answer" below, I don't believe that I need a table. I simply need a query which returns values between [start] and [end]. – Geoff Schultz Sep 17 '13 at 13:57
  • If you don't want the serial numbers to be returned to you in consecutive columns, as is the standard result of a sql query, in what format do you want your result? Or do you not have a table to query, you just want all numbers between certain values? – Amber Sep 17 '13 at 14:03

1 Answers1

2

Access has no CTE or something similar. I think you cannot do this without a table. Create a table with 10 rows. Values 0 to 9

Use this query

SELECT [T1].[id]+10*[T10].[Id]+100*[T100].[Id] AS IncrementValue
FROM [Table] AS T1, [Table] AS T10, [Table] AS T100
WHERE ((([T1].[id]+10*[T10].[Id]+100*[T100].[Id])>=8 
And ([T1].[id]+10*[T10].[Id]+100*[T100].[Id])<=90))
ORDER BY [T1].[id]+10*[T10].[Id]+100*[T100].[Id];

If you need higher numbers, increase amount of joins.

Wietze314
  • 5,942
  • 2
  • 21
  • 40
  • I'll just create some VB code to create a table populated with values. Strange that I can't do this via a query. – Geoff Schultz Sep 17 '13 at 23:48
  • I tried to create the Table as a query (SELECT 1 AS Id UNION SELECT 2 AS Id). But Access will not accept a query without a table. I do think my solution is pretty fast, and maybe faster than VB code. Good luck with your project! – Wietze314 Sep 18 '13 at 07:23
  • 1
    @GeoffSchultz bucket number and letter tables are usually recommended by practitioners as useful for generating a wide variety of data. – Yawar Sep 19 '13 at 01:13