Why query does this?

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

 

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

 

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

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 -

 

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

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 -

 

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

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 -

 

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

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 -

 

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

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 -

 

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

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 -

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 -

 

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

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.

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 -

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 -