Why query does this?

B

Beginner

Hi!

I'm testing the following thing because I'm interested in why it works.

The situation I'm starting with:

I have a table with 3 fields and some data in it. The fields are: Id
(double), Name (text) and Calc (double). I put data into the table like this
(with my two hands):
Id Name Calc
8 Eight
5 Five
2 Two ... etc, id:s are from 1 to 10. Into the field Calc I don't put
anything.

Then I make a query, select * from the table I just filled with stuff. I
order it desc by Id. The query is named MyQuery.

Then I run the following code:

Sub TestingValues()

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim k As Double

Do While rs.EOF = False
k = rs![Id]
rs![Calc] = k
rs.MoveNext
Loop
End Sub

So I put values into the Calc-fied, and the value I put is always the same
as the Id in the record I'm processing. Notice that I am processing the query
in my code.

So, in this thing I have a table which has 10 records. I have a query in
which I sort the records desc by Id and code which puts data into the Calc
field in the query.

Now. When I open the query, it is sorted desc (as it should be) and the
walues are correct (Id 1 has Calc value 1 and so on). When I open the table
it is sorted asc by Id and the values are correct (Id 2 has Calc value 2 and
so on).

My first question is:
Why does this work correctly?

And second one:
Can I rely on it to work like this even with huge amount of data and with
multiple calculated fields?

- Beginner -
 
B

Baz

When you open your query, the records are sorted the way they are because
you specified the sort order in the query. When you open the table, the
sort order is undefined. It is, if you like, sheer luck that they have come
out in ascending order of Id, and no, you cannot rely on it. If you want
records in a specific order, say so explicitly in a query.

The Calc field corresponds to the Id no matter how you sort the results,
because when you sort the results using a specified field, you are sorting
the *whole record*, not just that field. This you can rely on!
 
B

Beginner

Hi Baz!

Thanks for the answer! The order of the records in the table isn't relevant
for me, the only thing that matters is that the right record has the right
calculated value.

So, to make sure, are you saying that when I do the calculations in the
query, and open the table after that and the field I have calculated has the
values in the table, I always have the right calculated values in the right
record? Because that would be amazing and great! :)

- Beginner -

Baz said:
When you open your query, the records are sorted the way they are because
you specified the sort order in the query. When you open the table, the
sort order is undefined. It is, if you like, sheer luck that they have come
out in ascending order of Id, and no, you cannot rely on it. If you want
records in a specific order, say so explicitly in a query.

The Calc field corresponds to the Id no matter how you sort the results,
because when you sort the results using a specified field, you are sorting
the *whole record*, not just that field. This you can rely on!

Beginner said:
Hi!

I'm testing the following thing because I'm interested in why it works.

The situation I'm starting with:

I have a table with 3 fields and some data in it. The fields are: Id
(double), Name (text) and Calc (double). I put data into the table like this
(with my two hands):
Id Name Calc
8 Eight
5 Five
2 Two ... etc, id:s are from 1 to 10. Into the field Calc I don't put
anything.

Then I make a query, select * from the table I just filled with stuff. I
order it desc by Id. The query is named MyQuery.

Then I run the following code:

Sub TestingValues()

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim k As Double

Do While rs.EOF = False
k = rs![Id]
rs![Calc] = k
rs.MoveNext
Loop
End Sub

So I put values into the Calc-fied, and the value I put is always the same
as the Id in the record I'm processing. Notice that I am processing the query
in my code.

So, in this thing I have a table which has 10 records. I have a query in
which I sort the records desc by Id and code which puts data into the Calc
field in the query.

Now. When I open the query, it is sorted desc (as it should be) and the
walues are correct (Id 1 has Calc value 1 and so on). When I open the table
it is sorted asc by Id and the values are correct (Id 2 has Calc value 2 and
so on).

My first question is:
Why does this work correctly?

And second one:
Can I rely on it to work like this even with huge amount of data and with
multiple calculated fields?

