Add sequential number to a query

  • Thread starter Thread starter Ivor Williams
  • Start date Start date
I

Ivor Williams

I've a query which gets its data from two tables. I would like to add a
column to the query which will generate an "autonumber" which doesn't come
from either table. Something like 'Number:Autonumber'. Is this possible?

Ivor
 
Ivor Williams said:
I've a query which gets its data from two tables. I would like to add a
column to the query which will generate an "autonumber" which doesn't come
from either table. Something like 'Number:Autonumber'. Is this possible?

Ivor
Click to expand...

Ivor Williams,

Tables:

CREATE TABLE ExamAscKeyOne
(ExamAscKeyOneID AUTOINCREMENT
,TestValue TEXT(1)
,CONSTRAINT pk_ExamAscKeyOne PRIMARY KEY (ExamAscKeyOneID)
)

CREATE TABLE ExamAscKeyTwo
(ExamAscKeyTwoID AUTOINCREMENT
,TestValue TEXT(1)
,CONSTRAINT pk_ExamAscKeyTwo PRIMARY KEY (ExamAscKeyTwoID)
)

Sample Data:

ExamAscKeyOne
1, a
2, b
3, c

ExamAscKeyTwo
1, d
2, e
3, a

Query One:

SELECT (SELECT COUNT(E03.TestValue)
FROM (SELECT E001.TestValue
FROM ExamAscKeyOne AS E001
UNION
SELECT E002.TestValue
FROM ExamAscKeyTwo AS E002) AS E03
WHERE E03.TestValue <= E1.TestValue)
AS NewAscCol
,E1.TestValue
FROM (SELECT E01.TestValue
FROM ExamAscKeyOne AS E01
UNION
SELECT E02.TestValue
FROM ExamAscKeyTwo AS E02) AS E1

Results:

1, a
2, b
3, c
4, d
5, e


Sincerely,

Chris O.
 
Chris...
It appears that your solution may be what I'm looking for, but I don't
understand what parts of it need to go where.
Can you please explain in more detail, I'm not too bright you know.

Ivor
 
Ivor Williams said:
Chris...
It appears that your solution may be what I'm looking for, but I don't
understand what parts of it need to go where.
Can you please explain in more detail, I'm not too bright you know.

Ivor
Click to expand...

Ivor Williams,

It depends on what your table structures are.

You need to adapt the SQL example given to your situation by
changing the example table and column names in the query to your
table and column names.

I do not know what your table and column names are, so I cannot tell
you what that will be.

Once all the appropriate name changes have been made, open a new
query in Design View, change to SQL View (go to the menus, View >
SQL View), and copy and paste the adapted SQL code into the SQL
window. Then execute the query to see if what I have posted will
actually work for you (it might not).

Note: UNION queries, I believe, cannot be represented in Design
View, so don't try and switch back to Design View in this particular
case.


My example:

SELECT (SELECT COUNT(E03.TestValue)
FROM (SELECT E001.TestValue
FROM ExamAscKeyOne AS E001
UNION
SELECT E002.TestValue
FROM ExamAscKeyTwo AS E002) AS E03
WHERE E03.TestValue <= E1.TestValue)
AS NewAscCol
,E1.TestValue
FROM (SELECT E01.TestValue
FROM ExamAscKeyOne AS E01
UNION
SELECT E02.TestValue
FROM ExamAscKeyTwo AS E02) AS E1

ExamAscKeyOne > Gets changed to your first table name.

ExamAscKeyTwo > Gets changed to your second table name.

"AS NewAscCol" > Gets changed to whatever you want the new ascending
sequential value column to be named.

TestValue > Gets changed to whatever is the column name that you
want the new ascending sequential value to be "based on". (Note,
the UNION subqueries are set to eliminate duplicates.)

You can leave the table aliases alone, and it will still work (but I
would probably change the lettering to more closely match your table
names; "E" is for "Exam" in the table names, in this case).


Sincerely,

Chris O.
 
or you can add an autonumber field to the results of a make-table query
if you want
Click to expand...

dbahooker,

May I ask how you would cause MS Access to use an Autonumber data
type for a calculated column in a SELECT...INTO make-table query?

Please post the DDL, sample data, and SQL.


Sincerely,

Chris O>
 
here is a function for doing it
http://www.mvps.org/access/tables/tbl0016.htm


yeah, i googled Access DDL autonumber; i swear i can do it and it's a
function that was added in access 2000..

I would try th ansi/sql server verbage
ALTER TABLE ADD COLUMN MYAUTONUMBER AUTONUMBER
ALTER TABLE ADD COLUMN MYAUTONUMBER IDENTITY

or something along those lines

i've definitely seen some people that say 'you cant do this'

and i personally think that MDB is a piece of shit and nobody should be
using it in the real world for anything.. i mean-- SQL Server -- aka
MSDE-- is free with Access license; i mean-- it's a no-brainer to use
Access Data Projects for everything you do
 
Back
Top