Calculating Expression in Query

[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]

See:
Referring to a Field in the Previous or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

 

[sad]

Thank you for the article, it was helpful, but the problem is that I
am using a query not a form(so I can't use the code method), and for
my query there is no ID field of a number data type so I can't use
DLookup method..how do I generate an autonumber (ID field) for a
select query? I need to use a query since I will be graphing this data
later and maybe using it again in other queries, so a form is not
suitable.
Thank you.

See:
Referring to a Field in the Previous or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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]

You do realize that you can make a form in Datasheet view so it looks like a
query?

In any case, the DLookup() will work in a query.

The subquery will be much more efficient, but apparently you don't want to
use that.

And you still have the option of creating a temporary table from your query
(Make Table on Query menu, when in query design view), and then adding an
AutoNumber.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for the article, it was helpful, but the problem is that I
am using a query not a form(so I can't use the code method), and for
my query there is no ID field of a number data type so I can't use
DLookup method..how do I generate an autonumber (ID field) for a
select query? I need to use a query since I will be graphing this data
later and maybe using it again in other queries, so a form is not
suitable.
Thank you.




"Allen Browne" <[email protected]> wrote in message

See:
Referring to a Field in the Previous or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[sad]

Thank you again for replying. I realize that a form can look like a
query in datasheet view, but when I generate a graph the data must
come from a query or table. I don't think you can graph based on a
form in datasheet view. Is there a way to convert a form in datasheet
view to a query so I can graph the data?

I want to use a subquery, but all the subqueries code I know of
require that you have a unique field in your query, but I don't have
any unique fields in my query since there are possible duplicates in
all of my fields. This is the reason I wanted to generate an
autonumber[as a unique field], so I can use a subquery or the
DLoopup() Method.


The only option I can think of is as you said to create a temporary
table, then generate an autonumber in the table, then convert that
table back to a query. That way I have a unique field, which I can
then use for a subquery or the Dlookup. Thank you so much for your
help. It has helped me to understand many concepts better.

You do realize that you can make a form in Datasheet view so it looks like a
query?

In any case, the DLookup() will work in a query.

The subquery will be much more efficient, but apparently you don't want to
use that.

And you still have the option of creating a temporary table from your query
(Make Table on Query menu, when in query design view), and then adding an
AutoNumber.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for the article, it was helpful, but the problem is that I
am using a query not a form(so I can't use the code method), and for
my query there is no ID field of a number data type so I can't use
DLookup method..how do I generate an autonumber (ID field) for a
select query? I need to use a query since I will be graphing this data
later and maybe using it again in other queries, so a form is not
suitable.
Thank you.




"Allen Browne" <[email protected]> wrote in message

See:
Referring to a Field in the Previous or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.