how to add a field in queries

[poporogue]

i have a query that get data from three tables in a specific order.but the outcome is not what i expected.so i want to add a field which is a number from 1 to X in the query and ordered by it. Just like below example.
cnt | XXX | XXXX | XXXX
----------------------------------------------
1 xxxx xxxxx xxxxx
2 xxxxxxx xxxxxx xxxxxxxx
3 xxxxxx xxxx xxxxx
4 xxxx xxx xxxx
5 xxx xxx xxxx

question
i don't know how to add a field in query that will auto increase the number when one record is created.
can someone give me some ideas about this problem?

thanks.

poporogue

 

[scriverb]

We can help you with numbering your rows from the query in a ordered sequence but the records are still going to be in whatever order you are selecting them prior to the sequencing. Why don't you post the queries SQL and let me take a look at it. Also identify the key fields in query that would have a unique value.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[cottontop]

Can't autonumber work in this instance?

 

[scriverb]

Yes, we can use the autonumber but I just need you to tell me that you have one in your table and what the name is. Also identify the name of your table and the other fields that you want displayed.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mpastore]

Hey Popor,

This is from a similiar post I answered earlier this week, it might help.

You can do this via a function call. Here's a sample query:

SELECT Table1.field1, Table1.field2, getAutoNumber([field1]) AS mynum
FROM Table1
ORDER BY getAutoNumber([field1]);


Note you have to pass one of your fields from your table as a parameter so that the function works, doesn't matter what field.

Now you need to create a function in a VBA module:


Function getAutoNumber(pu As Variant) As Long
Static lRet As Long

lRet = lRet + 1
getAutoNumber = lRet


End Function



If you are unfamiliar with VBA, modules, pick up a book. I can't spend too much time walking you through VBA.

The first time you run the function the first number will be 1 and will increment by one each time. In order to start from 1 again, just close database and re-open.

Mike Pastore

Hats off to (Roy) Harper

 

[scriverb]

Here is SQL that will give you a row number without the need for a function call. It uses the exising fields and your autonumber. Actually, your autonumber field is redundant because it will be the same as what we are creating here. It is better to use a unique field rather than the autonumber unless you are going to have additional WHERE clause entries to further select the records.
SELECT Count(*) AS Count, A.Field1 A.Field2, A.Field3
FROM tblYourTableName AS A, tblYourTableName AS B
WHERE (((A.[AutoFieldName]) <= B.[AutoFieldName]))
GROUP BY A.Field1, A.Field2, A.Field3
ORDER BY Count(*);

Post back with questions and I will help you understand this query.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[poporogue]

thx all~
i'm trying to understand the solutions.
Anyway i post may query here.

SELECT qust_bnk.*, q1.max_no, exam_paper_no.*
FROM qust_bnk, q1 INNER JOIN exam_paper_no
ON (q1.exam_no = exam_paper_no.exam_no)
AND (q1.paper_code = exam_paper_no.paper_code)
WHERE (((q1.[1])=CStr([qust_bnk].[auto_no])))
OR (((q1.[2])=CStr([qust_bnk].[auto_no])))
OR (((q1.[3])=CStr([qust_bnk].[auto_no])))
OR (((q1.[4])=CStr([qust_bnk].[auto_no])))
OR (((q1.[5])=CStr([qust_bnk].[auto_no])))
OR (((q1.[6])=CStr([qust_bnk].[auto_no])))
OR (((q1.[7])=CStr([qust_bnk].[auto_no])))
OR (((q1.[8])=CStr([qust_bnk].[auto_no])))
OR (((q1.[9])=CStr([qust_bnk].[auto_no])))
OR (((q1.[10])=CStr([qust_bnk].[auto_no])))

i know it's very messy, but i don't another way to make it tidy.

thx all again, you all are very helpful.

 

[poporogue]

oh i've forgotten to explain what's contained in the query.
qust_bnk, exam_paper_no are tables
q1 is a query

the questions(results) are needed in order which predefined in q1, they are in random ordered, so it's no use to order by auto_no

what ever the order in q1, the result is not in q1.1 q1.2 q1.3 q1.4.........order.

well, my explanation is not very clear since my english is not good enough.

thx
poporogue

 

[poporogue]

i have used Mike Pastore's method
and i found that it's no use for my query>_<
anyway, thanks


Also, i don't understand Bob Scriver's method. can u explain to me? thanks.

poporogue