Auto Number in Access Query

[nikunj_pg]

hi All

I need to have an auto number in an access query, this is not the auto number already existing in the table. I need a separate auto number field in the query which begins with 1 and is in increasing order.
Any help will be much appreciated

 

[MStef]

Look at "DemoqryANumA2000.mdb" (attachment).
You can do it in Query via Module (function), or via Report (without query, without module).
Look at table, query, module, Form1, (if you want via query, in any case I suggest to make a report).
Open Form1 and try.

 

[NCLWMC]

This helped is a lot for a similar question. However, how do you keep from the auto number from changes (Increasing each time)
I have to leave access and return for the number to start again from 1.
The number will keep adding if i view the report, query, or do anything to it.
Your help is appreciated!

 

[MStef]

I don't understand you well. Be more precise.

 

[NCLWMC]

This helped is a lot for a similar question. However, how do you keep from the auto number from changes (Increasing each time)
I have to leave access and return for the number to start again from 1.
The number will keep adding if i view the report, query, or do anything to it.
Your help is appreciated!

OK, because I guess this isnt clear.

The database that was suggested in an earlier post. Showed us how to code the query to generate an auto number.
Basicly my co worker needs to generate a table of data that shows questions 1-100, thier averages, and the number that average falls under.
For example:
Question 1: Average 4.68 (Auto Number/Rank:2)
Question 2: Average 3.25 (Auto Number/Rank:3)
Question 3: Average 4.75 (Auto Number/Rank:1)

I am thinking I do not want an auto number. But he needs to show what that average score ranked out of 100.

The example that was given in the earlier post makes the number produce an auto number. However, If the refresh is clicked or run the report on the query. The auto number starts increasing. I have to close access all together for the number to start from 1 again. Please see the database the previous person posted. You will understand what i mean. refresh or run the report even close it and reopen. Watch the number increase.

SO I think we do not want auto number but something else. But any suggestions on how to create a function to rank the average out of 100 would be great! The data will change when more surveys come in. So just doing the simple out put to a table then report it is not what we want.

I hope this is more accurate.
Thank you

 

[MStef]

The Autonumber starts increasing because you Run a Query.
Open Form and click on the button.

 

[NCLWMC]

I do understand it is increasing because of the query. What do I need to do to get the ranking of the AvgScore out of total qty of questions? Is a Dcount? if so I dont know how to do that. It keeps coming back wrong.

I need to produce a report that shows:

Question1: AvgScore: 2.37 RanK: 4
Question2: AvgScore: 4.38 RanK: 2
Question3: AvgScore: 3.29 RanK: 3
Question4: AvgScore: 4.99 RanK: 1
(continueing till all questions are listed)

The query is creating my averages I just need to figure out how show the weight/rank of the score.

 

[NCLWMC]

Here is the quote from a website, I cant post the link yet.....
How can I generate a sequence of numbers (like record numbers) in a query.
In a module:
[FONT=Arial,Courier New,Times New Roman](declarations)Option ExplicitDim mlngCounter As LongFunction ResetCounter() mlngCounter = 0End FunctionFunction GetNextCounter(pvar As Variant) As Long mlngCounter = mlngCounter + 1 GetNextCounter = mlngCounterEnd Function[/FONT]
In the Query:
Select [feilds...], ResetCounter(), GetNextCounter([a valid column]) From table....

How this works:
ResetCounter() takes no parameters so Access (being as efficient as ever only calls the function once before returning any rows as it thinks the return value will never change, hence the counter only gets reset once.
GetNextCounter() must be sent a valid column as a parameter, Access will realise that it must call this function for every row as each value sent can be different, so it increases the counter on each row.

Please note: This won't work very well for queries & forms where you intend to update data or scroll up and down as the calculated fields gets calculated again and the sequence number will keep going up and up.


This works but how do I keep the number from increasing?

 

[MStef]

O.K. I think I understand what you want.
Look at "DemoAvgScoreRankA2000.mdb" (attachment, zip).
Look at "Table1", "Query1", "Form1", "Module1".
Open form and try.

 

[NCLWMC]

O.K. I think I understand what you want.
Look at "DemoAvgScoreRankA2000.mdb" (attachment, zip).
Look at "Table1", "Query1", "Form1", "Module1".
Open form and try.

Thank you for the effort! I had gotten to that point. Where I get confused is how to keep the number from increasing.
If you open the form/query/report and scroll down or click anywhere the number will start to increase.

We will just make sure the report is ran immediatly after the database is opened.

If anyone does know how to stop the numbers from increasing, we sure would like to know for future reference.

Thanks again

 

[MStef]

Hello NCLWMC!
Look at my first Demo. There is a Form1. In Form1 there are 2 command butons.
On the first button "Run Query" the "AutoNum" increase if you click. There are
2 possibilities to avoid this.
1) Don't click.
2) Make a "Make table query", (and bound a report on this new table).
Seccond command button "Preview Report" is bound on the table "dlb_tbl",
which number increase here ???

 

[apr pillai]

Have a look at the following Link:

Autonumbering in Query Column

You must sort the Average Value column in Descending Order to bring the highest value on top and to rank it as one. The parameter value passed to the function must be unique. Repeated use of the query will not increase the autonumber value, like the side effect that you have reported in earlier examples.

The QrySeq() function was created for autonumbering records after applying filter in the Query. Check whether it is useful to you or not.

 

[NCLWMC]

Have a look at the following Link:

Autonumbering in Query Column

You must sort the Average Value column in Descending Order to bring the highest value on top and to rank it as one. The parameter value passed to the function must be unique. Repeated use of the query will not increase the autonumber value, like the side effect that you have reported in earlier examples.

The QrySeq() function was created for autonumbering records after applying filter in the Query. Check whether it is useful to you or not.

I cant thank you enough! Autonumbering in Query Column is the solution I was looking for!
Worked perfectly!

 

[mahwilson]

I have a few queries which require to repeat the same command but I need the "autoNum" to begin from 1
How do I reset the "AutoNum" in each query without clicking the compact & repair database.?:banghead:

 

[dividebyzero]

I just put a 2nd ResetCounter function in the module he originally posted and refreshing it 100 times keeps it starting at 1. So, resets counter, populates row numbers, resets again. Hope this helps!

Module:
Option Explicit
Dim mlngCounter As Long

Function ResetCounter()
mlngCounter = 0
End Function

Function GetNextCounter(pvar As Variant) As Long
mlngCounter = mlngCounter + 1
GetNextCounter = mlngCounter
End Function

Function ResetCounter2()
mlngCounter = 0
End Function

Query String:
SELECT tbl_Below_Blanket_SIF_JEs.Customer, ResetCounter(), GetNextCounter([Customer]) AS [Inv#toAdd], ResetCounter2()
FROM tbl_Below_Blanket_SIF_JEs
WHERE (((tbl_Below_Blanket_SIF_JEs.[Date Uploaded to QB]) Is Null))
GROUP BY tbl_Below_Blanket_SIF_JEs.Customer;

 

[JMichaelM]

How do you get the number to reset back to 0 after you run the query a 2nd time?

 

[JMichaelM]

How do I start the numbering at 1 and not 0?

Option Compare Database

' Declaration section
Public AutoNumber As Long

Function SeqAutoNumber(i As Variant) As Variant

SeqAutoNumber = AutoNumber
AutoNumber = AutoNumber + 1

End Function