Calculated query problem

[sad]

Hi,
I am trying to create a calculated field in a query. The field has the
following formula:

freq: (1/numrecords) + valueofpreviousrecord

where numrecords is the number of records returned by the query. I
have already found this using the Dcount function. The problem is in
finding
valueofpreviousrecord.

valueofpreviousrecord is the previous record value displayed for this
field. i.e. freq[x-1] if this was an array.
I know that you can get the previous record field using subquery, but
not sure exactly how and it looks too complex.

A simpler way I can do this is to generate an autonumber so I can know
what the current record number is. Then I can revise my expression to

Expr1: (1/numrecords) * (numrecords-(numrecords-x)),

where x is the current record number

But, I can't find any way to get the current record number displayed
in a query.

I have also tried to create a visual basic function to do this. My
code is

Function freq(numrecords AS Integer)

for x=1 to numrecords
freq = (1/numrecords)*(numrecords-(numrecords-x))
Next
End Function

The problem is that this function returns the final value which is
always 1. I need the intermediate values to be displayed for each
record.
For example if RecordNum=4 I need


RecordNum Freq
1 0.4
2 0.8
3 1.2
4 1.6

But RecordNum varies each time the query is run.
So I think I need to pass the function the current record value, but
not sure how to find it.

Does anyone have a solution or any ideas. Thank you.

 

[Allen Browne]

Answered in m.p.a.

 

[sad]

Answered in m.p.a.

Thank you, but what is M.P.A.?

 

[sad]

Answered in m.p.a.

OK, I get it. You meant microsoft Public Access. Thank you for the
article, it was helpful, but the problem is that I am using a query
not a form, and for my query there is no ID field of a number data
type so I can't use DLookup..how do I generate an autonumber for a
select query?
Thanks.