- Beginner -
 
B

Baz

You aren't doing the calculations in the query. Your little code loop wrote
the calculation results back into the table, one record at a time. All you
are doing thereafter is reading the data back again.

It isn't amazing and great, it's essential! Access would be unusable if
didn't work like that!

Beginner said:
Hi Baz!

Thanks for the answer! The order of the records in the table isn't relevant
for me, the only thing that matters is that the right record has the right
calculated value.

So, to make sure, are you saying that when I do the calculations in the
query, and open the table after that and the field I have calculated has the
values in the table, I always have the right calculated values in the right
record? Because that would be amazing and great! :)

- Beginner -

Baz said:
When you open your query, the records are sorted the way they are because
you specified the sort order in the query. When you open the table, the
sort order is undefined. It is, if you like, sheer luck that they have come
out in ascending order of Id, and no, you cannot rely on it. If you want
records in a specific order, say so explicitly in a query.

The Calc field corresponds to the Id no matter how you sort the results,
because when you sort the results using a specified field, you are sorting
the *whole record*, not just that field. This you can rely on!

Beginner said:
Hi!

I'm testing the following thing because I'm interested in why it works.

The situation I'm starting with:

I have a table with 3 fields and some data in it. The fields are: Id
(double), Name (text) and Calc (double). I put data into the table
like
this
(with my two hands):
Id Name Calc
8 Eight
5 Five
2 Two ... etc, id:s are from 1 to 10. Into the field Calc I don't put
anything.

Then I make a query, select * from the table I just filled with stuff. I
order it desc by Id. The query is named MyQuery.

Then I run the following code:

Sub TestingValues()

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim k As Double

Do While rs.EOF = False
k = rs![Id]
rs![Calc] = k
rs.MoveNext
Loop
End Sub

So I put values into the Calc-fied, and the value I put is always the same
as the Id in the record I'm processing. Notice that I am processing
the
query
in my code.

So, in this thing I have a table which has 10 records. I have a query in
which I sort the records desc by Id and code which puts data into the Calc
field in the query.

Now. When I open the query, it is sorted desc (as it should be) and the
walues are correct (Id 1 has Calc value 1 and so on). When I open the table
it is sorted asc by Id and the values are correct (Id 2 has Calc value
2
and
so on).

My first question is:
Why does this work correctly?

And second one:
Can I rely on it to work like this even with huge amount of data and with
multiple calculated fields?

- Beginner -
 
B

Beginner

Thanks Baz! So now I can, without any hesitation, use this method to
calculate stuff. I'm planning to use queries to sort data and then do the
calculations on the sorted data (I'm doing quite compex calculations with
data from 7 tables, but I always use data from records which have the same
Id), so I can do it really fast, since I don't have to look for Id:s, I can
just go the sorted data over in one single loop record by record and do the
calculations.

Thanks!

- Beginner -

Baz said:
You aren't doing the calculations in the query. Your little code loop wrote
the calculation results back into the table, one record at a time. All you
are doing thereafter is reading the data back again.

It isn't amazing and great, it's essential! Access would be unusable if
didn't work like that!

Beginner said:
Hi Baz!

Thanks for the answer! The order of the records in the table isn't relevant
for me, the only thing that matters is that the right record has the right
calculated value.

So, to make sure, are you saying that when I do the calculations in the
query, and open the table after that and the field I have calculated has the
values in the table, I always have the right calculated values in the right
record? Because that would be amazing and great! :)

- Beginner -

Baz said:
When you open your query, the records are sorted the way they are because
you specified the sort order in the query. When you open the table, the
sort order is undefined. It is, if you like, sheer luck that they have come
out in ascending order of Id, and no, you cannot rely on it. If you want
records in a specific order, say so explicitly in a query.

The Calc field corresponds to the Id no matter how you sort the results,
because when you sort the results using a specified field, you are sorting
the *whole record*, not just that field. This you can rely on!

