Increment Record Count Field

[ChrisBelzona]

I am trying to create a query that has an incremented record count as a field ie results as follows
ID - Desc - Count
------------------
1 - Test1 - 1
5 - Test2 - 2
7 - Test3 - 3
9 - Test4 - 4

etc..

I have followed the FAQ entry on incrementing record counts in queries but that only seems to increment each time I run the query, and not for each record in the query.

Any help would be fantastic

Cheers

Chris

 

[tlbroadbent]

When you use the technique in the FAQ you need to reset the variable to zero after running the query.

If your table is small and ID is a unique column you can use a query like the following.

SELECT Tbl.ID, Tbl.Desc, (Select count(ID) From Tbl Where Id<=Tbl.ID) AS RowCnt
FROM Tbl
Order By ID;

This method doesn't require a variable but has several limitations. With large tables, this method is substantially slower. It will not work without a unique column. Note that the query also requires ordering of the data by the unique column. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ChrisBelzona]

Terry

Thanks for your quick response, however I may not have explained what I'm after correctly or I miss interpreted your reply wrongly.

I've tried your code and that gives me a total of the records in the query, if I can explain the situation in a bit more detail

I'm using the a switchboard menu system and my table that gives users access has a menu Id and a button ID. A menu may have 10 buttons but some users only have access to 5 if the do not run numerically ie. 1,2,3,4,5, then the screen looks messy ie button, miss a button, button etc..

I'm trying to write a query that will re-number the buttons so in the query you would have

Menu Id - Button No - New Button No
1 - 1 - 1
1 - 2 - 2
1 - 4 - 3
1 - 7 - 4

Does this make more sence?

Cheers

Chris

 

[tlbroadbent]

First, I assumed that when you mentioned a FAQ you meant faq701-896. Is that correct? If so, did you implement code to reset the variable to zero? That should be quite easy in your application.

To implement my suggestion, you will need to take into account multiple selection criteria.

SELECT Tbl.MenuID, Tbl.ButtonNo, (Select count(ID) From Tbl Where MenuId=Tbl.MenuID And ButtonNo<=Tbl.ButtonNo And <criteria to identify user's buttons>) AS RowCnt
FROM Tbl
WHERE <criteria to identify user's buttons>
Order By MenuID, ButtonNo; Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ChrisBelzona]

Terry

Thanks again, but I'm still getting the total number of records in the query as my field value ie for 3 records the RowCnt field returns 3 as its value on all rows instead of 1,2,3 etc.

Any further ideas?

The FAQ would seem to be the ideal solution and I was expecting that to work but it does not seem to increment the returning value until the query is run again and I need it to increment for each record in the query. I understand how to reset the counter. (could it be the system is running to fast for the counter to increment).

Thanks

Chris

 

[tlbroadbent]

Pleae post your query so I can analyze it.

Thanks, Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ChrisBelzona]

Terry

Here is the sql statement
SELECT tbl.intFormNo, tbl.intButtonID, 0 AS intNewIncrementVal INTO tbl
FROM tbl
WHERE (((tbl.intFormNo)=1));
intformNo is the menu ID if you add the following records to the table
1 - 1
1 - 2
1 - 3
1 - 7
1 - 9

On a normal switchboard screen the first three button would be Ok but then it would miss several before hitting.

I am trying in the intNewIncrementVal field to create a incremented number ie. 1,2,3,4,5 for the above records to on the switchboard screen the first 5 buttons are shown.

hope this helps

Chris

 

[tlbroadbent]

This query statement looks nothing like what I've posted. It doesn't use a function to increment the row count, either. Is this the original query? I'd like to see the query you are running that is returning the total record count (3) rather than 1,2,3 that you desire.

If you want to modify this query to work, do the following.

SELECT tbl.intFormNo, tbl.intButtonID, (Select count(*) From tbl1 where intFormNo = tbl.intFormNo And intButtonID <= tbl.intButtonID) AS intNewIncrementVal
INTO tbl2
FROM tbl1
WHERE (((tbl.intFormNo)=1));

Also, I hope the tbl listed in the INTO clause and tbl in the FROM clause are actually 2 different tables. Are you actually making a new table every time the user opens the switchboard? It shouldn't be necessary to run a make table query to get the info you need to name the buttons. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ChrisBelzona]

Terry

My apologies I must have used a query I was working on to quickly write the SQL and did not relise it was a Make Table, I have amended this to a normal select query now, sorry once again.

here it is

SELECT tbl.ID, tbl.intFormNo, tbl.intButtonID, (Select count(ID) From Tbl Where intFormNo=intFormNo And intButtonID<=intButtonID) AS test
FROM tbl;

the Id is made up of the Form Number and button number (I will amend this later for multiple entries once I fiqure this out.

Here also is the other method using the module i have copied the module also.

SELECT tbl.ID, tbl.intFormNo, tbl.intButtonID, IncrementValues() AS test
FROM tbl
WHERE (((tbl.intFormNo)=1));

Module

Public IncrementVariable As Integer

Function IncrementValues() As Integer
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable

End Function

hope this does not confuse to much

Thanks Again

Chris