Calculated Query expression

S

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.
 
J

John Nurick

Hi,

Here's an example of one approach:

SELECT
Current.ID, Previous.ID, Current.OtherField, Previous.OtherField
FROM MyTable AS Current, MyTable AS Previous
WHERE Previous.ID = (
SELECT
MAX(C.ID) FROM MyTable AS C
WHERE C.ID < Current.ID
)
ORDER BY Current.ID
;

If you ask in the Queries newsgroup (microsoft.public.access.queries)
you'll probably be offered a more elegant solution by the SQL experts
who hang out there.

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.
Click to expand...
 
S

sad

Thank you for your reply. Your code was helpful, but the problem is
that I am using a select query that gets its data from several other
tables and queries, and there is no ID field. Is there a way to
generate an autonumber (ID field) in a select query, where the num of
records will change? I have tried but can't find any way to do it. I
have posted in the microsoft.public.access.queries as well, but the
solutions provided all require an ID field defined. Thank you.

John Nurick said:
Hi,

Here's an example of one approach:

SELECT
Current.ID, Previous.ID, Current.OtherField, Previous.OtherField
FROM MyTable AS Current, MyTable AS Previous
WHERE Previous.ID = (
SELECT
MAX(C.ID) FROM MyTable AS C
WHERE C.ID < Current.ID
)
ORDER BY Current.ID
;

If you ask in the Queries newsgroup (microsoft.public.access.queries)
you'll probably be offered a more elegant solution by the SQL experts
who hang out there.

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.
Click to expand...
Click to expand...
 
M

MacDermott

I'm a bit confused as to how you want to calculate this function.
You've provided 2 ways of calculating it:
1. freq: (1/numrecords) + valueofpreviousrecord
In this case, you'll need to define a value for the first
record, as "value of previous record" won't be known.

2. Expr1: (1/numrecords) * (numrecords-(numrecords-x))
This would simplify to (1/numrecords) * x

In your example, numrecords=4, so in either case 1/numrecords should return
..25
It's a bit difficult to see how either of the formulae you give would return
the values you say you want...
 
J

John Nurick

There doesn't have to be a field named ID, but the query must return a
field (or combination of fields) that can be used to sort the records
into the order you need. Without that, you don't know what order the
records will be in and therefore it's meaningless to compare one record
with the previous one. Basically you just substitute the name of this
field wherever "ID" appears in the sample query.

Another thing about the concept of "previous record" is that it can't
apply to the first record, as Turtle points out.

Thank you for your reply. Your code was helpful, but the problem is
that I am using a select query that gets its data from several other
tables and queries, and there is no ID field. Is there a way to
generate an autonumber (ID field) in a select query, where the num of
records will change? I have tried but can't find any way to do it. I
have posted in the microsoft.public.access.queries as well, but the
solutions provided all require an ID field defined. Thank you.

John Nurick said:
Hi,

Here's an example of one approach:

SELECT
Current.ID, Previous.ID, Current.OtherField, Previous.OtherField
FROM MyTable AS Current, MyTable AS Previous
WHERE Previous.ID = (
SELECT
MAX(C.ID) FROM MyTable AS C
WHERE C.ID < Current.ID
)
ORDER BY Current.ID
;

If you ask in the Queries newsgroup (microsoft.public.access.queries)
you'll probably be offered a more elegant solution by the SQL experts
who hang out there.

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.
Click to expand...
Click to expand...
Click to expand...
 
S

sad

Thank you.

John Nurick said:
There doesn't have to be a field named ID, but the query must return a
field (or combination of fields) that can be used to sort the records
into the order you need. Without that, you don't know what order the
records will be in and therefore it's meaningless to compare one record
with the previous one. Basically you just substitute the name of this
field wherever "ID" appears in the sample query.

Another thing about the concept of "previous record" is that it can't
apply to the first record, as Turtle points out.

Thank you for your reply. Your code was helpful, but the problem is
that I am using a select query that gets its data from several other
tables and queries, and there is no ID field. Is there a way to
generate an autonumber (ID field) in a select query, where the num of
records will change? I have tried but can't find any way to do it. I
have posted in the microsoft.public.access.queries as well, but the
solutions provided all require an ID field defined. Thank you.

John Nurick said:
Hi,

Here's an example of one approach:

SELECT
Current.ID, Previous.ID, Current.OtherField, Previous.OtherField
FROM MyTable AS Current, MyTable AS Previous
WHERE Previous.ID = (
SELECT
MAX(C.ID) FROM MyTable AS C
WHERE C.ID < Current.ID
)
ORDER BY Current.ID
;

If you ask in the Queries newsgroup (microsoft.public.access.queries)
you'll probably be offered a more elegant solution by the SQL experts
who hang out there.

On 24 Nov 2004 13:03:26 -0800, (e-mail address removed) (sad) wrote:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculated query problem 3
Calculating Expression in Query 4
Number of records = -1 6
C# Retrieve COUNT from SQL Query - What am I doing wrong? 4
Query takes to long 1
how to know the number of records in a table? 2
Need Help with Coding!urgent 13
problem with an iif() function in a formula 5

Top