Hi!

I'm testing the following thing because I'm interested in why it works.

The situation I'm starting with:

I have a table with 3 fields and some data in it. The fields are: Id
(double), Name (text) and Calc (double). I put data into the table like
this
(with my two hands):
Id Name Calc
8 Eight
5 Five
2 Two ... etc, id:s are from 1 to 10. Into the field Calc I don't put
anything.

Then I make a query, select * from the table I just filled with stuff. I
order it desc by Id. The query is named MyQuery.

Then I run the following code:

Sub TestingValues()

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim k As Double

Do While rs.EOF = False
k = rs![Id]
rs![Calc] = k
rs.MoveNext
Loop
End Sub

So I put values into the Calc-fied, and the value I put is always the same
as the Id in the record I'm processing. Notice that I am processing the
query
in my code.

So, in this thing I have a table which has 10 records. I have a query in
which I sort the records desc by Id and code which puts data into the Calc
field in the query.

Now. When I open the query, it is sorted desc (as it should be) and the
walues are correct (Id 1 has Calc value 1 and so on). When I open the
table
it is sorted asc by Id and the values are correct (Id 2 has Calc value 2
and
so on).

My first question is:
Why does this work correctly?

And second one:
Can I rely on it to work like this even with huge amount of data and with
multiple calculated fields?

- Beginner -
 
B

Baz

Not necessarily a great idea. Storing calculated data in your tables is a
real no-no in database design. Much better to have a query calculate the
results when you need them.

Beginner said:
Thanks Baz! So now I can, without any hesitation, use this method to
calculate stuff. I'm planning to use queries to sort data and then do the
calculations on the sorted data (I'm doing quite compex calculations with
data from 7 tables, but I always use data from records which have the same
Id), so I can do it really fast, since I don't have to look for Id:s, I can
just go the sorted data over in one single loop record by record and do the
calculations.

Thanks!

- Beginner -

Baz said:
You aren't doing the calculations in the query. Your little code loop wrote
the calculation results back into the table, one record at a time. All you
are doing thereafter is reading the data back again.

It isn't amazing and great, it's essential! Access would be unusable if
didn't work like that!

Beginner said:
Hi Baz!

Thanks for the answer! The order of the records in the table isn't relevant
for me, the only thing that matters is that the right record has the right
calculated value.

So, to make sure, are you saying that when I do the calculations in the
query, and open the table after that and the field I have calculated
has
the
values in the table, I always have the right calculated values in the right
record? Because that would be amazing and great! :)

- Beginner -

:

When you open your query, the records are sorted the way they are because
you specified the sort order in the query. When you open the table, the
sort order is undefined. It is, if you like, sheer luck that they
have
come
out in ascending order of Id, and no, you cannot rely on it. If you want
records in a specific order, say so explicitly in a query.

The Calc field corresponds to the Id no matter how you sort the results,
because when you sort the results using a specified field, you are sorting
the *whole record*, not just that field. This you can rely on!

Hi!

I'm testing the following thing because I'm interested in why it works.

The situation I'm starting with:

I have a table with 3 fields and some data in it. The fields are: Id
(double), Name (text) and Calc (double). I put data into the table like
this
(with my two hands):
Id Name Calc
8 Eight
5 Five
2 Two ... etc, id:s are from 1 to 10. Into the field Calc I
don't
put
anything.

Then I make a query, select * from the table I just filled with
stuff.
I
order it desc by Id. The query is named MyQuery.

Then I run the following code:

Sub TestingValues()

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim k As Double

Do While rs.EOF = False
k = rs![Id]
rs![Calc] = k
rs.MoveNext
Loop
End Sub

So I put values into the Calc-fied, and the value I put is always
the
same
as the Id in the record I'm processing. Notice that I am
processing
the
query
in my code.

So, in this thing I have a table which has 10 records. I have a
query
in
which I sort the records desc by Id and code which puts data into
the
Calc
field in the query.

