record counter

[Guest]

am particularly dim this morning..... have simple table with field that is a
Ref that varies:

Ref
1
1
1
2
2
3
3

and I need to add a count-by-ref using query :

Ref Count
1 1
1 2
1 3
2 1
2 2
3 1
5 1
5 2

it's not Sum, it's not Count.....

adding a counter table with no join results in total count table for each
ref....

just kind of dense on this at the moment....help!!

 

[strive4peace]

Hello,

in a general module, put the following code:

'----------------
Dim gNum As Long, gRef As Long

Function GetNum(pRef As Long) as long
If pRef <> Nz(gRef) Then
gNum = 1
gRef = pRef
Else
gNum = gNum + 1
End If
GetNum = gNum
End Function
'----------------

then, the grid of query would be like this:

field --> Ref

field --> Count: GetNum([ref])


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Guest]

thankyou for the effort but no, alas, I can't get this going because I
receive a message:

Undefined Function: 'GetNum' in expression

I did name the Module GetNum and also double checked that the Function
GetNum doesn't have a typo inside ....but the query doesn't recognize it so
am not able to use this .....
--
NTC

Hello,

in a general module, put the following code:

'----------------
Dim gNum As Long, gRef As Long

Function GetNum(pRef As Long) as long
If pRef <> Nz(gRef) Then
gNum = 1
gRef = pRef
Else
gNum = gNum + 1
End If
GetNum = gNum
End Function
'----------------

then, the grid of query would be like this:

field --> Ref

field --> Count: GetNum([ref])


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

am particularly dim this morning..... have simple table with field that is a
Ref that varies:

Ref
1
1
1
2
2
3
3

and I need to add a count-by-ref using query :

Ref Count
1 1
1 2
1 3
2 1
2 2
3 1
5 1
5 2

it's not Sum, it's not Count.....

adding a counter table with no join results in total count table for each
ref....

just kind of dense on this at the moment....help!!

 

[strive4peace]

perhaps you are putting the code in the wrong place... it
goes in a general module, not a class module...

from the database window (where you can choose Tables,
Queries, Forms, Reports, Pages, Macros, Modules), click on
Modules and then click the NEW button...

THIS is where you need to put the code

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

 

[Guest]

indeed - that is where it is.

I never knew one could 'call' a module from a query calculation - - and it
seems like a great idea - - but as of now it is not working.....maybe I will
experiment with some other very simple modules and see if I can get them
called in by a query - - because even outside this specific need - it would
be a useful approach....

from a different pure SQL user forum someone gave me a pure sql statement
that I can plug directly into the query - - and my rough test last night
showed it to work. for which I am grateful. But I'm better at VB than SQL -
so would still like to iron out your technique if I can......

although I never got to this point yet with your technique; what is not
clear to me in using a module called in by a query is that the module
references to values in the table need to be explicit i.e. with
TableName.FieldName and it would seem that the module could be using values
from any table/form/report.

just fyi - a very interesting new venue to explore for me.....thanks

 

[John Spencer]

Pardon me, but you cannot name the module and the function the same. Try
re-naming the module modGetNum. Modules and procedures (functions and subs)
apparently share the same name space. I say "apparently" since I am
guessing. But I do know that you cannot name a module the same as any
function.

 

[Guest]

hey thanks for that additional input - I modified the Module name to be
different than the Function and the error disappeared and everything ran ok.

the exact math calc I need isn't correct - - but I should be able to fine
tune...

thanks much to both as I never realized you could call a module into a query
calc...

 

[Guest]

every time I use the slider up/down of the query result grid....the values in
the the calculated column change !!! I never seen anything like it.....

 

[strive4peace]

that is because it is recalculating each time -- this
solution assumes that you won't do that...

If you want to see the number from the top of the list, you
can look in the record selector box in the bottom left
corner of the screen.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

 

[Guest]

hello once more,

I owe you the retraction of my prior statement that the math does not work
quite right - - it works correctly. It was just that I had moved the up/down
scroll bar and the results had changed - and I didn't realize it.

I modified the query to a MakeTable query and so the table is of course not
changing - and every thing works fine. So thanks much for this....

But I have never seen the phenomena of the query grid values re-calculating
due to movement of its scroller bar....can you enlighten me on this??

 

[strive4peace]

you're welcome

I have noticed recalculation of "Record Number" fields done
this way in queries -- a query is a dynamic set of data that
is queried/calculated as you render to the screen. Queries
do not store information, they get data from the tables

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com