Autonumber Groupby Query

[jessedh]

Hello,

This is difficult for me to wrap my head around and I have been struggling with it for the better part of the day.

I have a group by query that is in the following format:

CatID | Product | SortCriteria1 | SortCriteria2 | [Needed Field]
A 1
A 2
B 3
B 1

and I need to create a field that will autoincrement by 1 for each Product in a CatID and then reset to 1 and do it all over again.

I honestly am at a loss on how to do this and any help would be appreciate

thanks!
jesse

 

[jessedh]

Found this and it works....

Thanks for all the input. I know your code will work ~ because I saw the results ~ even if it was only for a few seconds!!!

Could you take one last look at what I pasted into my module? I compacted and repaired the database, but still get the same error message.

Option Compare Database

Public RankVar As Integer
Public My_Var As Double
Public strSrore_No As String

Function fnRank(MyVar As Double, strSroreNo As String) As Integer

If strSrore_No <> strSroreNo Then
RankVar = 1
ElseIf MyVar > My_Var Then
RankVar = 1
Else
RankVar = RankVar + 1
End If

strSrore_No = strSroreNo
My_Var = MyVar
fnRank = RankVar

End Function

Then here's the SQL I have in my query:

SELECT Query1.store_no, Query1.TAHHS, fnRank([TAHHS],[store_no]) AS Rank
FROM Query1;

Thank you!!!

 

[lespaul]

what is the error message you receive?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PJStephenson]

You've got public and local variables with the same name, it could get confusing.

[&eta;][&beta;][&pi;]

 

[jessedh]

I copied the syntax verbatim from the post that I found it in. I didn't realize that I copied an error as well. The VBA works fine but is slow. Does anyone know how to do this in pure SQL ?

 

[PHV]

something like this ?
SELECT A.store_no, A.TAHHS, Count(*) AS Rank
FROM Query1 As A INNER JOIN Query1 As B
ON A.store_no = B.store_no AND A.TAHHS >= B.TAHHS
GROUP BY A.store_no, A.TAHHS

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886