Now. When I open the query, it is sorted desc (as it should be)
and
the
walues are correct (Id 1 has Calc value 1 and so on). When I open the
table
it is sorted asc by Id and the values are correct (Id 2 has Calc
value
2
and
so on).

My first question is:
Why does this work correctly?

And second one:
Can I rely on it to work like this even with huge amount of data
and
with
multiple calculated fields?

- Beginner -
 
B

Beginner

I'm not storing the data, I'm just calculating it, making some queries about
the calcuclated things and then making reports based on the calculated data.
I do this every day so I'm never storing anything but the reports. I can't
make the calculations in a query because they are not simple. I need to do
them in code, altough I personally would prefer doing them in a query
instead... But it is just not happening.

- Beginner -

Baz said:
Not necessarily a great idea. Storing calculated data in your tables is a
real no-no in database design. Much better to have a query calculate the
results when you need them.

Beginner said:
Thanks Baz! So now I can, without any hesitation, use this method to
calculate stuff. I'm planning to use queries to sort data and then do the
calculations on the sorted data (I'm doing quite compex calculations with
data from 7 tables, but I always use data from records which have the same
Id), so I can do it really fast, since I don't have to look for Id:s, I can
just go the sorted data over in one single loop record by record and do the
calculations.

Thanks!

- Beginner -

Baz said:
You aren't doing the calculations in the query. Your little code loop wrote
the calculation results back into the table, one record at a time. All you
are doing thereafter is reading the data back again.

It isn't amazing and great, it's essential! Access would be unusable if
didn't work like that!

Hi Baz!

Thanks for the answer! The order of the records in the table isn't
relevant
for me, the only thing that matters is that the right record has the right
calculated value.

So, to make sure, are you saying that when I do the calculations in the
query, and open the table after that and the field I have calculated has
the
values in the table, I always have the right calculated values in the
right
record? Because that would be amazing and great! :)

- Beginner -

:

When you open your query, the records are sorted the way they are
because
you specified the sort order in the query. When you open the table, the
sort order is undefined. It is, if you like, sheer luck that they have
come
out in ascending order of Id, and no, you cannot rely on it. If you
want
records in a specific order, say so explicitly in a query.

The Calc field corresponds to the Id no matter how you sort the results,
because when you sort the results using a specified field, you are
sorting
the *whole record*, not just that field. This you can rely on!

Hi!

I'm testing the following thing because I'm interested in why it
works.

The situation I'm starting with:

I have a table with 3 fields and some data in it. The fields are: Id
(double), Name (text) and Calc (double). I put data into the table
like
this
(with my two hands):
Id Name Calc
8 Eight
5 Five
2 Two ... etc, id:s are from 1 to 10. Into the field Calc I don't
put
anything.

Then I make a query, select * from the table I just filled with stuff.
I
order it desc by Id. The query is named MyQuery.

Then I run the following code:

Sub TestingValues()

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim k As Double

Do While rs.EOF = False
k = rs![Id]
rs![Calc] = k
rs.MoveNext
Loop
End Sub

So I put values into the Calc-fied, and the value I put is always the
same
as the Id in the record I'm processing. Notice that I am processing
the
query
in my code.

So, in this thing I have a table which has 10 records. I have a query
in
which I sort the records desc by Id and code which puts data into the
Calc
field in the query.

Now. When I open the query, it is sorted desc (as it should be) and
the
walues are correct (Id 1 has Calc value 1 and so on). When I open the
table
it is sorted asc by Id and the values are correct (Id 2 has Calc value
2
and
so on).

My first question is:
Why does this work correctly?

And second one:
Can I rely on it to work like this even with huge amount of data and
with
multiple calculated fields?

- Beginner -
 
B

Baz

The code you originally posted IS storing the calculation results in the
database - that's why you can see them when you open the table.

It's perfectly possible to do complex calculations in a query. You can even
write the calculations in a VBA function that the query calls at run-time.
Doing what you did originally - opening a recordset and iterating through
it - is generally a poor way to do anything in a relational database
environment, and should only be used as a last resort.

If you post more details about the calculation (start a new thread, no-one
else will see this one now) I'm sure someone will tell you what to do.

Beginner said:
I'm not storing the data, I'm just calculating it, making some queries about
the calcuclated things and then making reports based on the calculated data.
I do this every day so I'm never storing anything but the reports. I can't
make the calculations in a query because they are not simple. I need to do
them in code, altough I personally would prefer doing them in a query
instead... But it is just not happening.

- Beginner -

Baz said:
Not necessarily a great idea. Storing calculated data in your tables is a
real no-no in database design. Much better to have a query calculate the
results when you need them.

Beginner said:
Thanks Baz! So now I can, without any hesitation, use this method to
calculate stuff. I'm planning to use queries to sort data and then do the
calculations on the sorted data (I'm doing quite compex calculations with
data from 7 tables, but I always use data from records which have the same
Id), so I can do it really fast, since I don't have to look for Id:s,
I
can
just go the sorted data over in one single loop record by record and
do
the
calculations.

Thanks!

- Beginner -

:

You aren't doing the calculations in the query. Your little code
loop
wrote
the calculation results back into the table, one record at a time.
All
you
are doing thereafter is reading the data back again.

It isn't amazing and great, it's essential! Access would be unusable if
didn't work like that!

Hi Baz!

Thanks for the answer! The order of the records in the table isn't
relevant
for me, the only thing that matters is that the right record has
the
right
calculated value.

So, to make sure, are you saying that when I do the calculations
in
the
query, and open the table after that and the field I have
calculated
has
the
values in the table, I always have the right calculated values in the
right
record? Because that would be amazing and great! :)

- Beginner -

:

When you open your query, the records are sorted the way they are
because
you specified the sort order in the query. When you open the
table,
the
sort order is undefined. It is, if you like, sheer luck that
they
have
come
out in ascending order of Id, and no, you cannot rely on it. If you
want
records in a specific order, say so explicitly in a query.

The Calc field corresponds to the Id no matter how you sort the results,
because when you sort the results using a specified field, you are
sorting
the *whole record*, not just that field. This you can rely on!

Hi!

I'm testing the following thing because I'm interested in why it
works.

The situation I'm starting with:

I have a table with 3 fields and some data in it. The fields
are:
Id
(double), Name (text) and Calc (double). I put data into the table
like
this
(with my two hands):
Id Name Calc
8 Eight
5 Five
2 Two ... etc, id:s are from 1 to 10. Into the field Calc I don't
put
anything.

Then I make a query, select * from the table I just filled
with
stuff.
I
order it desc by Id. The query is named MyQuery.

Then I run the following code:

Sub TestingValues()

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim k As Double

Do While rs.EOF = False
k = rs![Id]
rs![Calc] = k
rs.MoveNext
Loop
End Sub

So I put values into the Calc-fied, and the value I put is
always
the
same
as the Id in the record I'm processing. Notice that I am processing
the
query
in my code.

So, in this thing I have a table which has 10 records. I have
a
query
in
which I sort the records desc by Id and code which puts data
into
the
Calc
field in the query.

Now. When I open the query, it is sorted desc (as it should
be)
and
the
walues are correct (Id 1 has Calc value 1 and so on). When I
open
the
table
it is sorted asc by Id and the values are correct (Id 2 has
Calc
value
2
and
so on).

My first question is:
Why does this work correctly?

And second one:
Can I rely on it to work like this even with huge amount of
data
and
with
multiple calculated fields?

- Beginner -
 
B

Beginner

Hi Baz!

You have a good point. I'll start a new thread next week and I'll ask for
futher information in is it possible to make the query do the calculations
somehow.

Thank you so much for all your answers! :)

- Beginner -

Baz said:
The code you originally posted IS storing the calculation results in the
database - that's why you can see them when you open the table.

It's perfectly possible to do complex calculations in a query. You can even
write the calculations in a VBA function that the query calls at run-time.
Doing what you did originally - opening a recordset and iterating through
it - is generally a poor way to do anything in a relational database
environment, and should only be used as a last resort.

If you post more details about the calculation (start a new thread, no-one
else will see this one now) I'm sure someone will tell you what to do.

Beginner said:
I'm not storing the data, I'm just calculating it, making some queries about
the calcuclated things and then making reports based on the calculated data.
I do this every day so I'm never storing anything but the reports. I can't
make the calculations in a query because they are not simple. I need to do
them in code, altough I personally would prefer doing them in a query
instead... But it is just not happening.

- Beginner -

Baz said:
Not necessarily a great idea. Storing calculated data in your tables is a
real no-no in database design. Much better to have a query calculate the
results when you need them.

Thanks Baz! So now I can, without any hesitation, use this method to
calculate stuff. I'm planning to use queries to sort data and then do the
calculations on the sorted data (I'm doing quite compex calculations with
data from 7 tables, but I always use data from records which have the same
Id), so I can do it really fast, since I don't have to look for Id:s, I
can
just go the sorted data over in one single loop record by record and do
the
calculations.

Thanks!

- Beginner -

:

You aren't doing the calculations in the query. Your little code loop
wrote
the calculation results back into the table, one record at a time. All
you
are doing thereafter is reading the data back again.

It isn't amazing and great, it's essential! Access would be unusable if
didn't work like that!

Hi Baz!

Thanks for the answer! The order of the records in the table isn't
relevant
for me, the only thing that matters is that the right record has the
right
calculated value.

So, to make sure, are you saying that when I do the calculations in
the
query, and open the table after that and the field I have calculated
has
the
values in the table, I always have the right calculated values in the
right
record? Because that would be amazing and great! :)

- Beginner -

:

When you open your query, the records are sorted the way they are
because
you specified the sort order in the query. When you open the table,
the
sort order is undefined. It is, if you like, sheer luck that they
have
come
out in ascending order of Id, and no, you cannot rely on it. If you
want
records in a specific order, say so explicitly in a query.

The Calc field corresponds to the Id no matter how you sort the
results,
because when you sort the results using a specified field, you are
sorting
the *whole record*, not just that field. This you can rely on!

Hi!

I'm testing the following thing because I'm interested in why it
works.

The situation I'm starting with:

I have a table with 3 fields and some data in it. The fields are:
Id
(double), Name (text) and Calc (double). I put data into the table
like
this
(with my two hands):
Id Name Calc
8 Eight
5 Five
2 Two ... etc, id:s are from 1 to 10. Into the field Calc I
don't
put
anything.

Then I make a query, select * from the table I just filled with
stuff.
I
order it desc by Id. The query is named MyQuery.

Then I run the following code:

Sub TestingValues()

Dim rs As New ADODB.Recordset
rs.Open "MyQuery", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim k As Double

Do While rs.EOF = False
k = rs![Id]
rs![Calc] = k
rs.MoveNext
Loop
End Sub

So I put values into the Calc-fied, and the value I put is always
the
same
as the Id in the record I'm processing. Notice that I am
processing
the
query
in my code.

So, in this thing I have a table which has 10 records. I have a
query
in
which I sort the records desc by Id and code which puts data into
the
Calc
field in the query.

Now. When I open the query, it is sorted desc (as it should be)
and
the
walues are correct (Id 1 has Calc value 1 and so on). When I open
the
table
it is sorted asc by Id and the values are correct (Id 2 has Calc
value
2
and
so on).

My first question is:
Why does this work correctly?

And second one:
Can I rely on it to work like this even with huge amount of data
and
with
multiple calculated fields?

- Beginner -
 

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

Top